Delete record not cascading through tables

[Guest]

Dear All

I Have been here a few weeks trying to get a attendance database up and
running. I have had a lot of help so far. Duane Hookum helped me on Tuesday
and I thought I was there but have hit another wall.

My table structure is: -

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
EventID (PK)
AttendeeID (PK)
ID (PK)
AttendenceDate
Attended (Y/N)

I add records to the Attendence table using an update query which work
perfectly, the problem I have is when I delete an attendees registration on
an event, this does not cascade to the attendance table to that attendee
attendence records stay in the table. I have tried adding a RegistrationID
and a 1:m relationship but it still does not cascade the deletes. any help
would be greatly appreciated

Thanks

Phil

 

[Duane Hookom]

I'm not sure why you have the EventID (PK) in Attendence since the ID field
allows you to lookup the EventID in the EventsLocation table.

 

[Duane Hookom]

Looking at this again, I would think you could use:
Attendence
========
AttendenceID
RegistrationID
ID (PK)
AttendenceDate
Attended (Y/N)

I also would replace the YN field with an integer since someone down the
road may want to store with the absense was excused or not or ...

 

[Guest]

Duane

I have tired your solution but I cannot change ID to the PK as their are
many records with the same ID, As many people will attend each date of the
event

I have changed the attended to an integer this makes more sense.

I see you have taken AttendeeID out of the table is this because I would get
the attendee data from the registration data????

sorry if I seem thick Access really confuses me most of the time then I have
episodes of enlightenment.

thanks for your help and time

Phil

 

[Duane Hookom]

Sorry, I didn't want you to make the ID field in Attendence a primary key.It
was a copy and paste issue and should have been removed with the other
fields. I assume AttendanceID is an autonumber primary key.

"I see you have taken AttendeeID out of the table is this because I would
get the attendee data from the registration data????" Yes

 

[Guest]

Yes attendanceID is an autonumber I did have that as the PK but when I used
the append query I kept getting duplicate records added, your original
solution worked well, my main problem is deleted records not cascading
through the database

thanks

Phil

 

[Guest]

You might want to check your "Relationship" diagram.

The properties for the linked tables should have "Enforce Referential
Integrity" and "Cascade Delete Related Records" enabled (check mark). Be
sure you want to do a physical delete, once the records are gone, they are
gone.

I always use a "logical" delete, by just enabling or disabling a logical
field for any given record (or records). That way you keep the data
physically for historical purposes (stats or whatever), and you can easily
retrieve the data in case you did not really want to delete it.

 

[Guest]

Hi Duane

Thanks again for your reply the relationships you discuss are how I have
the tables running at the moment it works well, had to change the query I
generated as it was not updatable (that was my fault) apart from not
cascading deletes everything works perfectly, I can put up with the odd extra
record in the table as it will not happen very often (I have will delete them
when they are on the attendance form)

thanks

Phil

 

[Guest]

Thanks for your reply

I have referential integrity checked, still does not work.

thanks

Phil