Acc 97 Update query with criteria

[Bruce Lawrence]

I have 3 tables.

In the MTD table there is a field called 'newgrade' which is currently
blank.
I want to update this field with a value from the MM Update table,
WHERE, the 'mm' field in both tables match.
I also only want to update this field when the order number in MTD is
not in Order Omit.

Basically, the order omit table contains a list of orders in MTD that I
do not want updated.

This is my query:
http://www.c-o-g.com/kool/priv/queryprob.jpg

Can someone tell me why the order omit part of this is being ignored?
I have an order called 12345678 in both tables yet the 'new grade'
field in MTD is still being updated when I don't want it to.

Thank you

 

[John Spencer]

It would be better if you opened your query in SQL view and posted the text
to the group. That would make it simpler to answer your question.

As best as I can tell from your posting, you should end up with something
that looks like the following.

UPDATE MTD INNER JOIN [MM Update Table] as MM
ON MTD.MM = MM.MM
SET MTD.NewGrade = [MM].[New Grade]
WHERE MTD.OrderNumber NOT IN
(SELECT OrderNumber FROM [Order Omit])

 

[Bruce Lawrence]

Thank you for your reply. This is how Access is creating the SQL code
in that query.

UPDATE MTD, [MM Grade update], [order omit] SET MTD.NEW_GRADE = [MM
Grade update]![NEW GRADE]
WHERE ((([MTD]![MATERIAL_NO])=[MM Grade update]![MM]) AND
((MTD.ORDNO)<>[order omit]![ORDNO]));

 

[David F Cox]

Have you got more than one order omit]![ORDNO])) ?

You appear to me to be looking at the entire set.

AND ((MTD.ORDNO) NOT IN (SELECT [order omit].[ORDNO] FROM [order omit] ))
?

 

[John Spencer]

As best as I can determine from you posting, you want to do the following.
MAKE a backup of your data before doing this. The only way you can restore
the data if this goes wrong is to revert to the backup.

UPDATE MTD INNER JOIN [MM Grade update]
ON MTD.Material_No = [MM Grade Update].MM
SET MTD.NEW_GRADE = [MM Grade update].[NEW GRADE]
WHERE MTD.ORDNO NOT IN ( SELECT OrdNo FROM [order omit] )

 

[Bruce Lawrence]

Yes there is more than one record in that order omit table. It has a
list of orders that I do not want the new grade field updated with.

I will try this query to see how it works. I'll keep you posted with
my progress. Thanks!

 

[Bruce Lawrence]

So far so good. The problem i had before was the orders in the order
omit table were being ignored and everything where the material nums
were matching updated to the new material nums. (not good)

I just did our download and processed the query and nothing updated
that shouldn't have so I thank you for your efforts.

The real test is when we get a new order that needs updated

Thanks again