Two left joins help

[goalnightx]

Hello all,

I'm stumped. Beginner Access user. I am joining two SUM queries to build a report but MS Access either duplicates the record or omits data when joining. Can you help me find what I am doing wrong?

[code] SELECT Bkg_Sum.Expr1, Bkg_Sum.Expr2, Bkg_Sum.Expr3, Bkg_Sum.SumOfQbooked, Bkg_Sum.SumOfQconfirmd, Bkg_Sum.SumOfQrlsd, Inv_Sum.SumOfQonHand, Inv_Sum.SumOfQonHandNS, Inv_Sum.SumOfQinProgIC, Inv_Sum.SumOfQinProgOP, Inv_Sum.SumOfQinProgOther, Inv_Sum.SumOfQonHold, Inv_Sum.SumOfQavailable, Inv_Sum.SumOfQavailableNS, Inv_Sum.SumOfQavailOnSite, Inv_Sum.SumOfQavailOffSite
FROM (PRODUCTS INNER JOIN Bkg_Sum ON PRODUCTS.KeyProduct = Bkg_Sum.Expr1) INNER JOIN Inv_Sum ON PRODUCTS.KeyProduct = Inv_Sum.Expr1
WHERE (((Bkg_Sum.Expr1)=56)); [code]



Results in records duplicating

[ranman256]

Depending on the join you could get duplicates.
you could set the query to OUTER join (to get whats missing)
and
set it to UNIQUE VALUES = TRUE (to remove dupes)

[goalnightx]

Ranman,

I tried that but still duplicating the records. Any other thoughts?

[goalnightx]

Here is the query and records produced. The red lines indicate the duplicate records.

[goalnightx]

Anyone with some thoughts on this? just trying to get this solved as soon as possible. Thanks in advance. G

[aytee111]

Could you post your database, or this portion of it?

[goalnightx]

Yes. Let me slim it down to fit the forum criteria. Just a minute. Thx

[goalnightx]

archive.zip

Had to compress it. Here ya go. Thanks!

[aytee111]

You need something else to join on, KeyProduct is not enough, that is why it is showing all records from each query (2 + 2). Still looking!

[goalnightx]

There ia also a productcode as well but when I did that it didnt seem to work. BTW I appreciate you looking at this. G

[goalnightx]

The result is this when I try to join the Expr2 and Expr3 fields. It needs to show the MN with the associated data in the second image.

[aytee111]

There is an "expr3" on Bkg_sum and an "expr2" on Inv_sum - are these two the same data? If you get the original table where these come from and do left joins you would end up with three records - is that what you are expecting?

[aytee111]

(posts crossed)

[goalnightx]

Yes, correct. My hope then is is would tell me in Region MN that i have zero Qty On Hand and that I need some of the 550 in Region WI to transfer to MN as that is where the demand for the product is.

[aytee111]

Bring in the table to Pos_Qry - it wasn't in the database that you posted. Remove Products, you don't need that