eliminate Duplicates from a SINGLE table

[JR]

Looking for a query that will eliminate duplicates from a SINGLE table. Will
have to do this time and again so manually eliminating from a find duplicates
query, while workable, is not a good solution.

Anyone know how to (the help from Microsoft on this is 18 pages long and
makes my head hurt before the bottom of the first page).

Thanks!

 

[Jeff Boyce]

If you'll describe the situation which causes the need to "do this time and
again", folks here may be able to offer suggestions that could reduce or
eliminate that need.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Graham Mandeno]

Hi JR

It seems the easiest solution is to add a unique index to the table so that
you don't get duplicates in the first place!

If this is not workable, then you can write some code to open your query
that lists the duplicates and loops through the records, deleting all but
the first record in each group.

 

[JR]

I get large amounts of data that have to be processed from sources external
to my organization.
Often the data is composed of stock numbers and how many orders per unit of
time were made against the stock numbers. So the stock numbers show up time
after time.
At the same time information about physical characteristics of the items the
stock numbers represent is maintained in a separate table because not
everyone enters correct basic data (such as weight and cube) so the
"official" weight and cube has to be maintained.
Because other data (not stock numbers) in the same record as the stock
numbers are not consistant you can wind up with the same stock number showing
up more than once due to the differint column data. This is usually the
cause of duplicate stock numbers.
It goes on and on ... Fact is that duplicate stock numbers show up
consistantly.

 

[Graham Mandeno]

Hi JR

Can the external data not be pre-processed to deal with the problems before
importing it into your main table(s)?

It seems to me a much better idea not to import the duplicates in the first
place.

 

[JR]

I would still need the query.

A little more: I get reports of daily activity once a week. stock numbers
may be ordered more than once a day or by more than one customer. By the end
of the week there are multiple occurances of the stock numbers. Data come in
in excel, get imported to access. Individual orders can not be deleted just
because the stock number shows up more than once.

This seems about as hopeless as the Microsoft "help" on the subject.

Hi JR

Can the external data not be pre-processed to deal with the problems before
importing it into your main table(s)?

It seems to me a much better idea not to import the duplicates in the first
place.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I get large amounts of data that have to be processed from sources external
to my organization.
Often the data is composed of stock numbers and how many orders per unit
of
time were made against the stock numbers. So the stock numbers show up
time
after time.
At the same time information about physical characteristics of the items
the
stock numbers represent is maintained in a separate table because not
everyone enters correct basic data (such as weight and cube) so the
"official" weight and cube has to be maintained.
Because other data (not stock numbers) in the same record as the stock
numbers are not consistant you can wind up with the same stock number
showing
up more than once due to the differint column data. This is usually the
cause of duplicate stock numbers.
It goes on and on ... Fact is that duplicate stock numbers show up
consistantly.

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "RemoveDuplicates.mdb " which illustrates how to do this. You
should be able to adapt it to your situation, but it requires a single
unique index. You can find the sample here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=285

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I would still need the query.

A little more: I get reports of daily activity once a week. stock
numbers
may be ordered more than once a day or by more than one customer. By the
end
of the week there are multiple occurances of the stock numbers. Data come
in
in excel, get imported to access. Individual orders can not be deleted
just
because the stock number shows up more than once.

This seems about as hopeless as the Microsoft "help" on the subject.

Hi JR

Can the external data not be pre-processed to deal with the problems
before
importing it into your main table(s)?

It seems to me a much better idea not to import the duplicates in the
first
place.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I get large amounts of data that have to be processed from sources
external
to my organization.
Often the data is composed of stock numbers and how many orders per
unit
of
time were made against the stock numbers. So the stock numbers show up
time
after time.
At the same time information about physical characteristics of the
items
the
stock numbers represent is maintained in a separate table because not
everyone enters correct basic data (such as weight and cube) so the
"official" weight and cube has to be maintained.
Because other data (not stock numbers) in the same record as the stock
numbers are not consistant you can wind up with the same stock number
showing
up more than once due to the differint column data. This is usually
the
cause of duplicate stock numbers.
It goes on and on ... Fact is that duplicate stock numbers show up
consistantly.

:

If you'll describe the situation which causes the need to "do this
time
and
again", folks here may be able to offer suggestions that could reduce
or
eliminate that need.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Looking for a query that will eliminate duplicates from a SINGLE
table.
Will
have to do this time and again so manually eliminating from a find
duplicates
query, while workable, is not a good solution.

Anyone know how to (the help from Microsoft on this is 18 pages long
and
makes my head hurt before the bottom of the first page).

Thanks!

 

[Fred]

Hello JR,

From your posts (and what's missing in them) I suspect that getting deeper
into table structure and "what is a record?" type questions would be useful
and provide a better solution.


But another idea would be a grouping "make table" query, and overwirte the
old table with the recently made new one. There wills be some details
dealing wit the things you didn't tell us about, like, are you defining a
duplicate record as one where just the contents of one field (e.g. stock
number) and if so, and, assuming that the table has more than one field, you
will presumably be deleting the unique data that is in the other fields?
And, if so, which of the sets of unique data from those other fields will be
kept vs. deleted.

 

[John W. Vinson]

I would still need the query.

A little more: I get reports of daily activity once a week. stock numbers
may be ordered more than once a day or by more than one customer. By the end
of the week there are multiple occurances of the stock numbers. Data come in
in excel, get imported to access. Individual orders can not be deleted just
because the stock number shows up more than once.

This seems about as hopeless as the Microsoft "help" on the subject.

It would seem that some of us may be confused by your description. Duplicates
are normal and expected on the "many" side of a one to many relationship -
each stock number may have multiple orders, right? What data do you want to
DELETE from your tables because of duplication? Not orders, as you clearly
understand!

So... if you don't want to delete duplicate order records, what DO you want to
delete?

 

[Graham Mandeno]

Hi JR

Let's assume you have the following:

1. Stock items, uniquely identifiable by stock number

2. Customers, uniquely identifiable by customer number

3. Orders, uniquely identifiable by order number

Each of these entities must be represented by a separate table in your
database, each with its respective unique number as a primary key. As each
customer may place multiple orders, you will need a one-to-many relationship
between Customers and Orders.

Assuming an order may comprise several items, you must also have a fourth
table for "OrderItems". This table will have fields for order number, stock
number, unit price, quantity, discount, etc.

How you handle the import of external data depends very much on what is
contained in your Excel files, but one thing is clear: your import procedure
must not create duplicates. In any of your three base tables.

The first step should be to check the import data to ensure that there are
no stock numbers which do not already exist in your table. If any are found
then an alert should be raised, and the problem rectified before the process
can continue. Presumably you can't sell something you don't know about!

The next step should be to scan the data for new customers which do not
already exist in the Customers table, and add them to that table.

Finally, you need to add new Orders and their related OrderItems.

I'm sorry if I'm suffering from a huge misunderstanding here, but I can't
see how you should be creating and duplicates that need to be deleted.

Please can you post back with information on (a) the basic structure of your
tables and relationships and (b) the columns in your import data.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I would still need the query.

A little more: I get reports of daily activity once a week. stock
numbers
may be ordered more than once a day or by more than one customer. By the
end
of the week there are multiple occurances of the stock numbers. Data come
in
in excel, get imported to access. Individual orders can not be deleted
just
because the stock number shows up more than once.

This seems about as hopeless as the Microsoft "help" on the subject.

Hi JR

Can the external data not be pre-processed to deal with the problems
before
importing it into your main table(s)?

It seems to me a much better idea not to import the duplicates in the
first
place.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I get large amounts of data that have to be processed from sources
external
to my organization.
Often the data is composed of stock numbers and how many orders per
unit
of
time were made against the stock numbers. So the stock numbers show up
time
after time.
At the same time information about physical characteristics of the
items
the
stock numbers represent is maintained in a separate table because not
everyone enters correct basic data (such as weight and cube) so the
"official" weight and cube has to be maintained.
Because other data (not stock numbers) in the same record as the stock
numbers are not consistant you can wind up with the same stock number
showing
up more than once due to the differint column data. This is usually
the
cause of duplicate stock numbers.
It goes on and on ... Fact is that duplicate stock numbers show up
consistantly.

:

If you'll describe the situation which causes the need to "do this
time
and
again", folks here may be able to offer suggestions that could reduce
or
eliminate that need.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Looking for a query that will eliminate duplicates from a SINGLE
table.
Will
have to do this time and again so manually eliminating from a find
duplicates
query, while workable, is not a good solution.

Anyone know how to (the help from Microsoft on this is 18 pages long
and
makes my head hurt before the bottom of the first page).

Thanks!

 

[amrtarek]

"Graham Mandeno" < wrote in message

Hi JR

Let's assume you have the following:

1. Stock items, uniquely identifiable by stock number

2. Customers, uniquely identifiable by customer number

3. Orders, uniquely identifiable by order number

Each of these entities must be represented by a separate table in your
database, each with its respective unique number as a primary key. As
each customer may place multiple orders, you will need a one-to-many
relationship between Customers and Orders.

Assuming an order may comprise several items, you must also have a fourth
table for "OrderItems". This table will have fields for order number,
stock number, unit price, quantity, discount, etc.

How you handle the import of external data depends very much on what is
contained in your Excel files, but one thing is clear: your import
procedure must not create duplicates. In any of your three base tables.

The first step should be to check the import data to ensure that there are
no stock numbers which do not already exist in your table. If any are
found then an alert should be raised, and the problem rectified before the
process can continue. Presumably you can't sell something you don't know
about!

The next step should be to scan the data for new customers which do not
already exist in the Customers table, and add them to that table.

Finally, you need to add new Orders and their related OrderItems.

I'm sorry if I'm suffering from a huge misunderstanding here, but I can't
see how you should be creating and duplicates that need to be deleted.

Please can you post back with information on (a) the basic structure of
your tables and relationships and (b) the columns in your import data.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I would still need the query.

A little more: I get reports of daily activity once a week. stock
numbers
may be ordered more than once a day or by more than one customer. By the
end
of the week there are multiple occurances of the stock numbers. Data
come in
in excel, get imported to access. Individual orders can not be deleted
just
because the stock number shows up more than once.

This seems about as hopeless as the Microsoft "help" on the subject.

Hi JR

Can the external data not be pre-processed to deal with the problems
before
importing it into your main table(s)?

It seems to me a much better idea not to import the duplicates in the
first
place.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


I get large amounts of data that have to be processed from sources
external
to my organization.
Often the data is composed of stock numbers and how many orders per
unit
of
time were made against the stock numbers. So the stock numbers show
up
time
after time.
At the same time information about physical characteristics of the
items
the
stock numbers represent is maintained in a separate table because not
everyone enters correct basic data (such as weight and cube) so the
"official" weight and cube has to be maintained.
Because other data (not stock numbers) in the same record as the stock
numbers are not consistant you can wind up with the same stock number
showing
up more than once due to the differint column data. This is usually
the
cause of duplicate stock numbers.
It goes on and on ... Fact is that duplicate stock numbers show up
consistantly.

:

If you'll describe the situation which causes the need to "do this
time
and
again", folks here may be able to offer suggestions that could reduce
or
eliminate that need.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Looking for a query that will eliminate duplicates from a SINGLE
table.
Will
have to do this time and again so manually eliminating from a find
duplicates
query, while workable, is not a good solution.

Anyone know how to (the help from Microsoft on this is 18 pages
long
and
makes my head hurt before the bottom of the first page).

Thanks!

 

[amrtarek]

Hi JR

Let's assume you have the following:

1. Stock items, uniquely identifiable by stock number

2. Customers, uniquely identifiable by customer number

3. Orders, uniquely identifiable by order number

Each of these entities must be represented by a separate table in your
database, each with its respective unique number as a primary key. As
each customer may place multiple orders, you will need a one-to-many
relationship between Customers and Orders.

Assuming an order may comprise several items, you must also have a fourth
table for "OrderItems". This table will have fields for order number,
stock number, unit price, quantity, discount, etc.

How you handle the import of external data depends very much on what is
contained in your Excel files, but one thing is clear: your import
procedure must not create duplicates. In any of your three base tables.

The first step should be to check the import data to ensure that there are
no stock numbers which do not already exist in your table. If any are
found then an alert should be raised, and the problem rectified before the
process can continue. Presumably you can't sell something you don't know
about!

The next step should be to scan the data for new customers which do not
already exist in the Customers table, and add them to that table.

Finally, you need to add new Orders and their related OrderItems.

I'm sorry if I'm suffering from a huge misunderstanding here, but I can't
see how you should be creating and duplicates that need to be deleted.

Please can you post back with information on (a) the basic structure of
your tables and relationships and (b) the columns in your import data.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I would still need the query.

A little more: I get reports of daily activity once a week. stock
numbers
may be ordered more than once a day or by more than one customer. By the
end
of the week there are multiple occurances of the stock numbers. Data
come in
in excel, get imported to access. Individual orders can not be deleted
just
because the stock number shows up more than once.

This seems about as hopeless as the Microsoft "help" on the subject.

Hi JR

Can the external data not be pre-processed to deal with the problems
before
importing it into your main table(s)?

It seems to me a much better idea not to import the duplicates in the
first
place.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


I get large amounts of data that have to be processed from sources
external
to my organization.
Often the data is composed of stock numbers and how many orders per
unit
of
time were made against the stock numbers. So the stock numbers show
up
time
after time.
At the same time information about physical characteristics of the
items
the
stock numbers represent is maintained in a separate table because not
everyone enters correct basic data (such as weight and cube) so the
"official" weight and cube has to be maintained.
Because other data (not stock numbers) in the same record as the stock
numbers are not consistant you can wind up with the same stock number
showing
up more than once due to the differint column data. This is usually
the
cause of duplicate stock numbers.
It goes on and on ... Fact is that duplicate stock numbers show up
consistantly.

:

If you'll describe the situation which causes the need to "do this
time
and
again", folks here may be able to offer suggestions that could reduce
or
eliminate that need.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Looking for a query that will eliminate duplicates from a SINGLE
table.
Will
have to do this time and again so manually eliminating from a find
duplicates
query, while workable, is not a good solution.

Anyone know how to (the help from Microsoft on this is 18 pages
long
and
makes my head hurt before the bottom of the first page).

Thanks!