Duplicating data in query

[thart21]

Does anyone know what the cause of this may be?



I am joining 2 tables with the following fields and data:
ModelNo SONo SO_Line_No OrderQty LateQty ProcessDt
ABC 123 10 350 25 4/15/2011
ABC 123 10 50 0 4/20/2011

I am pulling all but LateQty from Table1 and LateQty from Table2. I join my 2 tables on ModelNo & SONo (both text datatypes)

My results, regardless of the fact that I have 2 different ProcessDt(s) are entering 25 under LateQty for both records and I cannot figure out why.
I've tried to aggregate it with Sum but then it doubles both lines to 50.

Also tried to remove ProcessDt but with the same problem.
Any ideas? Driving me crazy!

Thanks!

Toni

[ajetrumpet]

what's the sql?

[thart21]

SELECT qUnion_Current_Prior.[Sld To Cust Id_BOP] AS [Sld To Cust Id], qUnion_Current_Prior.[SONbr] AS [Sls Doc Hdr Nbr], qUnion_Current_Prior.[SO_Ln_No] AS [SO Ln Itm Nbr], qUnion_Current_Prior.[Cust PO Nbr_BOP] AS [Cust PO Nbr], qUnion_Current_Prior.CRD_BOP AS CRD, qUnion_Current_Prior.[ModelNo] , qUnion_Current_Prior.[SumOfOrd Entr Qty_BOP] AS [Ord Entr Qty], [Contract Coverage BIP TEXT].[Late Qty_BIP] AS [Late Qty]
FROM [Contract Coverage BIP TEXT] INNER JOIN qUnion_Current_Prior ON ([Contract Coverage BIP TEXT].[ModelNo] = qUnion_Current_Prior.[ModelNo]) AND ([Contract Coverage BIP TEXT].[SONo] = qUnion_Current_Prior.[SONo])
ORDER BY qUnion_Current_Prior.[SONo];

More notes: For one particular ModelNo, prior to my running this query, my main table, qUnion_Current_Prior shows there are 3 records. All I am trying to do by bringing in the 2nd table is to match it on SONo and ModelNo, and pull the LateQty & a couple more fields in. but it is creating 3 more records in this query so it is giving me 6 total. I've tried to do right joins but it gives me the same result. Thanks for you time and let me know if you need more info.

Toni

[kennejd]

This is just a guess....but does the qUnion_Current_Prior table (or is it a query?) has multiple line items per SoNo? You may need to aggregate(create a separate group query without the items) with your qUnion table before joining to the Contract Coverage table.