find dupes query

[Meri]

Can you MVPs help a novice user?

I have taken a table that has invoices and assigned a 2-
decimal extension to each, thereby making each iteration
unique, except for dupes. (Its a multiple iteration
receipt record against an single invoice.)

I have found the dupes. Now I would like to Update Query
by assigning an incremental extension. Alas, I don't know
how to retain the first iteration (by date) as already
assigned and affect only the second, to be incremented,
iteration, then the do the same for the remainder of an
invoice's iterations. And for all my dupes.

I am so open to suggestions. Thanks.

Meri

 

[Ken Snell]

This can be done by running two separate queries.

The first query will be a "make-table" query. The resulting table will then
be used to update the original table via a second query.

Use the following "generic" SQL statement to construct your first query (the
"make table" query):

SELECT TableName.InvoiceNumber, TableName.InvoiceDate,
(SELECT Count(*) FROM TableName AS T
WHERE T.InvoiceNumber = TableName.InvoiceNumber AND
T.InvoiceDate < TableName.InvoiceDate) AS NumDups
INTO TableName_New
FROM TableName;


Use the following "generic" SQL statement to construct your update query:

UPDATE TableName INNER JOIN TableName_New
ON TableName.InvoiceNumber = TableName_New.InvoiceNumber AND
TableName.InvoiceDate = TableName_New.InvoiceDate
SET TableName.InvoiceNumber = TableName.InvoiceNumber &
IIf(TableName_New.NumDups > 0, TableName_New.NumDups, "");


Make a backup copy of the original table. Then run the make-table query, and
then run the update query. The update query will append a 1 to the end of
the InvoiceNumber field for the second record, a 2 for the third record,
etc.

 

[Ken Snell]

Now, let me recommend that you change your table structure slightly. In a
relational database, it's usually not a good practice to store combined data
in one field. In your database, you're combining the InvoiceNumber and the
InvoiceSequence together into one field. This will mean that you'll always
need to use a query (with a calculated field to parse the data) in order to
do any type of join on the InvoiceNumber (non-decimal part) and get reports,
etc.

What I recommend is that you add a new field to your table (name it
InvoiceSeq) and then put the "decimal" portion(s) in that field. You'll find
that that will be much more flexible and workable for you down the road.