To get single value in LEFT JOIN query result

[edihrtn]

Two tables,
1. Table Trans
RecNumber,TransID, ConsID, ConsName, ProductCount, ProductQty
1 6 7 Mike 2 88

2. Table DetailTrans
RecNumber, TransID, ProductID, Qty, TransDate
1 6 136 44 4/29/2013
2 6 188 44 4/29/2013

I want to join the tables, that will give result like this:
RecNumber,TransID, ConsumerID, ConsName, ProductCount, ProductQty, TransDATE
1 6 7 Mike 2 88 4/29/2013

I try :

SELECT Trans.TransID,Trans.ConsumerID,Trans.Name,Trans.ProductCount, Trans.ProductQty,DetailTrans.Date
FROM Trans LEFT JOIN DetailTrans ON Trans.TransID=DetailTrans.TransID

But the query give double value
TransID, ConsumerID, ConsName, ProductCount, ProductQty, TransDATE
6 7 Mike 2 88 4/29/2013
6 7 Mike 2 88 4/29/2013

I Spent 3++hours, try many LEFT JOIN syntax but still cant find the right syntax to get single value result.
Pls advise with this simple query, Thanks.

 

[dhookom]

Try using a group by

SELECT Trans.TransID, Trans.ConsumerID, Trans.Name,Trans.ProductCount, Trans.ProductQty,DetailTrans.Date
FROM Trans LEFT JOIN DetailTrans ON Trans.TransID=DetailTrans.TransID
GROUP BY Trans.TransID, Trans.ConsumerID, Trans.Name,Trans.ProductCount, Trans.ProductQty,DetailTrans.Date

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Why not simply SELECCT DISTINCT ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[edihrtn]

Its Worked. Thanks All.