Update grouped records with incremented number

[primagic]

Hi.

I need to update a set of records with a number incremented from another table.

I have an invoice table with two columns - InvoiceID, InvoiceNumber.

I have another table with a list of account transactions.

Account Number Name Date InvoiceNumber


I am need to run an update on the account transactions that will add the next invoice number to the records. At the moment the user enters dates into two textboxes to retrieve a set of records.

These may look like:
Account Number Name Date InvoiceNumber
1111111 ABC 04/10/11 (Null)
1111111 ABC 04/10/11 (Null)
1111115 RFG 04/10/11 (Null)
1111116 KOL 04/10/11 (Null)

What I need to do is, add a record to the invoice table which creates in invoice number (Dmax+1), get that invoice number and update the invoice number column in the dataset above.

So if the first invoice number created was 1000001 then my data would look like:

Account Number Name Date InvoiceNumber
1111111 ABC 04/10/11 1000001
1111111 ABC 04/10/11 1000001
1111115 RFG 04/10/11 1000002
1111116 KOL 04/10/11 1000003

So a different invoice number for each account that is the same. The update must only be done for records being retrieved from those dates. because the account number could appear in another date but will get a different account number

Thanks

 

[Darrylles]

primagic,

And you can't do this because - why?

ATB

Darrylles

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[primagic]

I can update each record with a different account number but trying to figure out how to check if it's the same account number allocate the same invoice number