do not allow duplicates in subform

[Kathy R.]

I have a form/subform in which I track attendance. The main form holds
the date/event information, the subform holds the attendees. I want to
limit the subform so that it does not have any duplicates per event
date. The underlying table should allow duplicates as someone could
attend on different dates. Would this be done with code, or is there
something to be set on the Property Sheet? If it is with code, could
you give me an example?

Main Form - frmAttendance (EventID, EventDate, EventName)
Subform (continuous form) - sfrAttendee (IndID, FirstName, LastName)


Thanks in advance!
KathyR.

 

[Tom van Stiphout]

On Tue, 02 Feb 2010 20:45:56 -0500, "Kathy R."

Start with the database design. It appears you have generic events
that are held on various dates, and each occurrence is attended by
several people. You want to avoid signing up people more than once for
the same date. Can you confirm this is your situation?

-Tom.
Microsoft Access MVP

 

[Kathy R.]

Tom,
I'm not sure what you mean by "generic events." A person could attend
two different events on the same date. I want to avoid "marking" them
as attending the same event more than once.
My table information:

tblIndividual
IndID
FirstName
LastName

tblEvent
EventID
EventName
EventDate

tblAttendance (a join table, if I've got the terminology right)
AttendID
AtIndID
AtEventID

Kathy R.

On Tue, 02 Feb 2010 20:45:56 -0500, "Kathy R."

Start with the database design. It appears you have generic events
that are held on various dates, and each occurrence is attended by
several people. You want to avoid signing up people more than once for
the same date. Can you confirm this is your situation?

-Tom.
Microsoft Access MVP

also includes AtEventID
The Master/Child fields are Event ID and AtEventID

 

[Tom van Stiphout]

On Wed, 03 Feb 2010 01:06:27 -0500, "Kathy R."

With genereic event I mean an event that may recur on different dates.
For example "Safety Training 101". It is not given once, but perhaps
once every quarter. In that case your design is incorrect, and the
EventDate should move to the Attendance table.
Bw, there typically is no need to add an autonumber (if that's what
you used) to the Attendance table: AttendID is redundant.

Perhaps you have unique events, which do occur only once. The database
design would be different.

-Tom.
Microsoft Access MVP

 

[Kathy R.]

First, thank you for your patience!

I’ll start at the beginning as I’m not sure which way it should be. I
believe I want each event to be unique, not generic. I’ll be tracking
attendance for church services. The unique event would be “worship
service on 2/7/10.”

I would like to be able to pull up a report to see who has been missing
for, say, three out of five Sundays. I also want to be able to easily
create reports that show what services John Doe has been attending, or
conversely, a list of individuals that attended a specific service.

For my purposes, I need to know that John Doe attended the early service
on 2/7/10. If I were to use the same set up for a class that runs over
several weeks, I only need to know that John Doe participated in “Safety
Training 101,” which began on 2/8/10. I wouldn’t need to know which
dates he was there, only that he participated.

If I put the EventDate in the Attendance table, wouldn’t I be entering
the same date multiple times – once for each individual that attended
that event? Isn’t that breaking the normalization rules?

I hope I’m making sense. I keep running it through my head and seem to
be going in circles.