Query need your HELP

  • Thread starter Thread starter william poh ben
  • Start date Start date
W

william poh ben

Hi the experts,

I face this problem in ACCESS2000 for more than a year at work which I
am not able to solve. I greatly appreciate anyone who can offer help
here.

For example, If my Lookup table had found Quantity to be zero for
PartNumber 11-221 in the following Reference table, then I want the
search to continue to look for same PartNumber that has prefix "W"
behind (ie. 11-221W) for its Quantity which is 8.

Because I have not been able to do this, I have to keep a separate
table with all the PartNumbers that have prefix "W" behind together
with the associated available qty (I call this W-parts). For those
PartNumbers that have qty zero, I then do another look up to this
separate W-parts table.

How can I use query or update query to have the field "Material" look
up for the W-parts Qty when the same PartNumber without the prefix "W"
has Qty zero ?
Eg. when 11-221 has qty zero, then lookup to 11-221W which has qty=8.

Thanks in advance for your help.


PartNumber Quantity
11-220 3
11-221 0
11-221W 8
11-223 4
11-223W 7


Material Desired findings
11-221 8
11-221W 8
11-223 4
11-223W 7
 
Hi,

i had created 2 query that hopefully will solve your problem. Just create a query, copy/paste this SQL string in the SQL View of Query Design window and change the table-, fields name with your own.

The 1st query SQL string is: SELECT A.PN, B.Q AS Q_W FROM tbl_PN_Qty AS B, tbl_PN_Qty AS A WHERE (((B.PN)=[a].[PN] & "W") AND ((A.Q)=0));

This query will return:
PN Q_W
11-221 8

Save the 1st query as Query1

The 2st query SQL string is: SELECT tbl_PN_Qty.PN, IIf([q]=0,[q_w],[q]) AS Qx FROM Query1 RIGHT JOIN tbl_PN_Qty ON Query1.PN = tbl_PN_Qty.PN;

This query will return:
PN Q_W
11-220 3
11-221 8
11-221W 8
11-223 4
11-223W 7
 
Back
Top