G
Guest
Hi
I've searched all around the net, but no "solution" seems to do what I'm
trying to achieve.
I have a database that imports data from a group of Excel spreadsheets. Due
to the manner in which these are generated, some duplicate information will
exist in the database after importing.
The table in question is called LOG, and has a unique primary key. The
remaining fields are EntryDate, EntryTime, EntryType and EntryMessage.
I would like to make a single query using SQL in Access that searches the
LOG table and removes all but the oldest record where EntryDate, EntryTime,
EntryType and EntryMessage are identical.
I figure there are few ways to do this:
1. Rename LOG to OLD_LOG, do a SELECT DISTINCT into LOG.
2. Use a GROUPBY and COUNT to return duplicates, and then delete those with
ENTRYID > MIN(ENTRYID).
3. Use a SELECT query to find duplicate records and save them into
DUPLICATES, then use a DELETE query WHERE LOG.* = DUPLICATES.*
The trouble is although I understand the principles of databases, I have
only a basic knowledge of SQL so I'm kindof winging it. Perhaps someone out
there would be kind enough to help?
Thanks
..Dave
I've searched all around the net, but no "solution" seems to do what I'm
trying to achieve.
I have a database that imports data from a group of Excel spreadsheets. Due
to the manner in which these are generated, some duplicate information will
exist in the database after importing.
The table in question is called LOG, and has a unique primary key. The
remaining fields are EntryDate, EntryTime, EntryType and EntryMessage.
I would like to make a single query using SQL in Access that searches the
LOG table and removes all but the oldest record where EntryDate, EntryTime,
EntryType and EntryMessage are identical.
I figure there are few ways to do this:
1. Rename LOG to OLD_LOG, do a SELECT DISTINCT into LOG.
2. Use a GROUPBY and COUNT to return duplicates, and then delete those with
ENTRYID > MIN(ENTRYID).
3. Use a SELECT query to find duplicate records and save them into
DUPLICATES, then use a DELETE query WHERE LOG.* = DUPLICATES.*
The trouble is although I understand the principles of databases, I have
only a basic knowledge of SQL so I'm kindof winging it. Perhaps someone out
there would be kind enough to help?
Thanks
..Dave