Two tables won't connect to the main table

[KaylaSimmonds]

Okay, I know this may sound basic, but I cannot figure this out for the life of me. I have created a database to track information within our repossession records and it consists of 6 tables: General Info, Residency, Employment, Finances, Transaction, and Payment Schedule. Two of my tables ('Employment' and 'Payment Schedule') refuse to grab the fk_RepoRecordID from my main table 'General Info' with the primary key being called RepoRecordID. The other 4 work fine; they bring over the 'General Info' RepoRecordID and are set to cascade so that deleted entries are referenced across. The other two, though, will not grab the RepoRecordID and they will not cascade, even though the source controls are all the same! I have checked every table and fk_RepoRecordID is the exact same, so I am baffled by why I can't get this to work.

I'm not sure if this means anything, but currently, each table has it's own primary table ID (Example: 'Finances' has a FinancesID autonumber field) and then the fk_RepoRecordID field to connect it to the proper Repo Record 'General Info'. Is that the best way to go about this? It works for the others!

 

[boblarson]

Can you upload a copy of the database with fake data so we can see? I think your table structure and/or relationships are probably not quite right.

If you are going to upload you will need to run COMPACT AND REPAIR first and then ZIP the file by right clicking on it and selecting SEND TO > COMPRESSED FOLDER and then see this for how to do the actual upload (and you should be able to do this even though you don't have 10 posts as long as it is a zip file).

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[KaylaSimmonds]

There is no true data input yet. As I'm playing with all of the settings, I just keep putting a random entry into the form for each of the first fields for every table and then close it and see the connections.

Then I delete the main record from the RepoRecord table and see which other tables cascade the delete also.

 

[boblarson]

A couple of things.

1. You failed to include the fk_Repo_RecordID field in the record source for your payment subform.

2. As for the subform which has employment on it, you have tried to save either time or effort and included the residency table in with the same subform. You can't do that. For residency you need to create its own subform. You can still have it on the same tab as the employment stuff but you can't really have those tables together in a single record source.

3. You may be aware of it and you may not be, but your data structure has repeating fields which is not good for a relational database. You should not have P1, P2, P3, P4, etc. and the others like that. You should be adding records for each payment not having fields. So, you would need at least one other table and perhaps two.

 

[KaylaSimmonds]

Okay, I understand #1 and #2. Will you elaborate more on the third issue for me please?

 

[jdraw]

As Bob said you have repeating fields. See this article on Normalization.

Point #1, in the article, No repeating elements or groups of elements is specific to Bob's #3.
http://www.phlonx.com/resources/nf3/