Items that exist in one table but not another 1

[wdverner]

Hi All,
I have a particular problem that I have not seen before nor know the best approch to solve.

TO explain...

User_1 creates a list of items that makes up a product.

Each item has a cost, so I created a query to look up the corresponding cost.

User_2 is responsible for costs. All he does is add/amend costs of each item.

What I want to do is this:
1) When User_1 creates a list, I want it to check up the costs and retreive for each item- Done.

2) Any Items that are returned with NO COST beside them means that the cost of this item is not present in the Costs table. i want to flag these items to User_2 and notify him that he needs to add costs for these items.

When he does so User_3 can then view a complete list with costs for every item.

how do I best achieve part 2)?

I can return a list of items with lookup of costs. There are a number without costs, how do I capture these? IsNull, " " doesnt work.

Many thanks for your time and suggestions,

 

[lynchg]

If the cost is just a field in the Item table and IsNull is not working then try this:

where len(trim(cost)) = 0

If the cost info is in it's own table linked by some item identifier, then do a left join from the Item table to the Cost table, joining on the item identifier field and use a Where clause like this:

where Cost.ItemID Is Null

 

[M8KWR]

Have you tried using the unmatched query wizard, this should do it for you, you may need to edit the id number etc, have you used instead of is null, <0.00001

 

[wdverner]

Thanks LynchG.
Yes I was using IsNull against Cost as opposed to is Null against ItemID.

Many thanks!