Can I find duplicate records in non-relational tables?

[prismlight]

Im creating a database that contains various tables (each table contains
mailing lists that need to stay seperate). Although the tables are
non-relational, we are using Access because it is so powerful.

Is there a way I can locate duplicate records within multiple tables? I have
10 tables that are non relational. While entering data I have noticed that
various mailing lists contain duplicates and I want an easy way to locate
(query?) the duplicates. It appears I can only do this type of search for
each table not for the entire databse????

any help would be appreciated.
thanks

 

[KARL DEWEY]

You can comapre two tables at a time but with addresses there is a problem in
that people use Street, ST, St., BLVD, etc.

What you might do is build one table with all and add fields to indicate
which table the record came from. Add a check field, sort A-Z, and scroll
manually. As you scroll decide if it is a duplicate and put check/"X" to
mark it.

 

[prismlight]

Hi thanks
How can I compare the two tables?

Also, would creating an Inner join work?

Also, Im trying to do a query in design view using a criteria to mean ONLY
DUPLICATES?
I read somewhere that it was "="

any ideas?

 

[KARL DEWEY]

Try using the wizard for Find Duplicates. You do not need a criteria as the
join will only list those records that are duplicate based on the fields that
you join.

 

[Craig Alexander Morrison]

There is no NEED to stay in seperate tables indeed there is little point
using Access if that is what you are doing.

You can add either a field (if a given address can only be in one list) or a
resolved Many to Many relationship to denote which lists the address
belongs to.

This means that each address appears once in the table and the related table
indicates which lists it belongs to.

You would have one table of ADDRESSES (this may or may not actually be one
table depending upon the content of your current tables)

One table of MAILING LISTS

And one table MAILING LIST ADDRESSES

The relationships are:
ADDRESSES 1 to Many with MAILING LIST ADDRESSES
MAILING LISTS to Many with MAILING LIST ADDRESSES

Using this approach an Address will appear once in the database but could
appear in more than one mailing list if that is what is required.

If you use the new field in the table to indicate that an address can only
be in one mailing list that field can be queried to only return the
addresses for a required mailing list.

The design solution changes depending upon your requirements as you can see.
The design, however, must make some attempt to be relational if you are to
harness the power of Access.

You should always uses Forms and Reports to input and output your data and
use Queries to provide them with the data they need.

Although the tables are non-relational, we are using Access because it is
so powerful. <

The design is non-relational, the tables may or may not qualify as
relations, and using Access this way cripples your possibilities so much
that if you persist in this approach you may be better off using Excel. You
could investigate the use of a Union Query but I would not recommend it.

 

[prismlight]

hi and thanks

I tried using the Wizard for FIND Duplicates but it appears to only allow me
to pick one table????? Is there a way to select more then one table within
that wizard?

thanks

 

[prismlight]

I have to stay in sperate tables because they each come from various sources.
I only want to find duplicates this ONE time and just am trying to locate
them to print out a list for my supervisor. What I want to do is simple:
seach for potential duplicates within many tables????
help?
thanks

 

[KARL DEWEY]

No, not with the wizard. You can pick more tables in Design view but you
will wind up with the lowest common denominator - addresses that appear in
all tables picked.

 

[prismlight]

thanks
so so far im creating a query in design view...
im using inner joins between a field (such as last name or address) to find
duplicates...is there a easy way to do an inner join because using the drag
is becoming a drag. lol
thanks

 

[Craig Alexander Morrison]

Do what has already been suggested put/copy them all into one table, add a
marker field to indicate source find the duplicates (this will in all
likelihood be manual; the same names and addresses may not be identically
recorded in a computer).

Once duplicates are identified resort to your bucket of tables to do
whatever update you are considering. If just need list then query the single
table above.

 

[prismlight]

I will try what you suggest.

So far Access has been so quick and easy.
i thought there would be an easier way to search within multiple tables at
once for duplicates. For example the Find Duplicate Wizard would be IDEAL if
it allowed me to pick from more then one table. Its so frustrating that I can
see all the tables from the pull down menu list but it wont allow me to chose
but only one table. *sigh*
thanks for all your help