Referntial Integrity

[Jojojo]

I have set up a database for a Fitness Gym. I was testing something this morning and ran into a problem with referential integrity, I'm not sure if I should be enforcing it for these particular relationships now or not.



I want it so that, if a class is deleted from the gym - it does not cascade delete - I want the history to show up for the members that enrolled in this class in the past.

However - b/c I have a many to many relationship table set up that links Registrations and Classes together - it won't let me delete the class unless it no longer exists in the join table as it voilates referential integrity.

So I just not be enforcing referential integrity in this case or is there a solution that I don't know about? Thanks!!

[June7]

Why bother deleting the class record? There is seldom any real justification for deleting records. Just leave it there forever.

Did you save a record ID or the actual name of the class in the related table?

[Jojojo]

Why bother deleting the class record? There is seldom any real justification for deleting records. Just leave it there forever.

Did you save a record ID or the actual name of the class in the related table?

I have an ID. I only wanted current classes show up in the list of choices. I set it up originally to omit (in a query) from the class drop down list, those that had been marked "cancelled" and it deleted all if the classes that were already registered in the past. I'll see if I can try something a little different. Thanks!

[Jojojo]

I have attached a copy of the database. Can you take a look and let me know how to solve this.

An event, ex Mommies Time Out, Wednesday's at 10:45am, can run 1 session, and be cancelled later, and then re-ran again. So - I have a cancelled (yes/no) option - that is editable - so it can run or be cancelled at any time. (Events Form)

Under the Registration Form - I want the drop down of class choices to only show the classes that are running (omitting those marked cancelled from the ClassDEtailsQuery that the subform calls).

However, by doing this, b/c it the class no longer shows up in the drop down as a choice, it also blanks out from past registrations.

How do I keep it so that it doesn't blank out - the client needs to know that people registered when it was running, but have the option to only show those that are currently running? My thought in doing this is to avoid the drop down list of events from becoming full on classes that no longer run.

Thanks!

[June7]

That is the issue with multi-column comboboxes with lookup alias. The only alternatives I know are to save the actual class name instead of the ID and drop the referential integrity or not show the past registrations for cancelled classes.