[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

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!
0
w00tw00t111
Asked:
w00tw00t111
  • 2
1 Solution
 
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
 
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now