Update query based on 2 criteria with the same join

[anthgav]

Hi

I am trying to create an update query, the query is called qryUpdateZeroLocations here is what I have so far...

UPDATE tblBinType RIGHT JOIN (tblBin LEFT JOIN (tblAllocatedBin LEFT JOIN tblProduct
ON tblAllocatedBin.allocated_bin_id = tblProduct.allocated_bin_1)
ON tblBin.bin_id = tblAllocatedBin.allocated_bin) ON tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type
SET tblBin.bin_width_mm = 0
WHERE (((tblProduct.allocated_bin_1) Is Null) AND ((tblBinType.calculate_bin_by_product)=True));

This query needs to set 'tblBin.bin_width_mm' field to 0 based on the conditions in the query. The query checks to see if the bin is being used in the 'tblProduct.allocated_bin_1' and if tblBinType.calculate_bin_by_product' is set to true.
So far this works but what I want is for the query to check if 'tblProduct.allocated_bin_1' and 'tblProduct.allocated_bin_2' is using a location. I'm struggling to make the query check both of these fields as they require the same join to 'tblAllocatedBin' table.

Please can you help?

Here is an attached sample, ignore the error that pops up upon opening as I stripped the file down.
Update Bins to Zero.zip

Thanks

[pbaldy]

Can't look at the sample right now, but it sounds like you need to add a second instance of tblAllocatedBin to the query and join the second field to it. Access will alias it to ..._1.

[anthgav]

Can't look at the sample right now, but it sounds like you need to add a second instance of tblAllocatedBin to the query and join the second field to it. Access will alias it to ..._1.

Thanks, what you said pointed me in the right direction. I seem to have got it to work by adding 2 instances of tblProduct and joining them to tblAllocatedBin.

UPDATE (tblBinType RIGHT JOIN (tblBin LEFT JOIN (tblAllocatedBin LEFT JOIN tblProduct
ON tblAllocatedBin.allocated_bin_id = tblProduct.allocated_bin_1)
ON tblBin.bin_id = tblAllocatedBin.allocated_bin)
ON tblBinType.bin_type_id = tblAllocatedBin.allocated_bin_type)
LEFT JOIN tblProduct AS tblProduct_1
ON tblAllocatedBin.allocated_bin_id = tblProduct_1.allocated_bin_2
SET tblBin.bin_width_mm = 0
WHERE (((tblProduct.allocated_bin_1) Is Null) AND ((tblProduct_1.allocated_bin_2) Is Null) AND ((tblBinType.calculate_bin_by_product)=True));

[pbaldy]

Sorry about mentioning the wrong table; that's what I get for posting right after I wake up.

Glad you figured it out.

[anthgav]

No problem. Windows XP?, Time for you to upgrade I think.

[pbaldy]

No, time to change my profile!