Attendance database ?relationship problems

P

Phil

I am attempting to set up an attendance database:

the tables I have are:

Attendees
=======
AttendeeID (PK) 1:n with Registration
1:n with Attendance
Demographic data
e.g firstname, surname etc.

Events
=====
EventID (PK) 1:n with Registration
Eventname
Spaces etc.

Registration
========
RegistrationID(PK)
AttendeeID
EventID
RegFee etc

These three table I think are OK the events database works how I want with
these
table. the remaining tables to add attendence are:

EventsLocation
==========
ID(PK) 1:n with attendance
EventID
DayNo.
Numberofdays
Dateofday
venueam
venuepm

Attendence
========
AttendenceID (PK)
AttendeeID
ID
AttendenceDate
Attended (Y/N)

this is where I am stuck, I would like to have a form open from my events
form, which would have a list of each person on the course with each date and
tick boxes

e.g
12/6/06 13/6/06 14/6/06
Michael Mouse [] [] []

I cannot think of a way to get data into the table, I have tried queries but
when I add the attendance table I get no records back (probably because there
are no records in the table)

I am stuck and need help

hope someone can help

Thanks

Phil
 
D

Duane Hookom

Depending on the number of registrations per event, I would try create two
list boxes on the event form. The first would allow you to select a single
EventsLocation record. The second list box would be multi-select of the
Attendees registered for the Event. Users could select an event and multiple
attendees then click a button to append records to the Attendence table.

You could add other functionality to display current attendees or just show
them in a subform linked to the event and the selected EventsLocation.
 
D

Duane Hookom

Your Attendence table should have a unique index defined on AttendeeID,
AttendenceDate, and ID fields so there can be no duplicates. You can then
append all registrants to the table for a particular ID and date.

--
Duane Hookom
MS Access MVP


Phil said:
Hi Duane

Thanks for your reply. I would like the register to open from the events
form so the event would be the active record from that form (your third
option).

I have about 20 registrants on each event, I would like a list on the
formof
all participants with a tick box for yes/no attended for each date, I just
do
not know how to get this onto a form, it might be because all the info
comes
form different tables, I originally created an append query to populate
the
table but each time I ran the query it added the same records again and
again. I dont know if I have problems with my relationships but I cannot
get
it to work.

Thanks

Phil

Duane Hookom said:
Depending on the number of registrations per event, I would try create
two
list boxes on the event form. The first would allow you to select a
single
EventsLocation record. The second list box would be multi-select of the
Attendees registered for the Event. Users could select an event and
multiple
attendees then click a button to append records to the Attendence table.

You could add other functionality to display current attendees or just
show
them in a subform linked to the event and the selected EventsLocation.

--
Duane Hookom
MS Access MVP

Phil said:
I am attempting to set up an attendance database:

the tables I have are:

Attendees
=======
AttendeeID (PK) 1:n with Registration
1:n with Attendance
Demographic data
e.g firstname, surname etc.

Events
=====
EventID (PK) 1:n with Registration
Eventname
Spaces etc.

Registration
========
RegistrationID(PK)
AttendeeID
EventID
RegFee etc

These three table I think are OK the events database works how I want
with
these
table. the remaining tables to add attendence are:

EventsLocation
==========
ID(PK) 1:n with attendance
EventID
DayNo.
Numberofdays
Dateofday
venueam
venuepm

Attendence
========
AttendenceID (PK)
AttendeeID
ID
AttendenceDate
Attended (Y/N)

this is where I am stuck, I would like to have a form open from my
events
form, which would have a list of each person on the course with each
date
and
tick boxes

e.g
12/6/06 13/6/06 14/6/06
Michael Mouse [] [] []

I cannot think of a way to get data into the table, I have tried
queries
but
when I add the attendance table I get no records back (probably because
there
are no records in the table)

I am stuck and need help

hope someone can help

Thanks

Phil
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top