Query help to remove duplicates

  • Thread starter Thread starter MsBing
  • Start date Start date
M

MsBing

I am a new access user and so far only know the basics.

I have a excel sheet with approx. 40,000 rows of data. I would like to
import the spreadsheet and run a query to remove the duplicate records with
the earliest dates.
e.g.
Bob's Tire 2/5/06 - remove this one
Bob's Tire 3/14/02- and remove this one
Bob's Tire 1/1/08

Any suggestions on the best way to do this? I know how to run a basic
duplicate query, but not to customize it by date.
TIA, I know this will be much faster than trying to import a formula and sort
it for this large of a spreadsheet in excel.
 
It is probably easier to link to this spreadsheet, and then create a query
that grabs the most recent Date for each Organization and inserts that into
your table. Assuming you already have the structure of your destination
table built, it might look like:

INSERT INTO tblNewTable (CompanyName, SomeDate, Field3, Field4, ...)
SELECT T.CompanyName, T.SomeDate, T.Field3, T.Field4, ...
FROM yourLinkedExcelSpreadsheet T
WHERE SomeDate = (SELECT Max(SomeDate)
FROM yourLinkedExcelSpreadsheet T1
WHERE T1.CompanyName = T.CompanyName)

HTH
Dale
 
Let me clarify.
I need to remove all of the old records of any duplicate companies. I am
trying to cut my list down so I am not sending information to the same
company twice. Instead of just running a duplicate query, I need to remove
the duplicates and leave the most recent record behind. Does this make more
sense?

Dale said:
It is probably easier to link to this spreadsheet, and then create a query
that grabs the most recent Date for each Organization and inserts that into
your table. Assuming you already have the structure of your destination
table built, it might look like:

INSERT INTO tblNewTable (CompanyName, SomeDate, Field3, Field4, ...)
SELECT T.CompanyName, T.SomeDate, T.Field3, T.Field4, ...
FROM yourLinkedExcelSpreadsheet T
WHERE SomeDate = (SELECT Max(SomeDate)
FROM yourLinkedExcelSpreadsheet T1
WHERE T1.CompanyName = T.CompanyName)

HTH
Dale
I am a new access user and so far only know the basics.
Click to expand...
[quoted text clipped - 10 lines]
TIA, I know this will be much faster than trying to import a formula and sort
it for this large of a spreadsheet in excel.
Click to expand...
Click to expand...
 
Exactly what I thought you meant, and I'd still do it the way I recommended
before. It is far easier to select a subset of data than it is to delete a
subset of data.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



MsBing said:
Let me clarify.
I need to remove all of the old records of any duplicate companies. I am
trying to cut my list down so I am not sending information to the same
company twice. Instead of just running a duplicate query, I need to remove
the duplicates and leave the most recent record behind. Does this make more
sense?

Dale said:
It is probably easier to link to this spreadsheet, and then create a query
that grabs the most recent Date for each Organization and inserts that into
your table. Assuming you already have the structure of your destination
table built, it might look like:

INSERT INTO tblNewTable (CompanyName, SomeDate, Field3, Field4, ...)
SELECT T.CompanyName, T.SomeDate, T.Field3, T.Field4, ...
FROM yourLinkedExcelSpreadsheet T
WHERE SomeDate = (SELECT Max(SomeDate)
FROM yourLinkedExcelSpreadsheet T1
WHERE T1.CompanyName = T.CompanyName)

HTH
Dale
I am a new access user and so far only know the basics.
Click to expand...
[quoted text clipped - 10 lines]
TIA, I know this will be much faster than trying to import a formula and sort
it for this large of a spreadsheet in excel.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top