Duplicate data in one field 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.

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.

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

Thanks!

Toni

 

[TheAceMan1]

To All:

For better viewing:

[tt][blue]ModelNo SONo SO_Line_No OrderQty LateQty ProcessDt
******* **** ********** ******** ******* *********
ABC 123 10 350 25 4/15/2011
ABC 123 10 50 0 4/20/2011[/blue][/tt]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

To All . . .

For better viewing:

[tt][blue]SELECT qUCP.[Sld To Cust Id_BOP] AS [Sld To Cust Id],
qUCP.[SONbr] AS [Sls Doc Hdr Nbr],
qUCP.[SO_Ln_No] AS [SO Ln Itm Nbr],
qUCP.[Cust PO Nbr_BOP] AS [Cust PO Nbr],
qUCP.CRD_BOP AS CRD,
qUCP.[ModelNo] ,
qUCP.[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 AS [purple]qUCP[/purple]

ON ([Contract Coverage BIP TEXT].[ModelNo] = qUCP.[ModelNo]) AND
([Contract Coverage BIP TEXT].[SONo] = qUCP.[SONo])

ORDER BY qUCP.[SONo];[/blue][/tt]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

thart21 . . .

And SQL for [blue]qUnion_Current_Prior[/blue]?

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]