Table Relationship Problem - Same Data appearing twice 2

[kirstenlargent]

I am trying to link three tables, and I am having trouble with records duplicating in a query.
Here are my three tables: (all are linked by Record Series Number)
Record Series Number - No duplicates allowed
Box Data - Each box is assigned a record series, multiple boxes can have the same record series number assigned
Hold Order - Each Record Series Number can have multiple hold orders attached

My problem occurs when I link all tables by Record Series Number. I only have ONE box entered in the Box Data table, but the Record Series Number on the box has TWO Hold Orders attached (2 hold orders entered in the Hold Order Table).

Therefore, in my query, it lists the SAME BOX twice, once for each Hold Order. But there is only ONE box, I don't want it listed twice!! As soon as I unlink the Hold Order table, the problem is solved, and the box is listed only once, so I know the problem is with my relationships, but I can't figure out how to fix.

Please Help!!

THANKS!!

 

[930driver]

From Access' point of view there is no problem. You have included the Hold Order table. It has two records for the given Box so of course it will give you two records.

Either you want the Hold Order data or you don't. If you don't want two records you should reconsider whether you really want the Hold Order data in your query. OR, you could limit the query to return a particular Hold Order.

As an example, if you only want one Hold Order to be displayed how can Access know _which_ one (of the two possibilities) to display? You could include criteria that would limit the query to a particular Hold Order, then Access would know what to do.

"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[JimHorton]

kirsten,
You should also look in Help for the Group BY clause (use that greek 'E' icon in the iconbar at the top of screen) and do the query in this way. Used correctly, this will return only the single box records if you like--you'd use Group By for all the BoxData fields and then for Hold Order fields you couldn't view individual records (for the reasons pointed out by 930driver), but you could use a Count aggregation of the Hold orders, or First, or some other aggregate function.
--Jim