Deleting Duplicates

[Tom]

On a monthly basis I receive a text file that adds new current data to it
along with previous data already received.

First, I add a colunm to this data, to make comments to review for posible
errors each month to data received. About 5% I make a comment in this new
column each month.

When importing the monthly data, I delete all records that are the exact
same for all fields and send to another database. The problem I am having is
that it will not delete any duplicate records that I have entered a comment
in the field I made.

Is thier a way to delete all duplicate records and ignore only the one
column I have created?

Your help is appreciated.
Thanks

 

[a a r o n _ k e m p f]

you need SQL Server to do this

if you try to do this in Jet, you go to edit a table while other
people have it open-- you get a locking error message (bug).

-Aaron

 

[Ken Sheridan]

Tom:

Firstly, ignore Aaron; he's clearly forgotten to take his medication
again.

Compare all columns apart from your Comments column, e.g. as a SELECT
query:

SELECT *
FROM Table1
WHERE EXISTS
(SELECT *
FROM Table2
WHERE Table2.SomeColumn = Table1.SomeColumn
AND Table2.SomeOtherColumn = Table1.SomeOtherColumn
AND Table2.YetAnotherColumn = Table1.YetAnotherColumn);

If this returns the correct rows to be archived/deleted you can then
easily create and execute modified versions of it, one to append the
rows to the 'archive' table and the other to delete the rows from the
original table.

Ken Sheridan
Stafford, England

 

[a a r o n _ k e m p f]

wow, you're the one that is giving a SELECT statement when he asked
for a DELETE statement

Jet is just crap, it's time to move on, guys!

SQL Server is the worlds most popular database..
Pretty soon, Microsoft is going to 'pull a foxPro' on you guys,
actually-- they already have.

 

[Tom]

Sheridan
Thank you---but I do not understand your reply

 

[Ken Sheridan]

Tom:

The SQL I sent you is for a query which compares the two tables on the
basis of (for simplicity in the example) three columns, all of which
must have identical values for the rows from Table1 to be returned.
It works by means of a subquery with a series of expressions, one for
each column, tacked together with AND operators. So each expression
must be true for the subquery to return any rows. The expressions
compare the columns in the subquery's table (table2) with those in the
outer query's (Table1). By using the EXISTS predicate in the outer
query's WHERE clause it will only return rows where the subquery
returns at least one row for each of the outer query's rows. So the
outer query will return a row from Table1 if there is an exact match
in any row in Table2 in all of the columns you specify. By excluding
your Comments column from the expressions in the subquery therefore,
it will consequently still detect duplicates irrespective of the
comments you've added.

To test it just paste the SQL statement into a new query in SQL view,
change the table and column names to your own, adding further ANDed
expressions to the subquery for the other columns apart from your
Comments column. It should then return the 'duplicated' rows,
including any to which you've added comments.

You can easily amend the query to an 'append' query to copy the
duplicated rows to an 'archive' table, and to a 'delete' query to
remove duplicated the rows from the original. This can be done in
design view rather than SQL view if you switch to that, saving the
query under a different name for each type. Each month you'd execute
the 'append' query to archive the rows before the 'delete' query of
course.

Ken Sheridan
Stafford, England

 

[Jellifish]

Aaron,

Access is not Jet. Jet is not Access. If you have nothing of value to
contribute then find something worthy to do with your life instead of
wasting it posting crap on here.

wow, you're the one that is giving a SELECT statement when he asked
for a DELETE statement

Jet is just crap, it's time to move on, guys!

SQL Server is the worlds most popular database..
Pretty soon, Microsoft is going to 'pull a foxPro' on you guys,
actually-- they already have.

 

[Arvin Meyer [MVP]]

SQL Server is the worlds most popular database..

Actually, that honor would go to Access which probably has more users, and
more databases in use, than all other databases combined. SQL-Server is very
popular, but comes in behind Oracle, which is the largest used server based
database. For my money, SQL-Server is way ahead of Oracle in features, but
it's hard to beat a system that's been around since 1972 and runs on almost
any platform.

 

[a a r o n _ k e m p f]

Arvin;

you are so full of crap, I mean seriously

SQL Server ships more seats, more downloads, more installations-- than
either MS Access or Oracle.
Yes, Oracle gets more licensing revenue-- because they are atrociously
priced.

Oracle isn't competitive, because it doesn't run on Windows.
WIndows is the worlds most popular operating system.

Microsoft has 10 times the market share of Oracle on Windows.

www.microsoft.com/sql is where MIcrosoft declares that SQL Server is
the worlds most popular database.

Jet ****ing sucks and anyone that uses it for anything is just plain
stupid.
Excel is infinitely more scalable and reliable than Access (Jet)

 

[Tom]

Ken

Sorry to take so long to respond to you. With what you are saying I think
you understand my problem. But, I use wizards for queries and not a SQL user
or know much about it.

Your help is appreciated. I am obviously not as experienced as you and can
you help with use of wizards in Access OR lead me by the hand more? Just not
grasping your response where I can actually try them.

I understand if you can not help a inexperienced like myself and appreciate
your time and help to this point.

 

[John W. Vinson]

Ken

Sorry to take so long to respond to you. With what you are saying I think
you understand my problem. But, I use wizards for queries and not a SQL user
or know much about it.

Your help is appreciated. I am obviously not as experienced as you and can
you help with use of wizards in Access OR lead me by the hand more? Just not
grasping your response where I can actually try them.

I understand if you can not help a inexperienced like myself and appreciate
your time and help to this point.

Pardon me for jumping in...

A query, whether it's generated by a wizard or by the query grid, is actually
a SQL string. That is how the computer stores and manages the query - it *IS*
that string.

What Ken was suggesting is that you create a new Query. Don't add any tables,
don't use any wizards. Select View... SQL on the menu. You should see just

SELECT;

in a big white textbox.

Replace this with the SQL query that Ken offered you. Change any table or
fieldnames if necessary (his query has scrolled off the newsgroup for me). See
if the query that Ken (himself a wizard, but a lot smarter than Microsoft's
<g>) gave you works.