Query need help - urgent

  • Thread starter william poh ben
  • Start date
W

william poh ben

Hi,

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
 
M

Michel Walsh

Hi,



SELECT Nz(iif(a.qty=0, b.qty, a.qty),0) As TheQty
FROM tableName As a LEFT JOIN tableName As b
ON a.PartNumber & "W" = b.PartNumber


If we take a look moment before the Nz(iif... ) is executed, we should see:

a.PartNumber a.qty b.PartNumber b.qty
11-220 3 null null
11-221 0 11-221W 8
11-221W 8 null null
11-223 4 11-223W 7
11-223W 7 null null

the iif condition about a.qty=0 is applied just once, so, the iif supplied:

a.PartNumber a.qty b.PartNumber b.qty iif(...)
11-220 3 null null 3
11-221 0 11-221W 8 8
11-221W 8 null null 8
11-223 4 11-223W 7 4
11-223W 7 null null 7


The Nz just take care of a possible case where a.qty=0 and there is no "W"
part matching its PartNumber.


Hoping it may help,
Vanderghast, Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top