relationships of 3 tables

[Scott_Brasted via AccessMonster.com]

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

 

[Steve]

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
(e-mail address removed)

 

[Steve]

In TblChurchDateFunctionPeople, make DateFunctionID ChurchDateFunctionID.

Steve

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
(e-mail address removed)

 

[Scott_Brasted via AccessMonster.com]

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

In TblChurchDateFunctionPeople, make DateFunctionID ChurchDateFunctionID.

Steve

Hi Scot,

[quoted text clipped - 81 lines]

 

[Fred]

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

InstancesOfAPersonServingAFunctionOnAParticulerDate (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

 

[Steve]

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
(e-mail address removed)

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

In TblChurchDateFunctionPeople, make DateFunctionID ChurchDateFunctionID.

Steve

Hi Scot,

[quoted text clipped - 81 lines]

Thanks,
Scott

 

[Scott_Brasted via AccessMonster.com]

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