query help again please! (1 Viewer)

ChrisC

Registered User.
Local time
Today, 21:51
Joined
Aug 13, 2019
Messages
50
Hi Guys,
After ArnelGP's helpful example last week I can now move forward with my project. This time...
  • I have a subform that shows warehouse locations (WHLocat) and the current amount of stock that is in that area (CurrentBalance), based on the part number (PartID) that has been entered on the form.
  • This subform is looking at the query "qryCurrentStockLevel"
  • I have another query called qryAllWHLocat and a corresponding table called WarehouseLocations - both of which simply list EVERY warehouse location I have
I need a combobox that lists all the warehouse locations that do NOT appear on the qryCurrentStockLevel query where the PartID on qryCurrentStockLevel matches the PartID entry on the form.

Do I need some form of union query in order to look at the qryAllWHLocat and qryCurrentStockLevel queries?

As always, thanks for looking and for any help that you can offer!

Kind regards
Chris
 

Ranman256

Registered User.
Local time
Today, 17:51
Joined
Apr 9, 2015
Messages
3,547
you can make a union query to see both.
but to see what's missing from the list, you want an OUTER join query.

in a query: table of stock items, and table of warehouse items, joined,
dbl-click the join line,
chose SHOW ALL records in Stock ITems, SOME in warehouse.
OK
bring down the Stock Itm from both tables into the query grid
run query
the items with NULL Stock Itm are the missing ones.

you can filter ONLY missing ones with criteria: IS NULL
 

ChrisC

Registered User.
Local time
Today, 21:51
Joined
Aug 13, 2019
Messages
50
Hi Ranman,

thanks for getting back so quickly!
My warehouse locations table/query (your warehouse items) has only got one field - WHLocatID (it is literally just a list of storage locations) so I cant bring down the Stock Itm (my PartID) from them both... sorry to be a pain!
 

Ranman256

Registered User.
Local time
Today, 17:51
Joined
Apr 9, 2015
Messages
3,547
then where is the list of: stock item IN the warehouse?

you can do the outer join with this:
tStockItms.WhID = tWarehouse.WhID
 

ChrisC

Registered User.
Local time
Today, 21:51
Joined
Aug 13, 2019
Messages
50
Hi,

the list of stock items IN the warehouse is qryCurrentStockLevel.

Forgive my ignorance, where would I put your code for the outer join?

many thanks
chris
 

Ranman256

Registered User.
Local time
Today, 17:51
Joined
Apr 9, 2015
Messages
3,547
there no code. Its a query.
in a new query, bring in tWarehouse & qryCurrentStockLevel
join: qryCurrentStockLevel.WhID =tWarehouse.WhID
then dbl-click the join, set for OUTER join.
bring in WhID field from BOTH table/qrys.
 

ChrisC

Registered User.
Local time
Today, 21:51
Joined
Aug 13, 2019
Messages
50
Thank you Ranman,

I'm clearly missing something. I have done this (thank you for the query information); but the list that appears in my combobox is either the full list of ALL warehouse locations or only those locations that have stock.

I need the combobox to show locations that do not have any entries on qryCurrentStockLevel for the PartID shown on the form.

Sorry for not understanding!

Chris
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom