Unmatched Query? (1 Viewer)

ChrisC

Registered User.
Local time
Today, 21:03
Joined
Aug 13, 2019
Messages
90
Hi everyone,

Still making inroads on my DB and have now come against my next problem.

I have 2 tables and 2 queries in this quesiton:

  • tblWarehouseLocations - this is the master list of all storage locations in the warehouse
  • tblInventory - this is a list of all items that have been put into our warehouse, the storage location and the current quantity
  • qryZeroStock - this query displays all storage locations for a given PartId on the form that have zero as a stock level (this represents "previous" storage locations for that part
  • qryInventory - this displays all locations for the given PartID that have a stock value of more than zero in the inventory table
in my form, I want to show three sub forms: one that displays qryInventory, one that shows qryZeroStock and one that shows locations from tblWarehouseLocations that do NOT appear in either of the other two.


I think this would be an unmatched query, but I can't work out how to do it. The resulting query that I get just shows either the warehouse locations that don't appear in qryInventory OR locations not in qryZeroStock... but not both.

Is it possible to compare 2 queries against a table and display the unmatched items?

thanks as always,
Chris
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:03
Joined
Feb 19, 2013
Messages
16,553
left join your locations table to the other two tables then set is null to the PK that appears in both those tables
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:03
Joined
May 7, 2009
Messages
19,169
you may also try to create a Union Query for the two queries, then select the location Not on the Union query:

select location from tblWarehouseLocations
where location not in
(select location from qryZeroStock
union
select location from qryInventory);
 

ChrisC

Registered User.
Local time
Today, 21:03
Joined
Aug 13, 2019
Messages
90
left join your locations table to the other two tables then set is null to the PK that appears in both those tables

Hi CJ, thanks for getting back to me.

Forgive my continued ignorance - but im not sure what "Left Join" means. Also, when you say "the other two tables", I only have one other in this case... do I use the two queries instead?

Thank you
Chris
 

ChrisC

Registered User.
Local time
Today, 21:03
Joined
Aug 13, 2019
Messages
90
you may also try to create a Union Query for the two queries, then select the location Not on the Union query:

select location from tblWarehouseLocations
where location not in
(select location from qryZeroStock
union
select location from qryInventory);

Hi ArnelGP

Thanks I will look into that as well as the join. In laymans terms do you mean to basically make a query that joins my other two queries together; THEN do an unmatched query that compares this new query to the tblWarehouseLocations?

Chris
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:03
Joined
Feb 19, 2013
Messages
16,553
sorry I meant your two queries

left joins are used in unmatched queries

double click on the join and select the option 'include all records from your table....'

Recommend you learn how sql is constructed. the query builder is just a GUI for creating sql. Once you have your query created, go to the sql view to see what it looks like
 

ChrisC

Registered User.
Local time
Today, 21:03
Joined
Aug 13, 2019
Messages
90
right - I have successfully joined two queries (thanks to all for the advice :))

CJ - thanks again; I will sit down and properly have a look at SQL soon. hopefully with understanding that, everything will become clear :)

I will keep exploring and trialling and will give an update soon.

thanks
Chris
 

Users who are viewing this thread

Top Bottom