one to many relationship problem

NLR

Registered User.
Local time
Today, 04:05
Joined
Aug 29, 2012
Messages
71
Hello,
I'm working with Access 2010 and am trying to create a one to many relationship between two of my tables. Access will only give me a one to one relationship. The one side (tbl_DailyFiles) has a PK of DailyFileID and a FK of Pre-NoteID. The many side (tbl_PreNotes) has a PK of Pre-NoteID and a FK of DailyFileID. The FKs are formatted as Number (Long Integer) with the PKs formatted as Auto Number.
I don't see why I can't seem to get Access to give me the one to many relationship between these two tables.

Any help is appreciated! :banghead:
NLR
 

Attachments

  • Relationship window.jpg
    Relationship window.jpg
    43.4 KB · Views: 468
Before pointing out the errors of your ways, let me first say they are good errors. You've taken the time to learn about foreign keys and why they should be used. Unfortunatley, you've over done it. They are everywhere and shouldn't be.

For your issue, tbl_DailyFiles should not have a FK to tbl_Prenotes. Only the many in the relationship should have the foreign key. You've also made this mistake in your other tables. I don't know the relationships you want among tbl_Companys, tbl_AccountOwners and tbl_AccountInfo, but only the many in those relationships should have a foreign key.

If the relationship is many tbl_AccountInfo records to many tbl_AccountOwner records to many tbl_Companys then tbl_AccountInfo should have just a foreign key to tbl_AccountOwners--it shouldn't have a foreign key to tbl_Companys; tbl_AccountOwners should have just a foreign key to tbl_Companys and tbl_Companys shouldn't have a foreign key at all.


My gut is also telling me there's something wrong with tbl_DailyFiles in that DebitCount and CreditCount might be calculated fields (which you shouldn't store in a table). Also, you probably shouldn't have seperate fields for DebitAMT and CreditAMT.

Then just to be nitpicky, its bad to have special characters in your table and field names. Underscores are ok, but dashes, number signs , spaces and plusses are not the best characters to use. It just makes query and code writing a little more of a pain.
 
Before pointing out the errors of your ways, let me first say they are good errors. You've taken the time to learn about foreign keys and why they should be used. Unfortunatley, you've over done it. They are everywhere and shouldn't be.

For your issue, tbl_DailyFiles should not have a FK to tbl_Prenotes. Only the many in the relationship should have the foreign key. You've also made this mistake in your other tables. I don't know the relationships you want among tbl_Companys, tbl_AccountOwners and tbl_AccountInfo, but only the many in those relationships should have a foreign key.

If the relationship is many tbl_AccountInfo records to many tbl_AccountOwner records to many tbl_Companys then tbl_AccountInfo should have just a foreign key to tbl_AccountOwners--it shouldn't have a foreign key to tbl_Companys; tbl_AccountOwners should have just a foreign key to tbl_Companys and tbl_Companys shouldn't have a foreign key at all.


My gut is also telling me there's something wrong with tbl_DailyFiles in that DebitCount and CreditCount might be calculated fields (which you shouldn't store in a table). Also, you probably shouldn't have seperate fields for DebitAMT and CreditAMT.

Then just to be nitpicky, its bad to have special characters in your table and field names. Underscores are ok, but dashes, number signs , spaces and plusses are not the best characters to use. It just makes query and code writing a little more of a pain.
^^^^^^^^^ + 1 ^^^^^^^^^^^^^^

thumbsupsmile.jpg
 
Thank you for the info. I've updated my tables as you suggested, but no luck. tbl_DailyFiles can have more than one (many) tbl_PreNotes. But access sets this relationship up as a one to one.
Also, I want tbl_Companys, tbl_AcctOwners and tbl_AccountInfo to have a one to one relationship and have updated those tables. (Not sure if this is correct.)
Any other suggestions?

Thank you,
NLR
 

Attachments

  • Relationship window2.jpg
    Relationship window2.jpg
    81.8 KB · Views: 244
Actually something like this (A company can have many accounts, an account can have many owners) - so if that is true then

attachment.php
 

Attachments

  • 3-14-2013 10-14-29 AM.jpg
    3-14-2013 10-14-29 AM.jpg
    68.8 KB · Views: 1,475
And actually your PreNotes stuff is fine but the reason why you have a one to one relationship may be due to the indexing you have selected or something. When I create it, it is fine.

attachment.php
 

Attachments

  • 3-14-2013 10-19-37 AM.jpg
    3-14-2013 10-19-37 AM.jpg
    88.7 KB · Views: 10,322
Can you tell me how to create the one to many relationship between tbl_DailyFiles (one side) and tbl_PreNotes (many side)?
Thank you.
NLR
 
Does that mean the many side should not be indexed? Sorry, I'm a little confused.
Does the relationship between the other tables have anything to do with it?
 
Does that mean the many side should not be indexed?
Yes, it can be indexed but it should have YES (Duplicates OK) instead of YES (No Duplicates).
Does the relationship between the other tables have anything to do with it?
No, it does not.
 
Yes, I changed the FK on my tbl_PreNotes table to say Index but allow duplicates and then I was able to create the one to many.
Thank you... :o)
When you created the other relationships, did you create a new link table? (I'm not familiar with this yet.) Can you tell me how this works? Thank you!
NLR
 

Users who are viewing this thread

Back
Top Bottom