Can't add change record related record required

[rgreene]

Can someone help me out by looking at my database I've been messing with it for 3 days and I can't figure it out.
I have 4 tables each are in a 1 to 1 relationship. From there I have a query that is for every field between the 4 tables. I then have a Tab Control form with 4 tabs and it uses the qryAll I have setup. when I enter on the form I can enter info on all 4 tabs but if I try to go to a different record I get the message "You can't add or change a record because a related record is required in tblScouts". I have tried so many different options. I had even posted yesterday trying to do the same thing but my tab controls had subforms on them. The_Doc_Man was helping me with that issue but I couldn't get that to go either.
Would anyone be willing to look at it for me and explain to me what I was doing wrong?

Rick

 

[Bat17]

all the join fields (possibly 6 for 4 tables) need to be in the query itself and on the form. If that does not sort it, strip a copy of the db to just those bits needed, clear most of the data, do a compact and repair then zip it up and post it here for someone to look at for you.

HTH

Peter

 

[neileg]

Why do you have 4 tables in a one to one? Why not just one table?

 

[rgreene]

Bat17 - I have the query that I'm running (qryAll and/or qryEverything) containg every possible field (I think). And each field is on one of the tabs somewhere (I think). LIke I said I have been mwssign with this for 3 days and I have tried so many different options and ways and now everyting is starting to blend together. That is why I thought a different set of eyes could benefit me.

neileg - There really isn't a "reason" for the different tables other than I thought it was always best to seperate them when possible. It's not a big database but there are probably about 130 different fields. I guess the possibility is there to create 1 big table. But I could also see this needing a few changes once I get it up and running.

I have attached the database, if anyone want's to help. If possible I would love to have someone explain to me what the problem is so that I can not make the same mistake twice.

Thanks again,

Rick

 

[neileg]

Definite design problems here.

The information you are holding for scouts and their parents are virtually identical. This means that you should have them in the same people table with a field that tells you which status they are, and fields to link scouts with their parents.

Now if the scout, mother and father all live together, you are holding the same address information three times. Not only is this a waste of space, it's a maintenace headache. So you hold address information in a separate table. Hold the ID of the address against the people record.

The existence of repeating groups like Med1, Med2, Med3, Med4 tells me that these should be individual records in another table. Now since there's a finite number of medications, you only need to hold these once and link them to your people. This is a many to many relationship so you need a junction table.

In short, your database is not normalised. You need to read up on this topic.

 

[rgreene]

Yea that address thing bothered me too but I was having a hard time trying to come up with a better idea. I just wanted to be able to track for families, single moms/dads, divorced familes, mom and grandma all those possibilities. Maybe I should scrap the whole thing and go back to the design board. I'm open to suggestions.