Solved query help again please! (1 Viewer)

ChrisC

Registered User.
Local time
Today, 11:02
Joined
Aug 13, 2019
Messages
90
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

Well-known member
Local time
Today, 07:02
Joined
Apr 9, 2015
Messages
4,337
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, 11:02
Joined
Aug 13, 2019
Messages
90
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

Well-known member
Local time
Today, 07:02
Joined
Apr 9, 2015
Messages
4,337
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, 11:02
Joined
Aug 13, 2019
Messages
90
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

Well-known member
Local time
Today, 07:02
Joined
Apr 9, 2015
Messages
4,337
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, 11:02
Joined
Aug 13, 2019
Messages
90
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
 

ChrisC

Registered User.
Local time
Today, 11:02
Joined
Aug 13, 2019
Messages
90
Really sorry to be a pest guys, is anyone able to give me further guidance on this one please?

many thanks
Chris
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:02
Joined
Sep 21, 2011
Messages
14,038
Well I am not that up on the required query joins?

I'd probably do it by a sub query looking at qryCurrentStockLevel

Speficially
Code:
ID NOT IN (Select ID from  qryCurrentStockLevel )

I am assuming qryCurrentStockLevel is for relevant form ID? else you will need to add that to the sub query Select clause.
 

cheekybuddha

AWF VIP
Local time
Today, 11:02
Joined
Jul 21, 2014
Messages
2,237
Hi Chris,

Please post the SQL of qryCurrentStockLevel and qryAllWHLocat
 

ChrisC

Registered User.
Local time
Today, 11:02
Joined
Aug 13, 2019
Messages
90
Hi all, my apologies for only just replying - happy to say that my Wife has just had our first baby, so have had other things on my mind lately :)

However; here is the SQL..

qryCurrentStockLeveL:

SELECT Inventory.WHLocatID, Inventory.PartID, Inventory.OpeningQty, qryTransLogTotal.TotalReceipt, qryTransLogTotal.TotalIssuance, [openingqty]+[totalReceipt]-[TotalIssuance] AS CurrentBalance
FROM Inventory LEFT JOIN qryTransLogTotal ON (Inventory.WHLocatID = qryTransLogTotal.Origin) AND (Inventory.PartID = qryTransLogTotal.PartID);

qryAllWHLocat:
SELECT WarehouseLocations.[WHLocatID]
FROM WarehouseLocations;

Also as qryTransLogTotal is mentioned, its SQL is:
SELECT TransactionLog.PartID, TransactionLog.Origin, Sum(IIf([TransactionQty]>0,[TransactionQty],0)) AS TotalReceipt, Sum(Nz(IIf([TransactionQty]<0,Abs([TransactionQty]),0),0)) AS TotalIssuance
FROM TransactionLog
GROUP BY TransactionLog.PartID, TransactionLog.Origin;

Thank you so much!!

Chris
(now... back to nappies/diapers!)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:02
Joined
May 7, 2009
Messages
19,169
Congratz for the baby! (don't name it after the Virus!)
 

cheekybuddha

AWF VIP
Local time
Today, 11:02
Joined
Jul 21, 2014
Messages
2,237
Many congratulations, Chris! Hope you're all doing well. I have a special admiration for new parents in this time of lockdown. Your wife and you will have to work out everything by yourselves!

Re. your combobox, you can try setting its RowSource to:
SQL:
SELECT
  l.WHLocatID
FROM WarehouseLocations l 
LEFT JOIN (
  SELECT 
    PartID,
    Origin  
  FROM TransactionLog
  WHERE PartID = [Form]![PartID]
  GROUP BY 
    PartID,
    Origin  
) t 
       ON l.WHLocatID = t.Origin
WHERE t.Origin IS NULL;

hth,

d
 

ChrisC

Registered User.
Local time
Today, 11:02
Joined
Aug 13, 2019
Messages
90
Thank you guys :)

Definitely will not be calling him "Corona"!! :) Its an odd feeling not being able to show him properly to his grandparents etc. but we will all come through this in the end and beat the virus!


Thank you for that CheekyBuddha, I think that has done the trick perfectly!!

Chris
 

Users who are viewing this thread

Top Bottom