Retrieve Highest Value in Table??

[TrishP]

I have to write Macros or some other code to automate a series of tasks in my Access 2000 db (I am clueless).

What I need to do in general is:

1) Find the highest value of a column in Table1 and save it.

2) Delete all the rows in Table1 and Table2.

3) Using the Highest Value I just retrieved, place it in the criteria cell for my query (overriding the one from the day before.. ie: >1234 would need to be replaced with >1355 that I just retrieved).

4) Run the query and save the changes.

There are more steps after this (ie: I have multiple queries to run), but this is the hardest part...Any help would be appreciated. Any samples of a similar macro would be useful... Thanks so very much...

 

[gol4]

This would be a piece of cake for VBA but to do it in a macro I suggest you look at it a bit differently
finding the highest value for the columns in table1 is easy =dmax("[fieldname]","table1&quot
but if you delete all the rows it won't be avaliable
so delete all but that record
runsql ...delete * from table1 where field <> dmax(&quot;fieldname&quot;,table1)
then delete table2 data
runsql...delete * from table2
now
use table1's one remaining record as your source of your queries
then delete it from table1 so you have a fresh strat tomorrow

runsql delete * from table1

 

[TrishP]

Thanks so much for steering me in the right direction. I ended up doing it a little differently.... I made a Make Table query using the DMAX function to store the highest numeric value alone; I then made 2 additional queries that deleted all the rows in my two tables. Finally, using OpenQuery in the macro I ran the tables in order to get the results I wanted. Thanks for pointing the DMAX function out to me!!!!!