Relationship Help

[Susan in MD]

I am creating an access database for a Luncheon Program at a school. So I
have a table with childs name and grade. Then I have another table with all
of the dates for the luncheons. I need to create a form that I can view the
children's records (viewing them one at a time) and then see all of the
available luncheon dates (in a datasheet view) and say yes or no if they are
ordering a luncheon that day. I can't seem to get the relationship worked
out right that will allow me to do this so that it will be tied to that
child.

I know I am missing something simple here, but I am at a brick wall.
Thanks,

Susan

 

[Jeff Boyce]

Susan

If you have children, luncheons, and child-at-luncheon, you need three
tables, not two.

From your description, one child could sign up for many luncheons, and one
luncheon could have many children. The way to resolve this many-to-many
relationship is by using a "junction/resolver/relation" table that holds one
record for each valid pair.

To handle this in an application (NOT the same as the underlying tables),
you could use a main form for the children, and a subform for the
child-at-luncheon records.

Or, consider using paired listboxes (again, in forms, not in the tables).
The main form holds child info, the left listbox holds "available"
luncheons, and the right listbox holds luncheons for which the child has
signed up.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jerry Whittle]

You need a third table. A child can have many Luncheons and a Luncheon can
have many Childern. That's a classic Many-to-Many relationship that needs to
be broken into two One-to-Many relationships by using a third linking or
bridging table between the other two.

This middle table, let's call it Lunch, will have a foriegn key field to
store the primary key from the Child table and a foriegn key field to store
the primary key from the Luncheon table. Another nice touch is that this
table should have its own primary key (autonumber), a comments field for
things such as special diet information, plus an index on the two FK fields
with no duplicates allowed so that someone can't accidently add the same
child/luncheon info in twice.

To display this create a form for the Child table. Next create a subform for
the Lunch table that is linked by the Child PK /FK fields. In the subform you
could have a combo box showing dates from the Luncheon field. Selecting a
date means that the child is having lunch on that day.

To see what days the child isn't having lunch, you will need to create a
query/report based on all three tables with a right outer join on the
Luncheon table which will display blanks if the child isn't have lunch on a
certain day.

As you can see, you weren't really missing something simple here. It is
somewhat complicated, but worth doing right.