×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Relationships from One-to-Many tables

Relationships from One-to-Many tables

Relationships from One-to-Many tables

(OP)
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

RE: Relationships from One-to-Many tables

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.   

RE: Relationships from One-to-Many tables

(OP)
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.

RE: Relationships from One-to-Many tables

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.

RE: Relationships from One-to-Many tables

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close