2 tables related by primary key have different records for the rec

I have two tables: tblOrders and tblOrder Detail. The primary key is Order
ID. The record for Order ID #4983 on the tblOrders table is different from
Order ID #4983 on tblOrder Details........why????

I have referential integrity and all the boxes checked but the basic reason
you have a primary key is so that this doesn't happen right?? So what do I
do now? I have no way to control the integrity of the data.

Are you saying that both tables have a same field (OrderID) as primary key?
If your situation requires a single order detail record per each order, that
would make sense. If you require one/more order detail rows per order row,
having these two tables related by their primary keys does not.

A typical design would have the primary key of the order table (OrderID) be
related, one-to-many, to a foreign key field in the order detail table.
Note that this means the order detail table needs its own, unique primary
key.

Can you describe your situation a bit more?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"TechyTemp" wrote in message
...
I have two tables: tblOrders and tblOrder Detail. The primary key is Order
ID. The record for Order ID #4983 on the tblOrders table is different
from
Order ID #4983 on tblOrder Details........why????

I have referential integrity and all the boxes checked but the basic
reason
you have a primary key is so that this doesn't happen right?? So what do
I
do now? I have no way to control the integrity of the data.

On Tue, 16 Jan 2007 11:55:01 -0800, TechyTemp
wrote:

I have two tables: tblOrders and tblOrder Detail. The primary key is Order
ID. The record for Order ID #4983 on the tblOrders table is different from
Order ID #4983 on tblOrder Details........why????

I have referential integrity and all the boxes checked but the basic reason
you have a primary key is so that this doesn't happen right?? So what do I
do now? I have no way to control the integrity of the data.

As Jeff says, this is quite odd: normally the Order Details table
would have MANY records for an order, and by making the primary key
the OrderID you're insisting that there be only one.

And are you assuming that you can have other fields duplicated between
the two tables, storing them redundantly, and somehow the relationship
will keep them up to date? That's NOT how relationships work if so!
The *only* field that tblOrders and tblOrderDetail (I'd suggest
getting rid of the blank in the name) should have in common is the
OrderID; that field should be the Primary Key of Orders and a foreign
key field (NOT primary) in OrderDetails.

John W. Vinson[MVP]