How to stop duplicate records

[SBM]

I have two table. Deliveries Created (DC) and Goods Receipt (GR).

I have a query which links the two by PO number, PO Item, Unit code and Delivery quantity.

This works fine until one PO Number has more than one delivery quantity which is equal. For example.... The PO quantity is 4,000 but, because the units can only be shipped in quantities of 1000, there are four different entries in both the DC and the GR tables each with the same PO number, PO Item number, Unit code and delivery quantity (and shipped on the same date). This of course gives 16 results for the query using this PO number.

There is one unique field in the DC table this is the posting number field. I've tried grouping by this number and taking the First values from the GR table. This works OK in that it produces Four results but, two of the deliveries were received on Day 1 and two were received on Day 2. If I take First of GR date I get DAY 1 for all, If I take Max I get Day2 for all. What I need is the correct date.

I hope I have explained this OK.

And I hope you can help

 

[namliam]

All in all your Primary key is simply not Unique, therefor you cannot/shouldnot use it for linking the two tables.

If you trully have a 1-1 relationship between the two tables you should have a 'true' Primary Key (auto number for example) and link them 2 tables using them.

I have a feeling we are moreso talking about a 1-M relationship.
1 Order (4000) has 4 deliveries (1000).

Greets and Good Luck

 

[zog]

I'm not totally sure I follow exactly what you are trying to do without seeing the SQL. I suspect you could use a subquery, something along the lines of selecting a distinct value from your table with multiple keys and then matching the results to your second table.

 

[SBM]

The problem is in the DC table the only unique field (in this case) is the posting document. There is also a posting document in the GR table but they are generated from different systems and thus are different (DC posting starts 89..... and GR posting starts 56....).

Steve

 

[namliam]

If you dont have a natural true PK then you have to resort to the techinical PK. The autonumber....

Just go into your table design and add an autonumber field. Then use this to link the 2 tables preventing issues with doubling up (i.e. 4x4=16 records)

 

[SBM]

If I put Autonumber fields on both tables surely I could only link them if they had the same numbers. They will, without doubt, have different numbers so how can I use this method to block duplicates ?

 

[namliam]

You dont use both the autonumbers, you use the one autonumber to link to another field in the other table.

Using a Foreign Key - Primary Key kind off thing...