how to build attendence database

I currently have a events database that stores details of courses and their
attendees. I would like to add an attendance part to this datbase so I can
see when people attended (or not).

The tables I have so far are

Attendees
=======
which stores attendee demographic data, primary key is AttendeeID (autonumber)

Events
======
Stores details of events, primary key is EventsID (autonumber)

Registration
=========
stores details of attendee registration on events PK is RegistrationID
(autonumber)

tblEventsLocation
===========
stores dates and location for events PK tblEventslocationID (autonumber)


I am a little stuck on how to do a attendance table, the courses and events
are multiday courses, I am sure I need another table called

Attendance
=======

to store details of attendance on each date. but what details do I need in
the table and how should it be linked with other parts of the database?????


Attendance would only be either yes or no so.


any help would be greatly appreciated, thanks in advance


Phil

Hi Phil

It may be an idea to have the following tables (have used basic names)

People
Payments
Events
Dates (only needed if event are spread over multi-dates)
EventLocations (only needed if the same event is spead over multi-locations)
Bookings (this where you attendees field would go)

People
ID
1stName
2ndName
DoB
Address
etc, etc
PUS (Important) an extra field
we use the all the time and they are normally called [extra])

Payments
ID
Amount
Method
etc,etc

Events
ID
Title
Location - link with tblEventLocations
Date - link with tblDates
Basic discription

EventLocations
ID
Where - Link with tblDates if moveable

Bookings
ID
This is One side of the Many

Note each person can have been BookingID's so can book more than one event
Each person can attend event on all or some of the dates
Each person can have more than one payment for each event

If you create a form you could create the Main form from the tblPeople and
have tabs with subforms linked to the person.

In this case case you would need to create a "set value" for [extra] taken
from the booking ID (normally OnOpen) This is what makes the whole thing work.

Bit mad I know but we have many event DB'S and it seems to work best from
all the one-to-many types we have tried.

Hope this helps



--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo :-)
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.


"Phil" wrote:

I currently have a events database that stores details of courses and their
attendees. I would like to add an attendance part to this datbase so I can
see when people attended (or not).

The tables I have so far are

Attendees
=======
which stores attendee demographic data, primary key is AttendeeID (autonumber)

Events
======
Stores details of events, primary key is EventsID (autonumber)

Registration
=========
stores details of attendee registration on events PK is RegistrationID
(autonumber)

tblEventsLocation
===========
stores dates and location for events PK tblEventslocationID (autonumber)


I am a little stuck on how to do a attendance table, the courses and events
are multiday courses, I am sure I need another table called

Attendance
=======

to store details of attendance on each date. but what details do I need in
the table and how should it be linked with other parts of the database?????


Attendance would only be either yes or no so.


any help would be greatly appreciated, thanks in advance


Phil

Phil:

It looks to me like you need an Attendances table with at least the
following columns:

AttendeeID (long integer number)
EventLocationID (long integer number)
Attended (Boolean (Yes/No))

AttendeeID and EventLocationID should be the composite primary key of the
table. If you want to record attendances for the whole of an EventLocation
rathere than for each day you can automatically generate the rows for a
particular EventLocation with all registered participants for that event,
ready for the Attended column to be manually updated to TRUE for those who
attended. On a form bound to the tblEventLocations for instance you could
have a button to generate the Attendances rows for the current EventLocation
with code like this in its Click event procedu

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

' insert new rows into Attendances table
strSQL = "INSERT INTO Attendances(AttendeeID, EventLocationID)" & _
" SELECT AttendeeID, " & Me.EventLocationID & _
" FROM Registration " & _
" WHERE EventsID = " & Me.EventsID

cmd.CommandText = strSQL
cmd.Execute

Set cmd = Nothing

If you want to record attendances for each day of a multi-day EventLocation
(assuming this table has start and end date columns) then you'd need to add
an AttendanceDate column to the Attendances table and develop the above
routine further so it iterates through each day in the range and inserts a
row for each, e.g.

Dim cmd As ADODB.Command
Dim strSQL As String
Dim dtmDate As date

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

' insert new rows into Attendances table
For dtmDate = Me.StartDate To Me.EndDate
strSQL = "INSERT INTO Attendances" & _
"(AttendeeID, EventLocationID, AttendanceDate)" & _
" SELECT AttendeeID, " & _
Me.EventLocationID & ", "#" & _
FORMAT(dtmDate,"mm/dd/yyyy") & "#" & _
" FROM Registration " & _
" WHERE EventsID = " & Me.EventsID

cmd.CommandText = strSQL
cmd.Execute
Next dtmDate

Set cmd = Nothing

Ken Sheridan
Stafford, England

"Phil" wrote:

I currently have a events database that stores details of courses and their
attendees. I would like to add an attendance part to this datbase so I can
see when people attended (or not).

The tables I have so far are

Attendees
=======
which stores attendee demographic data, primary key is AttendeeID (autonumber)

Events
======
Stores details of events, primary key is EventsID (autonumber)

Registration
=========
stores details of attendee registration on events PK is RegistrationID
(autonumber)

tblEventsLocation
===========
stores dates and location for events PK tblEventslocationID (autonumber)


I am a little stuck on how to do a attendance table, the courses and events
are multiday courses, I am sure I need another table called

Attendance
=======

to store details of attendance on each date. but what details do I need in
the table and how should it be linked with other parts of the database?????


Attendance would only be either yes or no so.


any help would be greatly appreciated, thanks in advance


Phil

Hi Ken

thanks for this, I have set up the table and form, I would like to capture
attendance for each date as in your second example, however your code
assumes I have a start date and end date, unfortunatelty I do not have this I
have a field [daynumber] which is 1,2 3,etc and a field [numberofdays] which
is how long the course is. the start date is always day1 and enddate is when
daynumber and numberofdays are the same. how would I alter your code to take
account of this.

I hope you can help

thanks

Phil

"Ken Sheridan" wrote:

Phil:

It looks to me like you need an Attendances table with at least the
following columns:

AttendeeID (long integer number)
EventLocationID (long integer number)
Attended (Boolean (Yes/No))

AttendeeID and EventLocationID should be the composite primary key of the
table. If you want to record attendances for the whole of an EventLocation
rathere than for each day you can automatically generate the rows for a
particular EventLocation with all registered participants for that event,
ready for the Attended column to be manually updated to TRUE for those who
attended. On a form bound to the tblEventLocations for instance you could
have a button to generate the Attendances rows for the current EventLocation
with code like this in its Click event procedu

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

' insert new rows into Attendances table
strSQL = "INSERT INTO Attendances(AttendeeID, EventLocationID)" & _
" SELECT AttendeeID, " & Me.EventLocationID & _
" FROM Registration " & _
" WHERE EventsID = " & Me.EventsID

cmd.CommandText = strSQL
cmd.Execute

Set cmd = Nothing

If you want to record attendances for each day of a multi-day EventLocation
(assuming this table has start and end date columns) then you'd need to add
an AttendanceDate column to the Attendances table and develop the above
routine further so it iterates through each day in the range and inserts a
row for each, e.g.

Dim cmd As ADODB.Command
Dim strSQL As String
Dim dtmDate As date

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

' insert new rows into Attendances table
For dtmDate = Me.StartDate To Me.EndDate
strSQL = "INSERT INTO Attendances" & _
"(AttendeeID, EventLocationID, AttendanceDate)" & _
" SELECT AttendeeID, " & _
Me.EventLocationID & ", "#" & _
FORMAT(dtmDate,"mm/dd/yyyy") & "#" & _
" FROM Registration " & _
" WHERE EventsID = " & Me.EventsID

cmd.CommandText = strSQL
cmd.Execute
Next dtmDate

Set cmd = Nothing

Ken Sheridan
Stafford, England

"Phil" wrote:

I currently have a events database that stores details of courses and their
attendees. I would like to add an attendance part to this datbase so I can
see when people attended (or not).

The tables I have so far are

Attendees
=======
which stores attendee demographic data, primary key is AttendeeID (autonumber)

Events
======
Stores details of events, primary key is EventsID (autonumber)

Registration
=========
stores details of attendee registration on events PK is RegistrationID
(autonumber)

tblEventsLocation
===========
stores dates and location for events PK tblEventslocationID (autonumber)


I am a little stuck on how to do a attendance table, the courses and events
are multiday courses, I am sure I need another table called

Attendance
=======

to store details of attendance on each date. but what details do I need in
the table and how should it be linked with other parts of the database?????


Attendance would only be either yes or no so.


any help would be greatly appreciated, thanks in advance


Phil