highest value with update query 
Author Message
 highest value with update query

I am using Access 2000 with VBA.

I would like to run a query to increase the total field
for the record with the highest counter field (in this example,
the counter field is autonumber).

When I know the highest counter value is nine (9), I can set it equal
to nine and it seems to work:

UPDATE tbldata SET tbldata.total = [total]+12
WHERE ((([tbldata].[counter])=9));

(Just for example, the counter will always be incrementing
by one for each new record).

I think it should work with the max statement, using something like this:

UPDATE tbldata SET tbldata.total = [total]+12
WHERE (((Max([counter] as highest))));

Unfortunately, it keeps telling me I have a syntax error in that statement.
I have tried varying the number of parenthesis, such as this:

UPDATE tbldata SET tbldata.total = [total]+12
WHERE (Max([counter]) as highest);

and it still does not work.

Please tell me what I need to do differently.

Thank you in advance for your cooperation.



Fri, 01 Jul 2005 23:30:30 GMT
 highest value with update query

Quote:

> I am using Access 2000 with VBA.

> I would like to run a query to increase the total field
> for the record with the highest counter field (in this example,
> the counter field is autonumber).

> When I know the highest counter value is nine (9), I can set it equal
> to nine and it seems to work:

> UPDATE tbldata SET tbldata.total = [total]+12
> WHERE ((([tbldata].[counter])=9));

Dont know about access, but the following should work on most databases:

UPDATE tbldata SET total = total + 12
WHERE counter = ( select max(counter) from tbldata )

HTH
/Lennart

[...]



Sat, 02 Jul 2005 01:21:36 GMT
 highest value with update query

Quote:
>> I am using Access 2000 with VBA. <<

I hope you can escape to a real SQL some day.  

Quote:
>> I would like to run a query to increase the total field [sic] for

the record [sic] with the highest counter field [sic] (in this
example, the counter field [sic] is autonumber). <<

SQL has rows, not records, and columns, not fields.  There are major
differences in these things.  AUTONUMBER is a non-relational flaw that
you should never use; it applies to sequential files and not to
tables.

UPDATE Foobar
   SET total
       = total +
         (SELECT MAX (counter)
            FROM Foobar);



Sat, 02 Jul 2005 06:01:21 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. select value then update value without intervening reads/updates

2. SQL query to update primary key values

3. Return value of an update query

4. Updating with a value from a sub-query

5. Update query returning more than 1 value

6. Returning value from an update query

7. updating multiple rows with values from a query

8. Update Query with date value

9. Selecting fields with the HIGHEST value?

10. how to get the higher value?

11. Highest Values

12. Second highest value


 
Powered by phpBB® Forum Software