Recordning Attendance of Individuals stored in two separate tables

[Carlo]

I have a databse recording the details of Adults and Children:

tbl Adult Users
=========
Adult User ID (PK, Autonumber)
Adult First Name
Adult Last Name
etc

tbl Child Users
=========
Child User ID (PK, Autonumber)
Child First Name
Child Last Name
etc

I also store details of events that go on at our community centre in a
separate table, and then need to record the attendance of the adults or
children in a third table. However, I am wondering how I do that, given that
the attendence table would presumably look something like:

tbl Event Attendance
=============
Attendance Ref Number (PK, Autonumber)
Event Ref Number (FPK of the event table)
+ an attendee recording field

However, the primary keys for both the adult and children tables are std
autonumbers, and in any case, how would I select the appropriate attendee
from a list?

 

[John W. Vinson]

I have a databse recording the details of Adults and Children:

tbl Adult Users
=========
Adult User ID (PK, Autonumber)
Adult First Name
Adult Last Name
etc

tbl Child Users
=========
Child User ID (PK, Autonumber)
Child First Name
Child Last Name
etc

I also store details of events that go on at our community centre in a
separate table, and then need to record the attendance of the adults or
children in a third table. However, I am wondering how I do that, given that
the attendence table would presumably look something like:

tbl Event Attendance
=============
Attendance Ref Number (PK, Autonumber)
Event Ref Number (FPK of the event table)
+ an attendee recording field

However, the primary keys for both the adult and children tables are std
autonumbers, and in any case, how would I select the appropriate attendee
from a list?

It would be quite difficult, because your decision to use two different tables
for one type of entity (a person who can attend events) was misguided.

This would be much easier if you had ONE People table with an autonumber ID
and a field indicating whether the person is an adult or a child.

If you are stuck with the current design, you'll need to use a UNION query
joining the adult and child tables, with an additional field indicating
adult/child status, and some VBA code to provide the link. Tricky and I'm not
sure it would actually work!

 

[Carlo]

HI John and Steve

Thanks for getting back to me. Unfortunately I am stuck with the design
(and it is useful in other respects, such as associating adults and children
via a custodianship table). However, what you said about a UNION query set
me thinking: do you think it would work if I set up two subforms to record
attendance, one for children and one for adults, and then used a query to
combine the attendance data as opposed to the adult and child personal data?
That would presumably allow me to manipulate it and output reports by
ethnicity and other fields which are common to both tables, wouldnt it? I
dont know much (anything) about this sort of process, so any guidance would
be appreciated!

Thanks a lot

Carlo

 

[CraigH]

Hi,
Because you are stuck with the design with 2 "People" tables - (you are on
the correct path thinking you need to use a union query) -- first your
table....

tbl Event Attendance
=============
Attendance Ref Number (PK, Autonumber)
Event Ref Number (FPK of the event table)

.... can be modified to have
AdultUserID (FPK of Adult table) and
ChildUserID (FPK of Child table)

And thus your quesion:

However, the primary keys for both the adult and children tables are std
autonumbers, and in any case, how would I select the appropriate attendee
from a list?

1. You will be able to have 2 values that might be the same for attendee at
the same event.
2. You will select the appropriate field to add the attendee (ONLY ONE PER
AttenedeeRefNum). I would suggest 2 subforms on your event form -- 1 for
adult and 1 for child attendees and not show the other field.

To expand on / and modify what John said.

If you are stuck with the current design, you'll need to use a UNION query
joining the adult and child tables,

You can now have your Union query with a select for the ChildUserID and a
select for the AdultUserID.

with an additional field indicating adult/child status,

Kind of built into the field name so nothing extra- except if in the union
you need to distinguish the two - all you need is another field in each
select query (in Child the field "IsChild: True"

and some VBA code to provide the link. Tricky and I'm not
sure it would actually work!

Since you have two different subforms and this is a different approach no
VBA coding is needed.

 

[Piet Linden]

HI John and Steve

Thanks for getting back to me.  Unfortunately I am stuck with the design
(and it is useful in other respects, such as associating adults and children
via a custodianship table).  However, what you said about a UNION queryset
me thinking: do you think it would work if I set up two subforms to record
attendance, one for children and one for adults, and then used a query to
combine the attendance data as opposed to the adult and child personal data?  
That would presumably allow me to manipulate it and output reports by
ethnicity and other fields which are common to both tables, wouldnt it? I
dont know much (anything) about this sort of process, so any guidance would
be appreciated!

Thanks a lot

Carlo

I agree with John... you're going to make your life really hard if you
put the two types of people into different tables. I had this problem
once... Probably the easiest way to solve it is to have a field in
your Person table called "ParentID" (or whatever you want to call it
that refers back to a child's parent record id.) And then you just
make it NOT required. This design would allow you to enter parents
and children and indicate in the *child* which parent he is related
to. Then you can just join that Person table to attendance and your
problem is solved. You could still *show* the children of a parent in
the form, but it would not be editable...