Force SubQuery to Run First without Making a Separate Query (Ambiguous Outer Join)

Hello Experts!

I have the following access query:
 
SELECT DISTINCT t1.Inventory, t1.TTechID, [StockStatus]-[Inventory] AS Difference, tmpTeleDProductss.StockStatus AS NewInventory
FROM tmpTeleDProductss INNER JOIN (tblProductInventory AS t1 INNER JOIN [SELECT a.TTechID as ID, Max(a.LastUpdate) as Max_LastUpdate FROM tblProductInventory a GROUP BY a.TTechID]. AS t2 ON (t1.TTechID = t2.ID) AND (t1.LastUpdate = t2.Max_LastUpdate)) ON tmpTeleDProductss.TTechID = t1.TTechID
WHERE (((t1.VendorID)=1));

Open in new window


I need the above query to operate with a Left Join between tmpTeleDProductss and t1, essentially:
 
SELECT DISTINCT t1.Inventory, t1.TTechID, [StockStatus]-[Inventory] AS Difference, tmpTeleDProductss.StockStatus AS NewInventory
FROM tmpTeleDProductss LEFT JOIN (tblProductInventory AS t1 INNER JOIN [SELECT a.TTechID as ID, Max(a.LastUpdate) as Max_LastUpdate FROM tblProductInventory a GROUP BY a.TTechID]. AS t2 ON (t1.TTechID = t2.ID) AND (t1.LastUpdate = t2.Max_LastUpdate)) ON tmpTeleDProductss.TTechID = t1.TTechID
WHERE (((t1.VendorID)=1));

Open in new window


Because I want all of the records from tmpTeleDProductss and only those that match in t1. However, when I do this I get an error from access that this creates an ambiguous outer join and that I need to create a query, save it, and then nest that query in this query so that it is performed first.

That's fine and dandy in a normal situation, but these queries are being created on the fly in VBA based on certain input information, so I could not readily or easily perform access's suggestion unless I made querydef's etc.
I believe it would be far less complicated just to right one query string and use that instead of the querydef method.

The t1 and t2 tables are essentially hunting through the Inventory table to find what the most recent inventory level was for that ID. The reason I need a Left Join is because new products may be in tmpTeleD that have never had inventory entered for them and these need to be in the resulting table along with those that already have had inventory entered.

Any suggestions on how best to go about this would be excellent!
w00tw00t111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

w00tw00t111Author Commented:
Phew!
Finally, I got it!

May not be the most elegant solution, but it is quick and it works!

Essentially, I'm still using the query above (the inner join) and this is only updating the products that need updating. Then I run another query:
 
SELECT Source.[StockStatus] As Inventory, Source.TTechID FROM tmpTeleDProductss As Source LEFT JOIN [SELECT a.TTechID, a.Inventory FROM tblProductInventory AS a WHERE a.VendorID = 1]. AS Q ON Source.TTechID = Q.TTechID WHERE q.TTechID Is Null And (Source.TTechID <> Null)

Open in new window

That add's new inventory values for the products that are missing from the above query.

So, the logic being used is:
1) Update existing inventory values
2) Run a second append query for new values

I hope this helps someone else with this issue!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
w00tw00t111Author Commented:
I should mention, the query in comment #2 gets ONLY the rows that are NOT in the Inventory table for a particular vendor.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.