[JoeMicro]

Hi,

Is it possible to have an Update Query that increment all records in a field

Ex:
Record ID Trans#
1 100
2 101
3 102
4 103
5 104
6 105

Thanks
Joe

[ScottGem]

Sure:

UPDATE table SET field = Field+1;

[JoeMicro]

Thanks Scott.

This just increments all the records to +1.

What I need is:

All the records in Trans# are empty, and by excuting the query (or vba) all the records will beincremented from the previus, as in my ex.

101
102
103
104

[ScottGem]

Ahh, Lookup Running Sum in Access Help. That explains how to do what you want.

[JoeMicro]

Thank Scott

sorry for letting you wait, I was ut of the office for a few days.

here is what I did

DCount("AccountNo","tblUnique","RecordID<=" & [tblUnique].[RecordID]).

now all this is a workaround to what I really wanted to accomplish.

what I really need is:

I have got a subform

Field1 = RecordID
Field2 = AccountNo
Field3 = Receipt

I would like to have a button that gives to each AccountNo a receipt no, meaning that if there is a few of the same AccountNo's it should all have the same Receipt #, and so on.

Ex:

AccountNo Receipt
AC22 2311
AC22 2311
FG56 2312
AC22 2311
FG56 2312

My workaround is. I append to a new table all the unique AccounNo's, with the running sum I increment a ReceiptNo for each AccountNo, then with an Update Query I Do the ReceiptNo on the Form. Its all being done in vba, so for the user its still one button, but for me my question is, Is there an easier way

Joe

[ScottGem]

See, now, if you had explained your ultimate goal I would. Have made a different suggestion.

Create a query of unique Account numbers and export that query to Excel. Use Excel's ability to create a sequential number then link back to that table and do an Update query that updates the number based on the account.

For new accounts, I would use the following expression to assign a new receipt.

Nz(DLookup("[Receipt]","table","[AccountNo] = '" & curracct &"'"),Dmax("[Receipt]","Table")+1)

[JoeMicro]

Now, what's the benefit of using Excel over doing the same thing in the same file in Access? I think I'm getting the same results?

And for the new receiptNo's I actually used

DLookup("max(ReceiptNo)","tblReceipts")+1+DCount(" AccountNo","tblUnique","RecordID<=" & [tblUnique].[RecordID])

Reeason: because its not always the same accountNo with the same ReceiptNo, it works by batch. So in each batch I have to reassign receiptNo's for each AccountNo in that batch.
So for each batch the process is being done over again with new no's.

Thanks Scott for taking your time.

Joe