Update query problem

R

Regi

I have a table called A028 CMF2. In that table I have a fields called Job#,
Sub Task and Fee Pct.

An amount for the Fee Pct comes over from another system but it is only on a
Job#'s main task. I need that Fee Pct to be copied on to the Job#'s Sub Task.
See below.
Current read out
Job# Sub Task Fee Pct
1188 200 13.00 (main task)
1188 210 0.00 (sub task)
1188 220 0.00 (sub task)

Desired read out

Job# Sub Task Fee Pct
1188 200 13.00
1188 210 13.00
1188 220 13.00

I asked this question previously and was told to run an Update Query using
Dmax. I tried it but got a conversion error, key violation, lock violation
and validation rule violation. Can someone see what I'm doing wrong?
UPDATE [A028 CMF2]
SET [A028 CMF2].[Fee Pct] = DMax("[A028 CMF2]![Fee Pct]","[A028 CMF2]![Job#]
=" & [A028 CMF2]![Job#]);

I'm new to update queries and DMax.

Thanks, Regi
 
J

John Spencer

Try the following.

UPDATE [A028 CMF2]
SET [A028 CMF2].[Fee Pct] =
DMax("[Fee Pct]","[A028 CMF2]", "[Job#] =" & [A028 CMF2]![Job#])
WHERE [A028 CMF2].[Fee Pct] = 0 or [A028 CMF2].[Fee Pct] is Null

BACKUP your data first in case this does not work the way you want.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
R

Regi

John
I copied this into a query and ran it. No errors but it left the percentage
(13.00) in the main job# but then cleared the fields out for the sub tasks. (
ie They appear blank) Suggestions?
Thanks


John Spencer said:
Try the following.

UPDATE [A028 CMF2]
SET [A028 CMF2].[Fee Pct] =
DMax("[Fee Pct]","[A028 CMF2]", "[Job#] =" & [A028 CMF2]![Job#])
WHERE [A028 CMF2].[Fee Pct] = 0 or [A028 CMF2].[Fee Pct] is Null

BACKUP your data first in case this does not work the way you want.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Regi said:
I have a table called A028 CMF2. In that table I have a fields called Job#,
Sub Task and Fee Pct.

An amount for the Fee Pct comes over from another system but it is only on
a
Job#'s main task. I need that Fee Pct to be copied on to the Job#'s Sub
Task.
See below.
Current read out
Job# Sub Task Fee Pct
1188 200 13.00 (main task)
1188 210 0.00 (sub task)
1188 220 0.00 (sub task)

Desired read out

Job# Sub Task Fee Pct
1188 200 13.00
1188 210 13.00
1188 220 13.00

I asked this question previously and was told to run an Update Query using
Dmax. I tried it but got a conversion error, key violation, lock violation
and validation rule violation. Can someone see what I'm doing wrong?
UPDATE [A028 CMF2]
SET [A028 CMF2].[Fee Pct] = DMax("[A028 CMF2]![Fee Pct]","[A028
CMF2]![Job#]
=" & [A028 CMF2]![Job#]);

I'm new to update queries and DMax.

Thanks, Regi
Click to expand...
Click to expand...
 
J

John Spencer

Did you run the query (Query: Run on the menu) or did you simply switch to
datasheet view? If the latter then all you see is the existing data that
would be updated if you run the query.

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

Regi said:
John
I copied this into a query and ran it. No errors but it left the
percentage
(13.00) in the main job# but then cleared the fields out for the sub
tasks. (
ie They appear blank) Suggestions?
Thanks


John Spencer said:
Try the following.

UPDATE [A028 CMF2]
SET [A028 CMF2].[Fee Pct] =
DMax("[Fee Pct]","[A028 CMF2]", "[Job#] =" & [A028 CMF2]![Job#])
WHERE [A028 CMF2].[Fee Pct] = 0 or [A028 CMF2].[Fee Pct] is Null

BACKUP your data first in case this does not work the way you want.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Regi said:
I have a table called A028 CMF2. In that table I have a fields called
Job#,
Sub Task and Fee Pct.

An amount for the Fee Pct comes over from another system but it is only
on
a
Job#'s main task. I need that Fee Pct to be copied on to the Job#'s Sub
Task.
See below.
Current read out
Job# Sub Task Fee Pct
1188 200 13.00 (main task)
1188 210 0.00 (sub task)
1188 220 0.00 (sub task)

Desired read out

Job# Sub Task Fee Pct
1188 200 13.00
1188 210 13.00
1188 220 13.00

I asked this question previously and was told to run an Update Query
using
Dmax. I tried it but got a conversion error, key violation, lock
violation
and validation rule violation. Can someone see what I'm doing wrong?
UPDATE [A028 CMF2]
SET [A028 CMF2].[Fee Pct] = DMax("[A028 CMF2]![Fee Pct]","[A028
CMF2]![Job#]
=" & [A028 CMF2]![Job#]);

I'm new to update queries and DMax.

Thanks, Regi
Click to expand...
Click to expand...
Click to expand...
 
R

Regi

Yes, I did Run the query. I did it again just to make sure and the same thing
happened.

John Spencer said:
Did you run the query (Query: Run on the menu) or did you simply switch to
datasheet view? If the latter then all you see is the existing data that
would be updated if you run the query.

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

Regi said:
John
I copied this into a query and ran it. No errors but it left the
percentage
(13.00) in the main job# but then cleared the fields out for the sub
tasks. (
ie They appear blank) Suggestions?
Thanks


John Spencer said:
Try the following.

UPDATE [A028 CMF2]
SET [A028 CMF2].[Fee Pct] =
DMax("[Fee Pct]","[A028 CMF2]", "[Job#] =" & [A028 CMF2]![Job#])
WHERE [A028 CMF2].[Fee Pct] = 0 or [A028 CMF2].[Fee Pct] is Null

BACKUP your data first in case this does not work the way you want.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table called A028 CMF2. In that table I have a fields called
Job#,
Sub Task and Fee Pct.

An amount for the Fee Pct comes over from another system but it is only
on
a
Job#'s main task. I need that Fee Pct to be copied on to the Job#'s Sub
Task.
See below.
Current read out
Job# Sub Task Fee Pct
1188 200 13.00 (main task)
1188 210 0.00 (sub task)
1188 220 0.00 (sub task)

Desired read out

Job# Sub Task Fee Pct
1188 200 13.00
1188 210 13.00
1188 220 13.00

I asked this question previously and was told to run an Update Query
using
Dmax. I tried it but got a conversion error, key violation, lock
violation
and validation rule violation. Can someone see what I'm doing wrong?
UPDATE [A028 CMF2]
SET [A028 CMF2].[Fee Pct] = DMax("[A028 CMF2]![Fee Pct]","[A028
CMF2]![Job#]
=" & [A028 CMF2]![Job#]);

I'm new to update queries and DMax.

Thanks, Regi
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Well, I am stumped. That should work assuming theat A028 CMF2 is a table
that has the values in it.





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

Yes, I did Run the query. I did it again just to make sure and the same thing
happened.

John Spencer said:
Did you run the query (Query: Run on the menu) or did you simply switch to
datasheet view? If the latter then all you see is the existing data that
would be updated if you run the query.

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

Regi said:
John
I copied this into a query and ran it. No errors but it left the
percentage
(13.00) in the main job# but then cleared the fields out for the sub
tasks. (
ie They appear blank) Suggestions?
Thanks


:

Try the following.

UPDATE [A028 CMF2]
SET [A028 CMF2].[Fee Pct] =
DMax("[Fee Pct]","[A028 CMF2]", "[Job#] =" & [A028 CMF2]![Job#])
WHERE [A028 CMF2].[Fee Pct] = 0 or [A028 CMF2].[Fee Pct] is Null

BACKUP your data first in case this does not work the way you want.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table called A028 CMF2. In that table I have a fields called
Job#,
Sub Task and Fee Pct.

An amount for the Fee Pct comes over from another system but it is only
on
a
Job#'s main task. I need that Fee Pct to be copied on to the Job#'s Sub
Task.
See below.
Current read out
Job# Sub Task Fee Pct
1188 200 13.00 (main task)
1188 210 0.00 (sub task)
1188 220 0.00 (sub task)

Desired read out

Job# Sub Task Fee Pct
1188 200 13.00
1188 210 13.00
1188 220 13.00

I asked this question previously and was told to run an Update Query
using
Dmax. I tried it but got a conversion error, key violation, lock
violation
and validation rule violation. Can someone see what I'm doing wrong?
UPDATE [A028 CMF2]
SET [A028 CMF2].[Fee Pct] = DMax("[A028 CMF2]![Fee Pct]","[A028
CMF2]![Job#]
=" & [A028 CMF2]![Job#]);

I'm new to update queries and DMax.

Thanks, Regi
Click to expand...
Click to expand...
Click to expand...
 
R

Regi

Well I really apreciate you're trying. Have a good day! Regi

John Spencer said:
Well, I am stumped. That should work assuming theat A028 CMF2 is a table
that has the values in it.





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

Yes, I did Run the query. I did it again just to make sure and the same thing
happened.

John Spencer said:
Did you run the query (Query: Run on the menu) or did you simply switch to
datasheet view? If the latter then all you see is the existing data that
would be updated if you run the query.

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

John
I copied this into a query and ran it. No errors but it left the
percentage
(13.00) in the main job# but then cleared the fields out for the sub
tasks. (
ie They appear blank) Suggestions?
Thanks


:

Try the following.

UPDATE [A028 CMF2]
SET [A028 CMF2].[Fee Pct] =
DMax("[Fee Pct]","[A028 CMF2]", "[Job#] =" & [A028 CMF2]![Job#])
WHERE [A028 CMF2].[Fee Pct] = 0 or [A028 CMF2].[Fee Pct] is Null

BACKUP your data first in case this does not work the way you want.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table called A028 CMF2. In that table I have a fields called
Job#,
Sub Task and Fee Pct.

An amount for the Fee Pct comes over from another system but it is only
on
a
Job#'s main task. I need that Fee Pct to be copied on to the Job#'s Sub
Task.
See below.
Current read out
Job# Sub Task Fee Pct
1188 200 13.00 (main task)
1188 210 0.00 (sub task)
1188 220 0.00 (sub task)

Desired read out

Job# Sub Task Fee Pct
1188 200 13.00
1188 210 13.00
1188 220 13.00

I asked this question previously and was told to run an Update Query
using
Dmax. I tried it but got a conversion error, key violation, lock
violation
and validation rule violation. Can someone see what I'm doing wrong?
UPDATE [A028 CMF2]
SET [A028 CMF2].[Fee Pct] = DMax("[A028 CMF2]![Fee Pct]","[A028
CMF2]![Job#]
=" & [A028 CMF2]![Job#]);

I'm new to update queries and DMax.

Thanks, Regi
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Update Query problem 7
Dmax in an Update Query 1
Query Expression problem 1
Need to get a field to repeat in a table or query 5
Print Query Expressions 0
Access Append Query Failure 1
SQL Syntax for an update query 2
refresh a subform 3

Top