Dmax in an Update Query

[Regi]

Here is a sample of my table:
Task Sub Task Job# Fee Pct
1092 000 1092-000 10
1092 001 1092-001 0
1188 000 1188-000 13
1188 001 1188-001 0
1188 200 1188-200 0

What I need to is to get the fee percentage to fill in where the zeros are.
So 1092-001 would update to 10 and the two 1188 jobs would update to 13. I
already found out that the 0 are Null. Also the "Job#" is a calculated field
from another database program.

My problem is when I run the following Update Query it leaves the 10 and 13
for Job# 1092-000 and 1188-000 but Fee Pct items get blanked out for the
other job#'s.

Here's the query I'm running.

UPDATE [A028 CMF3] SET [A028 CMF3].[Fee Pct] = DMax("[Fee Pct]","[A028
CMF3]","[Job#] =" & [A028 CMF3]![Job#])
WHERE ([A028 CMF3].[Fee Pct]=0 Or [A028 CMF3].[Fee Pct] Is Null) and ([A028
CMF3.SUB TASK]<>"000");

Thanks, Regi

 

[John Spencer]

Assuming Job# is always a combination of Task and Sub Task then I would use

UPDATE [A028 CMF3]
SET [A028 CMF3].[Fee Pct] =
DMax("[Fee Pct]","[A028 CMF3]","[Task] =" & [A028 CMF3]![Task])
WHERE ([A028 CMF3].[Fee Pct]=0 Or [A028 CMF3].[Fee Pct] Is Null)
and ([A028 CMF3.SUB TASK]<>"000");


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County