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
|
|||
|
|||
Recordning Attendance of Individuals stored in two separate tables
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? |
#2
|
|||
|
|||
Recordning Attendance of Individuals stored in two separate tables
On Thu, 13 Aug 2009 09:43:01 -0700, Carlo
wrote: 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! -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Recordning Attendance of Individuals stored in two separate ta
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 "John W. Vinson" wrote: On Thu, 13 Aug 2009 09:43:01 -0700, Carlo wrote: 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! -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Recordning Attendance of Individuals stored in two separate ta
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. "Carlo" wrote: 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 "John W. Vinson" wrote: On Thu, 13 Aug 2009 09:43:01 -0700, Carlo wrote: 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! -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Recordning Attendance of Individuals stored in two separate ta
On Aug 13, 2:29*pm, Carlo wrote:
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 "John W. Vinson" wrote: On Thu, 13 Aug 2009 09:43:01 -0700, Carlo wrote: 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? 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... |
Thread Tools | |
Display Modes | |
|
|