problems with referential integrity (1 Viewer)

So I just learned about referential integrity and I got it working on my database. I deleted all my "test records" and did a compact and repair to reset the primary key back to 1 and now it's acting all strange and adding random values to the table.

Background:
I have a pretty complex database with multiple tables but there are two tables that I'm having a problem with.

Table 1: Incident - stores values dealing with a specific fire department call
table 2: incident_sub - stores values of what apparatus was taken out, the times logged for that apparatus (when it left the station, arrived to scene, etc...) and the people on board

Table 1 and table 2 primary key is just an autonumber
Table 2 has a field called [incident_num] that gets the value of the incident key

You may have multiple records in table 2 that relate to a single call in table 1 (more than 1 vehicle went on a call)

I wanted to make it so if you delete the call, it deletes all the records associated with that call in table 2 so I created a referential integrity relationship with cascade delete and update.

It was working great until i removed all the records in both tables, and did a compact and repair. Now when i open the form to enter the values (table 2 is a subform), it automatically populates with random data in the personnel field.

I tried to remove the relationship and re-establish it and it errors out now saying that it cannot create the relationship and enforce referential integrity. Both tables are empty and it worked before I deleted the records and did a compact and repair.

Lastly i'm typing this on my break at work so I didn't have time to re-read it to make sure everything makes complete sense. If i need to clarify anything, please let me know.

I'm really confused where it's populating these random values from. It's different each time. It's almost like it still remembers the records that were in the tables before I deleted them and did the compact and repair and now it's "remembering" the values and putting them back into the new records.

Any help or direction would be great!

Thanks!!

Hi. Would you be able to post a small copy of your database with sample data?

I just attached the whole database.

I'm also open to any criticism people may have in regards to it. I'm not an access expert and am learning on the fly. I'm sure there are a lot better and easier ways to doing things I've done.

The database I attached has not data in both tables because I want to show you how it enters information randomly. To navigate to the part where it does it:

Open the database and under username "Test Employee" and password is "test" then the buttons will become enabled. Click "Call / Incident" and it pops up. If you go to the "aparatus / times" tab and select E3, it will just randomly populate the Personnel field with values.

Ok so I pulled up the backup database that had the previous records (that I deleted) and compared it to the DB that I sent you (that have no records) and the data it's pulling (the random personnel names) isn't random and are the names that were on the records that I deleted. So if I deleted those records, why are the new records populating with the names?

Hi. This sounds like a bug to me. In case you haven't heard it before, first, using lookup fields at the table level causes more trouble or confusion than necessary, and second, using multi-value fields is just as bad as simple lookup fields. Most developers try to avoid using either of those.

So just and update...

I took the old database (my backup) before i created the referential integrity relationship and deleted all the data in both those tables, did a compact and repair (to reset keys to 1) and then created the relationship.

That seems to work and it won't enter random values.

But something obviously trigger it to populate the values and I don't want it to start doing it in the future if I do a "compact and repair" so I'm still trying to figure out why it's doing that. Maybe some other table relationship triggering it?

Hi. I took your file and viewed the relationship window. I noticed RI is not enforced between incident_sub and personal. After I enforced it, I stopped getting the old data. Hope it helps...

Unfortunately this is not a bug, it is doing what it is supposed to do. There are just a lot of things to fix.

If you go to the "aparatus / times" tab and select E3, it will just randomly populate the Personnel field with values

Click to expand...

That is not random. It is just showing the first record.

MajP said:

Unfortunately this is not a bug, it is doing what it is supposed to do. There are just a lot of things to fix.

That is not random. It is just showing the first record.

Click to expand...

Hi. Here's what happened with me. Please verify if you're seeing something else. When I select E3, I see some names in the combo (remember these names). When I hit Cancel and say yes, then select E3 again, a different set of names show up in the combo. Do the above steps a few more times and you'll see either different names each time or maybe go back to previously displayed names. So, if you're also seeing different set of names, would you still say it's the "first" record? Even if it is, first record of what?

MajP said:

Unfortunately this is not a bug, it is doing what it is supposed to do. There are just a lot of things to fix.

That is not random. It is just showing the first record.

Click to expand...

Correct, it’s not random and I know it’s supposed to pupulate with the data that’s associated with that ID but where is it getting that data? The tables are empty? Did it create a table in the background that it’s pulling from?

If I delete all the records in both tables and create a new “call”, when I put “E3” it automatically pulls the names from the records that were deleted. Like the tables are empty but a table exists in the background that it’s not linked to.

I know there is a lot to fix. I’m trying to learn as I go as this isn’t my skill by trade. I plan on going back and fixing stuff as I learn better ways of doing stuff. But if you point out things that I could look at to fix, I’ll do that. I’m also not saying “fix my program”. I learn best by doing, screwing up, and fixing.

theDBguy said:

Hi. Here's what happened with me. Please verify if you're seeing something else. When I select E3, I see some names in the combo (remember these names). When I hit Cancel and say yes, then select E3 again, a different set of names show up in the combo. Do the above steps a few more times and you'll see either different names each time or maybe go back to previously displayed names. So, if you're also seeing different set of names, would you still say it's the "first" record? Even if it is, first record of what?

Click to expand...

Exactly! The names arnt random. They used to be records before I deleted them... why are they still pulling and from where? That’s where I’m stumped?

dcf1999 said:

Exactly! The names arnt random. They used to be records before I deleted them... why are they still pulling and from where? That’s where I’m stumped?

Click to expand...

Hi. They are coming from a hidden table, which was created when you made Personal a MVF (multi-value field). Have you tried what I said earlier of setting RI for the link between Personal and Incident_Sub?

theDBguy said:

Hi. They are coming from a hidden table, which was created when you made Personal a MVF (multi-value field). Have you tried what I said earlier of setting RI for the link between Personal and Incident_Sub?

Click to expand...

Thank you, that seems to have worked!

I do have a couple questions though in regards to things you said prior.

I heard about not using lookup fields at table level, and I plan on changing that. For now I'm going to deploy the program and work on that at home and update it at the fire dept after I finish fixing it.

1) Your comment about avoiding multi-value fields.. How would you accomplish that with what I have now (needing multiple people on a vehicle)? Would you create another table that links to table 2 with an ID and Names fields where the ID field links to the ID field of table 2? Then form wise, put a subform within the subform?

2) With my "relationships" page, I notice there are multiple instances of certain tables (i.e. Personal, Personal_1, Personal_2). Is that normal or should all the relationships be linked to just one table (Personal). I didn't manually create any relationships besides the RI one. They were all created automatically as I was creating lookups and queries. I'm going to read up on "relationships" at work later tonight. I'm sure I can make things a lot easier for myself if I understand them better (in regards to creating reports and queries)

Thanks for your help on this! I feel like this program was supposed to be a simple database that spiraled out of control as I tend to do... This one is starting to get a little over my head. I have plenty of questions on how to do/changes things to make it more efficient, but I plan on trying to figure those out on my own and ask questions when I'm really stumped!

Sometimes I reget telling them I "dabbled" in Access :banghead: but I do find it fun to make these things and learn.

So playing around with it more:

I have 2 copies of the same database (with the same sample data and no RI's created)

Copy 1: I create the RI's (between incident_id & ID and between Personal & ID) then delete the data in the tables and do a compact and repair.

Copy 2: I delete the data in the tables, do a compact and repair then create the RI's

Copy 1 populates the "phantom" data in the personal field while copy 2 doesn't populate any data and works just fine (even if I create records and delete them and do a repair and compact)

So I'm thinking it must be because I had some data in the tables before I created the RI's and when it linked them it somehow corrupted the tables and "repairing and compacting" it made it worse because if I start fresh with no data (as in copy 2) everything works fine... even if i create test records and later delete them all and run the repair and compact...

Does this sound legit or am I screwing something up by going into the table and deleting the records and running a repair and compact? Again on the database I attached to this thread, I did the RI between personal and Incident_sub and it worked until I deleted the records and did a R&C.

This is driving me crazy... I'm about to abandon the idea of the RI and cascade delete and just let there be orphan records in the incident_sub table and delete those orphans as needed. Yea it's not right (and that drives me nuts) but until I figure this out, I cannot risk a bunch of data getting messed up a year later when someone creates a record than deletes it for some reason.

dcf1999 said:

Actually I lied.. It doesn't work now. So it was working fine as stated above until I deleted the records in the [incidents] table and repaired and compacted the database... now it's doing the same thing...

Click to expand...

I still say this is a bug until I find a better explanation from people smarter than me.



Regarding your questions above:


1. Yes, you got it right. MVF simply does what you're describing behind the scenes (i.e. creating a hidden child table to store the related records), which adds to the user's confusion. If you don't use MVF, then you'll just have to create the child table yourself and use form/subform setup. It's just the same as if you don't use the lookup tab in the table design view, you'll just have to use a combobox on your form to aid the user in selecting valid choices for that field.


2. The Relationship Window is very limited in trying to represent the actual relationship constraints enforced in the table schema. The duplicate tables is Access' way of showing how one table could related to another using more than one foreign key. For example, you had a relatonship between the Incidents and Personal table where you have foreign keys for the person who entered the incident into the database and the crew assigned to the incident (all persons coming from the same table - Personal).


Hope it makes sense...

theDBguy said:

I still say this is a bug until I find a better explanation from people smarter than me.



Regarding your questions above:


1. Yes, you got it right. MVF simply does what you're describing behind the scenes (i.e. creating a hidden child table to store the related records), which adds to the user's confusion. If you don't use MVF, then you'll just have to create the child table yourself and use form/subform setup. It's just the same as if you don't use the lookup tab in the table design view, you'll just have to use a combobox on your form to aid the user in selecting valid choices for that field.


2. The Relationship Window is very limited in trying to represent the actual relationship constraints enforced in the table schema. The duplicate tables is Access' way of showing how one table could related to another using more than one foreign key. For example, you had a relatonship between the Incidents and Personal table where you have foreign keys for the person who entered the incident into the database and the crew assigned to the incident (all persons coming from the same table - Personal).


Hope it makes sense...

Click to expand...

Makes sense.

I feels like a bug... but I'm sure I'm missing something ...

theDBguy said:

I still say this is a bug until I find a better explanation from people smarter than me.



Regarding your questions above:


1. Yes, you got it right. MVF simply does what you're describing behind the scenes (i.e. creating a hidden child table to store the related records), which adds to the user's confusion. If you don't use MVF, then you'll just have to create the child table yourself and use form/subform setup. It's just the same as if you don't use the lookup tab in the table design view, you'll just have to use a combobox on your form to aid the user in selecting valid choices for that field.


2. The Relationship Window is very limited in trying to represent the actual relationship constraints enforced in the table schema. The duplicate tables is Access' way of showing how one table could related to another using more than one foreign key. For example, you had a relatonship between the Incidents and Personal table where you have foreign keys for the person who entered the incident into the database and the crew assigned to the incident (all persons coming from the same table - Personal).


Hope it makes sense...

Click to expand...

Like I said before (maybe you didn't see the update as you sent this before I posted it) but maybe it's the way I went about creating the RI's that's corrupting something:

creating the RI's before deleting the old data and repair/compacting (doesn't work)

vs

deleting all the records (starting fresh) then creating the RI's (works)

dcf1999 said:

Like I said before (maybe you didn't see the update as you sent this before I posted it) but maybe it's the way I went about creating the RI's that's corrupting something:

creating the RI's before deleting the old data and repair/compacting (doesn't work)

vs

deleting all the records (starting fresh) then creating the RI's (works)

Click to expand...

Well, to my mind, it should work either way. So, I am going with "a bug" until I hear otherwise. I don't have any spare time right now, but some tests might shed some more light as to which way to go to fix this.