Two left joins help

[goalnightx]

The two tables were summarized by the Bkg_Sum and the Inv_Sum to create a total of all inventory by "region" (NE, WI, MN). What table would I bring into the Pos_Qry?

[aytee111]

The one that contains all of the - is it states? Expr2/3, LocSub0/RegionCode. I take it these are the same data?

[aytee111]

Didn't read your whole post, sorry! Bring in a table that contains all states, replace Products with this table in Pos_Qry and left join from it to both of these queries.

[goalnightx]

Correct: LocSub0 is a state abbrev field in the Inv_Sum qry and the RegionCode is state abbrev for the Bkg_Sum qry. When I join on these (along with KeyProduct) it just gives me the NE (state) data only

[aytee111]

Still working on it!

[goalnightx]

Ok, so I brought in a state table and joined it this way. Same result. Just gives me the "NE" state

[aytee111]

Got it working:
- made a new table of states, added MN,NE,WI
- made a new query containing all products/states (SELECT PRODUCTS.KeyProduct, Table1.state FROM PRODUCTS, Table1
- New Pos_qry:
SELECT DISTINCT Query2.KeyProduct, Query2.state, Inv_Sum.Expr1, Bkg_Sum.Expr1, Inv_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, Inv_Sum.ProductCode AS Expr5, Inv_Sum.ProductCode AS Expr6, Inv_Sum.Expr1, Inv_Sum.Expr1
FROM (Query2 LEFT JOIN Bkg_Sum ON (Query2.state = Bkg_Sum.Expr3) AND (Query2.KeyProduct = Bkg_Sum.Expr1)) LEFT JOIN Inv_Sum ON (Query2.state = Inv_Sum.Expr2) AND (Query2.KeyProduct = Inv_Sum.Expr1);

[aytee111]

Hate those automated emoticons!

[goalnightx]

any chance you could compress that and send it to me? I think I got it but I'm pretty visual..lol

[aytee111]

Sorry, no, can't do it from work. Can you copy and paste the SQL that I gave you?

[goalnightx]

I'm trying it now.

[goalnightx]

Ok, so I got the state table built and copied the SQL into Query2. It works but it is only selecting the KeyProduct = 56. (which is the one product key that I put in the zipped version of the database)

How do I expand to select ALL KeyProducts and pickup all the records?

[aytee111]

That's not possible! It reads all of the products on the table, look at the SQL, it does not restrict. The 56 is in your other query, not in query2.