Beginner - help with design/relationships

[SadGrl]

Totally new to Access and databases in general. I am trying to set up a very simple database but when I'm trying to create relationships between my tables, they automatically set one-to-many in the wrong direction (for instance, I have an account names table that I want to link to a contacts table. For every account, there can be multiple contacts (but only one account per contact), but access keeps trying to put 'one' on the contacts side and 'many' on the account name side). I'm having similar problems with other tables, so I know it's something I'm not understanding right.

Here's part of my setup:

Contacts Table: ContactID (PK, autonumber), ContactFirstName, ContactLastName, various address/phone number fields. Not currently linked to anything else. Trying to link ContactID to foreign ContactID in Accounts table.

Accounts Table: AccountID (PK, autonumber), AccountName, AccountNotes, ContactID (foreign key-number, not autonumber). Currently linked to a junction table (one to many) which contains AccountID, StatementID,InvoiceID (shared PKs)

Hope that's enough info. Thx

 

[stopher]

The foreign key must be in the "many" table. So your structure should look like this:

Contacts Table: ContactID (PK, autonumber), AccountID (FK), ContactFirstName, ContactLastName, various address/phone number fields.

Accounts Table: AccountID (PK, autonumber), AccountName, AccountNotes

Think of it like this. If you used your original design and then went about setting up an account (just one record), what value would you put in ContactID given that you know you have many contacts.

hth
Chris

 

[SadGrl]

If you used your original design and then went about setting up an account (just one record), what value would you put in ContactID given that you know you have many contacts.

Thanks for the response.

I assumed I could use ContactID in the Account table and just add multiple ContactIDs as needed, but that wouldn't that be allowed, right? (it would cause duplication of the primary key, unless I made them shared PKs). Alternately, I could add Contact1 and Contact1 fields to the Accounts table--but would both these have to be linked to the ContactID in Contacts table?

Or should I just to make another junction box, that contains AccountID and ContactID (shared PK)--would that solve the issue? (it lets me create the relationship anyway, not sure how well it works though)

 

[stopher]

I assumed I could use ContactID in the Account table and just add multiple ContactIDs as needed, but that wouldn't that be allowed, right? (it would cause duplication of the primary key, unless I made them shared PKs). Alternately, I could add Contact1 and Contact1 fields to the Accounts table--but would both these have to be linked to the ContactID in Contacts table?

You are right in your summation. Both these methods are wrong.

Or should I just to make another junction box, that contains AccountID and ContactID (shared PK)--would that solve the issue? (it lets me create the relationship anyway, not sure how well it works though)

Nope. Just use the solution I gave you. That's how you implement many contacts to one account.

Chris

 

[SadGrl]

Ok, I think my brain is finally 'getting it'. The relationship definitely seems to work. Thanks!

 

[SadGrl]

Actually, now I have another question, since I'll have to know eventually. If I want to create a many-to-many relationship, is that when i can use the junction table? For instance, multiple payment statements can have multiple invoices associated with them and vice-versus. So would I create a statement table, an invoice table, and then a junction table containing their shared PKs? And then just link the two tables to the junction table? Or is there a better way to do this? Or am I completely misunderstanding the function of a junction table to begin with?

Thanks, you've been a real help

 

[The_Doc_Man]

If I want to create a many-to-many relationship, is that when i can use the junction table?

That is exactly when you would use this. I do not believe that you misunderstood the purpose of a junction table. Your example is relevant and (the way I see it) can only be done via junction table. So consider this a "thumbs up" for your correct understanding of the need and applicability of said junction table.

 

[SadGrl]

Woohoo! Thanks