Adding Records to multiple tables

[Scott]

I am working on a database with the primary function of tracking attendance
for many people to many events. The database is set up so that there is a
table of personnel and a table of events where each user's name and each
event appear once respectively. Connecting these two tables is another table
which has a record containing each event for each individual. A yes/no is
then used to indicate that the individual has attended the event. For
example:
tblAttendance:
Attendance ID | UserID | EventID | Complete
1 | 1 | 1 | Yes
2 | 2 | 1 | Yes
3 | 1 | 2 | No
4 | 2 | 2 | Yes

What I need now is an administrative function to add people and events.
That is a function which when a new user is added to the database a new
record is added to the attendance table for each event in the event table.
The same process would then be applied to adding events. The process needs
to be very simple for the administrator of the database and manually copying
in new records from excel is not considered to be easy enough. Thank you.

 

[John W. Vinson]

I am working on a database with the primary function of tracking attendance
for many people to many events. The database is set up so that there is a
table of personnel and a table of events where each user's name and each
event appear once respectively. Connecting these two tables is another table
which has a record containing each event for each individual. A yes/no is
then used to indicate that the individual has attended the event. For
example:
tblAttendance:
Attendance ID | UserID | EventID | Complete
1 | 1 | 1 | Yes
2 | 2 | 1 | Yes
3 | 1 | 2 | No
4 | 2 | 2 | Yes

What I need now is an administrative function to add people and events.
That is a function which when a new user is added to the database a new
record is added to the attendance table for each event in the event table.
The same process would then be applied to adding events. The process needs
to be very simple for the administrator of the database and manually copying
in new records from excel is not considered to be easy enough. Thank you.

I would suggest a simpler structure: the third table should contain records
only for people who attended the event. The absence of a record for UserID 3
and EventID 4 would indicate that User 3 did not attend event 4; an "unmatched
query wizard" query would let you easily find which people did NOT attend a
particular event.

Data entry with such a structure is very easy: you would have a Form based on
the Users table (or the Events table, or perhaps both) with a Subform based on
the attendance table. You can use a combo box on the subform to select the
Events attended by a user (or the users attending an Event). If the number of
events (or users) isn't too big, you can - with a little code - use a
multiselect Listbox instead.

 

[Scott]

Thanks John, for your input. you may have misread the example table in my
last post (or I didn't explain it very well). The "AttendanceID" field is
only to create a unique primary key for the table. More importantly each
event does appear exactly once for each user. I already have a form (based
on tblUsers with tblAttendance subform) in which the user sees all records in
tblAttendance that correspond to their UserID. This works great for me and
there are a number of other reasons that I need the data structured like
this.

Right now when a new event occurs I copy the list of UserID's from tblUsers
and paste it into tblAttendance via tblEvent (I use the "+" icon to the left
of the record). This way, the next time a user accesses the database the new
event appears on their attendance form. I am looking for help in an easy way
to automate this.

Ideally something like a form with
TxtBox: Event Title TxtBox: Event Date
CmdButton: Execute VBA to add the two above fields to tblEvents
and all the necessary fields to tblAttendance

 

[John W. Vinson]

Thanks John, for your input. you may have misread the example table in my
last post (or I didn't explain it very well). The "AttendanceID" field is
only to create a unique primary key for the table. More importantly each
event does appear exactly once for each user. I already have a form (based
on tblUsers with tblAttendance subform) in which the user sees all records in
tblAttendance that correspond to their UserID. This works great for me and
there are a number of other reasons that I need the data structured like
this.

Right now when a new event occurs I copy the list of UserID's from tblUsers
and paste it into tblAttendance via tblEvent (I use the "+" icon to the left
of the record). This way, the next time a user accesses the database the new
event appears on their attendance form. I am looking for help in an easy way
to automate this.

Ideally something like a form with
TxtBox: Event Title TxtBox: Event Date
CmdButton: Execute VBA to add the two above fields to tblEvents
and all the necessary fields to tblAttendance

I did understand your structure.
I just disagree with it.

I would assert that there is no need to waste space in your database by
storing a record of a non-event. If your attendance table contains all the
records of events that people DID attend, *that is all you need*. You do not
need to store a record to assert that someone did NOT attend a meeting - a
query can easily detect the absence of a record for that person.

I would avoid using table datasheets (particularly subdatasheets, which put a
major drag on performance) for ANY interaction with data. A Form with a
Subform, as I suggested, will give a better user interface.

If you have some good reason to store 128 attendance records (with maybe 124
of them with no useful data) for the 128 people for every new event, you can
run an Append query in the AfterUpdate event of the Events form. How you will
handle getting records for new employees into the table for past events is
another issue.

 

[Evi]

In the TblAttendance Design View, create a Unique Index for UserID and
EventID. (Google "Duplicate Table Values" for this Newsgroup to see how)

If you have to add all users to all events then

Add the User table and the Event table to a query (but not the Attendence
table
Do not join the 2 tables.
Add UserID and EventID to the query.
Use the QueryType button to change it into an Append query.
The Unique Index will prevent you from entering a user more than once to
TblAttendance.
Perhaps you want to take a role call of or write letters to all those who
ought to be attending the event otherwise what John says is the best advice.

Evi

 

[Scott]

Thanks Evi, that sounds like the type of solution I'm looking for.