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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 | |
|
|