One to One Relationships

N

**Nicole**

I'm trying to create a one to one relationship between two tables. One is
called Invoices and the other is Orders. There should be one invoice for
every order that is placed. However when I try to create a relationship the
program only let's me create a one-to-many relationship. How can I fix this?
 
M

Mike Labosh

I'm trying to create a one to one relationship between two tables. One is
called Invoices and the other is Orders. There should be one invoice for
every order that is placed. However when I try to create a relationship
the
program only let's me create a one-to-many relationship. How can I fix
this?
Click to expand...

Each table must have a column with a unique primary key index. Then you
make the relationship on those two columns, for example:

Table Orders:
OrderID, Number, Long Integer, Indexed No Duplicates, Primary Key

Table Invoices
InvoiceID, Number, Long Integer, Indexed No Duplicates, Primary Key

Then you make the 1:1 relationship between Orders.OrderID and
Invoices.InvoiceID

You should also consider using the same column name in each table.
--
Peace & happy computing,

Mike Labosh, MCSD

"It's 4:30 am. Do you know where your stack pointer is?"
 
A

Arvin Meyer

Create a few temporary records using the same ID number as a Primary Key on
both tables. Access will see that both Primary Keys contain the same values
and automatically set up the 1 to 1 relationship when you make the
connection.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

**Nicole** said:
I'm trying to create a one to one relationship between two tables. One is
called Invoices and the other is Orders. There should be one invoice for
every order that is placed. However when I try to create a relationship the
program only let's me create a one-to-many relationship. How can I fix
Click to expand...
this?
 
J

John Vinson

I'm trying to create a one to one relationship between two tables. One is
called Invoices and the other is Orders. There should be one invoice for
every order that is placed. However when I try to create a relationship the
program only let's me create a one-to-many relationship. How can I fix this?
Click to expand...

Short answer: create a unique Index, such as a Primary Key, on both
tables' linking fields.

Long answer: One to one relationships are VERY rare. If each Invoice
refers to one and only one order, and each Order has one and only one
invoice, why have two tables at all? Just use a single OrderInvoice
table and include all the fields from both tables.

Typically, however, one table or the other will (at least sometimes)
be in a "many" relationship. If I order a widget, a wadget, and a
Boff, I'd really prefer to get one invoice with three items on it
rather than three separate invoices. Clearly your business rules will
be the controlling factor here though!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
J

JoanOC

Mike: I hope you see this for I have been trying unsuccessfully for ages to
create a Accts Receivable and Cash Receipts report based on 2 tables. I
thought the fields had to have the same names. Your information has opened up
a whole new Access world.
I setup my primary keys as you suggested(I wanted a one to many) and now I
am able to see the name of the clients on my reports. Thank you thank you!!!
Joan
 
Top