Same Primary Key in Different Tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SactoGal - 13 Jan 2008 07:47 GMT

I'm new to Access and using Access 2003. I'm setting up my tables and have 2
questions. 1) I have a table for customer names. I set up Customer ID with
Autonumber as the primary key. I want to use Customer ID in another table
again as the primary key. Do I set it with Autonumber again or set it to
Numerial? 2) If I use an input form to create a new customer, how does
Customer ID actually get created? Thanks in advance for your help.

Reply to this Message

Allen Browne - 13 Jan 2008 08:37 GMT

To use the same CustomerID as primary key in another table, use a field of
type Number.

When you create a relation between your 2 tables (in the Relationships
window), this will give you a one-to-one relation, since the field is unique
in both tables.

You do not automatically get a record inserted into the related table when
you add a new customer to your main table. You can program it in the
AfterInsert event procedure of the form where you enter customers if you
have a reason to do so.

Before you get that far, it might be worth considering whether you are
actually creating a design that is correctly normalized. There are some
cases where what you are doing could be right, but if you are only doing
this because Access won't let you have more than 255 fields in your Customer
table, the design is not right.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I'm new to Access and using Access 2003. I'm setting up my tables and
> have 2
[quoted text clipped - 4 lines]
> Numerial? 2) If I use an input form to create a new customer, how does
> Customer ID actually get created? Thanks in advance for your help.

Reply to this Message

msnews.microsoft.com - 13 Jan 2008 08:43 GMT

Lazy. moronic, rude and inconsiderate top-posting fixed.

>> I'm new to Access

> it might be worth considering whether you are actually creating a design
> that is correctly normalized.

LMAO

Reply to this Message