Relationships from One-to-Many tables

[Guest_imported]

Hello,

I'm having an issue that none of the docs I've seen cover. My database consists of 5 tables, all with a primary key defined (ContactID). These keys are all of the same number type. I have a Contact table, which contains info like name, address, etc., and the other four tables are misc. info about these people.

The documentation makes it seem extremely easy to create one-to-many relationships; i.e. link this table to all of the others. When I try to do it, though, Access grays out the 'One-to-Many' label and replaces it with a one-to-one label, so I can't make the relationships I need to. The direction of the linking is all correct and I've selected the proper key fields in each table. Any ideas or suggestions?

Thanks,

Matt

 

[sko]

Sounds like you made the ContactID the *primary* key in each of your tables. Could you give us a description of the tables and fields? Some of the misc. information may belong in your Contact table.

 

[Guest_imported]

I did make the ContactID the primary key in each of the fields. Should it be different from that?

Originally there were only 4 tables. What I did was cut out the information in each field that was duplicated over all four tables and put it into the Contact table.

 

[sko]

The ContactID should be the primary key in the Contact Table only. To link to the other tables, ContactID must be included in those tables, but not as the primary key. Since I don't know what information you are storing in the other tables I'll use customers and their orders as an example.

tblCustomers
CustomerID (Primary Key)
LName
FName
Address

tblOrders
OrderNumber (Primary Key)
CustomerID
ShipTo
OrderDate

Link tblCustomers.CustomerID to tblOrders.CustomerID

This is a one to many relationship. One customer can have many orders.

The Primary key uniquely identifies the record, so in the Customer Table there can be only 1 customer with ID # 1234

The Order table uses OrderNumber as it's primary key. Each order is unique and can be identified by it's OrderNumber.
Each order has a Customer ID field. Linking this to the Customer Table allows us to match CustomerID and fill in the ShipTo field with the Customer Address.

BTW if the primary key in tblCustomers is Autonumber then the CustomerID field in the Orders Table *must be* formatted as Long Integer.

 

[Guest_imported]

Thanks! This worked out for me, I appreciate your time.