Append duplicate invoice numbers

[T]

I have a table that has invoices brought in from another program. The
table contains duplicate invoices. I created the find duplicates
query which is working. Now I need to increment the duplicate
invoices by 1. The invoice number cannot be longer than 10
characters. Any assistance with this would be appreciated.

Example:
OldInvoice# NewInvoice#
1560001100 1560001101
1560001100 1560001102
2635001100 2635001101
2635001100 2635001102

Thanks

 

[Michel Walsh]

Appending, or adding? I assume it is adding and that the invoice number
field is a number, not a string.

Have a table, Iotas, one field, iota, with values from 1 to 10 (in 10
records, sure).

Make a first query, q1, say, like:

SELECT invoiceNumber, COUNT(*) As theCount
FROM originalTable
GROUP BY invoiceNumber


Your final query is then something in the line of:

SELECT a.*, a.invoiceNumber + Iotas.Iota AS newInvoiceNumber
FROM (originalTable AS a INNER JOIN q1 ON a.invoiceNumber=q1.invoiceNumber)
INNER JOIN iotas ON iotas.iota <= q1.theCount



Hoping it may help,
Vanderghast, Access MVP