Deleting Anything Duplicate

[Mike]

I have a report that I run weekly in Excel and import it into Access. The
primary key is the customer number, and the constant should be the order
date. Since the report pulls all information old and new, I have to be
careful and delete all the information from last week.

Problem occurred when employees started to change the order date on the
customer numbers and now we have duplicates that are hard to find and we can
no longer sort by dates to remove duplicates.

I want to create a query that only pulls the records that do not have
duplicates, or one that deletes any record with a duplicate as well as the
duplicate.

Please help if you can, I'm in a crunch.

 

[Allen Browne]

What did you want to do?
a) Just create a list of clients + dates that are unique (no combination of
client and date occurs more than once)?

b) Exclude everyone who has a duplicate (so they don't appear at all in the
export)?

For (a), create a totals query, i.e. depress the Total button on the toolbar
in query design view. Access adds as Total row to the design grid. Choose
Group By under the ClientID and the OrderDate. If you want the order number
as well, choose Min or Max under that field.

For (b), use a subquery to see if there is another row that has the same
ClientID and OrderDate, but a different OrderID. If subqueries are new, see:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

All this assumes the OrderDate is a date value only (i.e. it does not
contain a time component.)

 

[Mike]

The reason for the database was to match data from two tables. We are
provided with an Excel spreadsheet of "new orders" for customers,
unfortunately old orders are on there as well. So we would sort by the order
date and remove all orders before a particular date, import it into access
and run a query that matches customer ID's with their addresses to then send
out the work to contractors. We had no problem before, until some of the
schedulers started to reschedule order dates. Now we have "new" order dates
that are matching up with old ones, but the only way I currently have to
identify these and remove then is manually, and we are talking 20,000 records
to sort through.

I have three tables to my database. One with the Customer ID's and their
addresses, then the first "New Orders" report that has the original orders,
and now I have a second "New Orders" report that has new info and the
duplicates. I have a relationship created between the two "New Orders"
tables and you can open the more recent table and expand the record to see if
it is matching up with data from the second table.

I hope this helped clarify.

 

[Allen Browne]

Sorry, Mike: I still don't understand how you define duplicate, nor which
records from which table(s) you need to export.