Copy certain records in a form

[pbrown2]

I need to copy all records that have the highest number in the [NoMonth] field.
I.E.
If the table has 168 records, with a value of 1 in [NoMonth] for 100 records, value of 2 for [NoMonth] for 8 records, and the remaining 60 records have a value of 3 for [NoMonth]. I need to have a button that will automatically look at the [NoMonth] field, find the highest value and copy all records with that value. However, when they are copied, I need the [NoMonth] value to be automatically increased by 1.

So in this case the button would find all 60 records with a [NoMonth] value of 3, but copy them with a [NoMonth] value of 4.

I have set up an append query that looks at the fields, but am unable to restrict it to only copy the highest value, and am unable to get it to automatically add 1 to the value in [NoMonth]. Currently the query copies all 168 records and gives the new records the same value as the value showing on the form.

Any suggestions?

Thank you for any and all help,

PBrown

 

[PHV]

Something like this ?[tt]
SELECT NoMonth + 1 AS NewMonth, Field2, ..., FieldN
FROM YourTable
WHERE NoMonth = (SELECT Max(NoMonth) FROM YourTable)[/tt]

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[pbrown2]

PHV,
It looks like it would work, however, I am not use to writting queries into access. Also, what is being referred to as NewMonth and field 2?

Thank you for any and all help,

PBrown