A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

relationships of 3 tables



 
 
Thread Tools Display Modes
  #1  
Old December 13th, 2009, 12:45 AM posted to microsoft.public.access.tablesdbdesign
Scott_Brasted via AccessMonster.com
external usenet poster
 
Posts: 49
Default relationships of 3 tables

Greeings,

I am creating a db to track who serves what function in what Sunday at church.
So I think I need 3 tables.
1. people who serve
2. functions they serve
3. dates they serve

Many people can serve many dates and many people can serve many functions on
many dates.

So do I have the three tables and then a join table for people/dates and a
join table for people/function and a join table for dates and functions? And
how do I structure them. I am thinking:
tblPeople
peopleID pk
name

tblFunction
functionID pk
functionName

tblDate
dateID pk
serviceDate

tblJoinPeopleDates
peopleID pk
dateID pk

tblJoinPeopleFunction
peopleID pk
functionID pk

tblJoinFunctionDate
functionID pk
dateID pk

If this structure works, what does the relationship window look like? This is
the first time I have tried anything like this.

Thanks,
Scott

--
Message posted via http://www.accessmonster.com

  #2  
Old December 13th, 2009, 02:46 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default relationships of 3 tables

Hi Scot,

You don't need all those tables! I have assumed there are different
functions for different dates.
TblPeople
PeopleID
FirstName
LastName

TblFunction
FunctionID
Function

TblChurchDateFunction
ChurchDateFunctionID
ChurchDate
FunctionID

TblChurchDateFunctionPeople
ChurchDateFunctionPeopleID
DateFunctionID
PeopleID

Assuming you have TblPeople and TblFunction filled, next create a form on
TblChurchDateFunction and enter all the functions for each church date. You
can use a calendar control for entering the church dates and a combobox
having a rowsource of TblFunction for entering each FunctionID. Next create
a query that includes TblFunction, TblChurchDateFunction and
TblChurchDateFunctionPeople. With the proper design of the query, you will
be able to display a list of each church date and all the functions on each
church date. Use this query as the recordsource of a form and use a combobox
with a rowsource of TblPeople to enter the person for each function on each
church date.

Steve


"Scott_Brasted via AccessMonster.com" u56211@uwe wrote in message
news:a07ff70bde172@uwe...
Greeings,

I am creating a db to track who serves what function in what Sunday at
church.
So I think I need 3 tables.
1. people who serve
2. functions they serve
3. dates they serve

Many people can serve many dates and many people can serve many functions
on
many dates.

So do I have the three tables and then a join table for people/dates and a
join table for people/function and a join table for dates and functions?
And
how do I structure them. I am thinking:
tblPeople
peopleID pk
name

tblFunction
functionID pk
functionName

tblDate
dateID pk
serviceDate

tblJoinPeopleDates
peopleID pk
dateID pk

tblJoinPeopleFunction
peopleID pk
functionID pk

tblJoinFunctionDate
functionID pk
dateID pk

If this structure works, what does the relationship window look like? This
is
the first time I have tried anything like this.

Thanks,
Scott

--
Message posted via
http://www.accessmonster.com



  #3  
Old December 13th, 2009, 05:54 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default relationships of 3 tables

In TblChurchDateFunctionPeople, make DateFunctionID ChurchDateFunctionID.

Steve

"Steve" wrote in message
...
Hi Scot,

You don't need all those tables! I have assumed there are different
functions for different dates.
TblPeople
PeopleID
FirstName
LastName

TblFunction
FunctionID
Function

TblChurchDateFunction
ChurchDateFunctionID
ChurchDate
FunctionID

TblChurchDateFunctionPeople
ChurchDateFunctionPeopleID
DateFunctionID
PeopleID

Assuming you have TblPeople and TblFunction filled, next create a form on
TblChurchDateFunction and enter all the functions for each church date.
You can use a calendar control for entering the church dates and a
combobox having a rowsource of TblFunction for entering each FunctionID.
Next create a query that includes TblFunction, TblChurchDateFunction and
TblChurchDateFunctionPeople. With the proper design of the query, you will
be able to display a list of each church date and all the functions on
each church date. Use this query as the recordsource of a form and use a
combobox with a rowsource of TblPeople to enter the person for each
function on each church date.

Steve


"Scott_Brasted via AccessMonster.com" u56211@uwe wrote in message
news:a07ff70bde172@uwe...
Greeings,

I am creating a db to track who serves what function in what Sunday at
church.
So I think I need 3 tables.
1. people who serve
2. functions they serve
3. dates they serve

Many people can serve many dates and many people can serve many functions
on
many dates.

So do I have the three tables and then a join table for people/dates and
a
join table for people/function and a join table for dates and functions?
And
how do I structure them. I am thinking:
tblPeople
peopleID pk
name

tblFunction
functionID pk
functionName

tblDate
dateID pk
serviceDate

tblJoinPeopleDates
peopleID pk
dateID pk

tblJoinPeopleFunction
peopleID pk
functionID pk

tblJoinFunctionDate
functionID pk
dateID pk

If this structure works, what does the relationship window look like?
This is
the first time I have tried anything like this.

Thanks,
Scott

--
Message posted via
http://www.accessmonster.com





  #4  
Old December 13th, 2009, 10:53 PM posted to microsoft.public.access.tablesdbdesign
Scott_Brasted via AccessMonster.com
external usenet poster
 
Posts: 49
Default relationships of 3 tables

Steve,

Thanks for the info. Ihave been strubbling with this most of the day. I ran
aground on the query. I have found several ways to get the data to display,
but none of them allow me to add people to the mix.

My joins in the query:

tblFunction (left table) to tblChurchDateFunction (right table) to only
include rows where joined fields from tables are equal. This by itself gets
me a list of dates and functions.

Then I add tblChurchDateFunctionPeople. I join it to tblChurchDateFunction.

My Joins:
tblChurchDateFunction (left table) to tblChurchDateFunctionPeople (right
table) to include All records from tblChurchDateFunction and only those
records from tblChurchDateFunctionPeople where the joined fields are equal.

There are several permutations of joins that allow me to see the data in the
query, but so far no join has allowed me to add a person to the
tblChurchDateFunctionPeople table.

And it just dawned on me that I did not realize when I first posted this that
I sometimes need to have more than one person in a function on a date.This
may be possible with tblChurchDateFunctionPeople table,but I do not know how
to enter it.

Thanks,
Scott


Steve wrote:
In TblChurchDateFunctionPeople, make DateFunctionID ChurchDateFunctionID.

Steve

Hi Scot,

[quoted text clipped - 81 lines]
Thanks,
Scott


--
Message posted via http://www.accessmonster.com

  #5  
Old December 14th, 2009, 02:51 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default relationships of 3 tables

Scott,

I only briefly perused the subsequent exchange, but if you make a solid
decision on the entities being database and then a solid table structure
based on that, then I think you job will be much easier / more successful.

The first question is whether "dates" are an entity that needs databasing
vs. just an attribute of other entities.

If the answer is yes, the next question is whether or not your "date"
entity has other 1:1 attributes that you will be actively databasing. That
will make the difference between a true data table vs. a table that is just
there to populate dropdown lists.

Either way, I suspect that you will need to ditch a few of your tables and
your main 3 tables should be:

People

Functions

InstancesOfAPersonServingAFunctionOnAParticulerDat e (of course, shorten
that). This table will have FK's for People and Function and probalby just a
date field, but possible a FK for a dates table, if the latter is truly an
entity that needs databasing.

Well, there's my 2 cents.




Even if the answer is the former, then the next question is whether or not
you need to
  #6  
Old December 14th, 2009, 05:04 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default relationships of 3 tables

Hi Scott,

TblChurchDateFunctionPeople has the field PeopleID. Include PeopleID in your
query. PeopleID is a specific person. On your form, use a combobox to enter
PeopleID. Create a query based on TblPeople. Put PeopleID in the first
column of the query. Put the following expression in the second column:
Person = [LastName] & ", " & [FirstName]
Sort Ascending on this column. Set the rowsource of the combobox to this
query. Set the Bound Column property to 1 , Column Count property to 2 and
Column Width property tp 0;2.

Regarding having more than one person in a function on a date, Yes, you can
record more than one person in TblChurchDateFunctionPeople for the same date
and function.

Since you can have more than one person in a function on a date, the better
way to enter your data would be a form/subform. Create a query that includes
TblFunction and TblChurchDateFunction. Include Function from TblFunction,
ChurchDateFunctionID from TblChurchDateFunction and ChurchDate from
TblChurchDateFunction. Use this query as the recordsource of the main form.
You can the display a church date and a function on that date. Base the
subform on TblChurchDateFunctionPeople. Make the subform a continuous form
and you only need the combobox described above. On the main form, set the
LinkMaster/Linkchild properties to ChurchDateFunctionID.

Steve


"Scott_Brasted via AccessMonster.com" u56211@uwe wrote in message
news:a08b8f6fb6d71@uwe...
Steve,

Thanks for the info. Ihave been strubbling with this most of the day. I
ran
aground on the query. I have found several ways to get the data to
display,
but none of them allow me to add people to the mix.

My joins in the query:

tblFunction (left table) to tblChurchDateFunction (right table) to only
include rows where joined fields from tables are equal. This by itself
gets
me a list of dates and functions.

Then I add tblChurchDateFunctionPeople. I join it to
tblChurchDateFunction.

My Joins:
tblChurchDateFunction (left table) to tblChurchDateFunctionPeople (right
table) to include All records from tblChurchDateFunction and only those
records from tblChurchDateFunctionPeople where the joined fields are
equal.

There are several permutations of joins that allow me to see the data in
the
query, but so far no join has allowed me to add a person to the
tblChurchDateFunctionPeople table.

And it just dawned on me that I did not realize when I first posted this
that
I sometimes need to have more than one person in a function on a date.This
may be possible with tblChurchDateFunctionPeople table,but I do not know
how
to enter it.

Thanks,
Scott


Steve wrote:
In TblChurchDateFunctionPeople, make DateFunctionID ChurchDateFunctionID.

Steve

Hi Scot,

[quoted text clipped - 81 lines]
Thanks,
Scott


--
Message posted via
http://www.accessmonster.com



  #7  
Old December 14th, 2009, 06:12 PM posted to microsoft.public.access.tablesdbdesign
Scott_Brasted via AccessMonster.com
external usenet poster
 
Posts: 49
Default relationships of 3 tables

Fred,

Working! Simple is better.

I have decided that date is an entity. Four tables and a query.

SQL:
SELECT tblChurchDate.ChurchDate, tblFunction.Function, tblPeople.Name
FROM tblChurchDate INNER JOIN ((tblChurchDateFunctionPeople INNER JOIN
tblFunction ON tblChurchDateFunctionPeople.FunctionID = tblFunction.
FunctionID) INNER JOIN tblPeople ON tblChurchDateFunctionPeople.PeopleID =
tblPeople.PeopleID) ON tblChurchDate.ChurchDateFunctionID =
tblChurchDateFunctionPeople.ChurchDateID
ORDER BY tblChurchDate.ChurchDate, tblFunction.FunctionID, tblPeople.
PeopleID;

The reason I am doing this is to get the table structure and the SQL to
create a mySQL db for a website. I am just starting to learn how to do a
mySQL. Now I have to see how to get this to work on a web page.

Wish me luck.

Thank you Fred & Steve.
Scott


Fred wrote:
Scott,

I only briefly perused the subsequent exchange, but if you make a solid
decision on the entities being database and then a solid table structure
based on that, then I think you job will be much easier / more successful.

The first question is whether "dates" are an entity that needs databasing
vs. just an attribute of other entities.

If the answer is yes, the next question is whether or not your "date"
entity has other 1:1 attributes that you will be actively databasing. That
will make the difference between a true data table vs. a table that is just
there to populate dropdown lists.

Either way, I suspect that you will need to ditch a few of your tables and
your main 3 tables should be:

People

Functions

InstancesOfAPersonServingAFunctionOnAParticulerDa te (of course, shorten
that). This table will have FK's for People and Function and probalby just a
date field, but possible a FK for a dates table, if the latter is truly an
entity that needs databasing.

Well, there's my 2 cents.



Even if the answer is the former, then the next question is whether or not
you need to


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200912/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:33 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.