Show Non-Duplicate Records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables - both contain information about people, with initials,
surname, address etc.

First - I want to set up a query to show ALL the people who are on the
second table who do NOT also appear on the first table (with all the other
fields shown if poss).

Second - I want to set up a query to show ALL the people who are on the
second table who DO also appear on the first table (with all the other fields
shown if poss).

(A number of people share the same surname, but have different initials.)

I have no idea how to start going about this - I assume I will need to
create a relationship between the two tables but I'm not really sure what I
need to do!

Any help would be very much appreciated!

Jen
 
Jen,
Let's say, for example, that both tables are related via a key field
called CustID...

1. Create a query with 2 tables... Table1 and Table2. Link from Table2
to Table1, using "Show All in Table2 and Only Those in Table1 that Match"
realtionship.
Place the CustID from Table2 and Table1 on the design grid, in that
order, and run the query. You'll see that records in 2 that don't exist in
1 will show a Null in the Table1 CustID column. So... a Table1 CustID
criteria of IsNull will yield all the 2's not in 1.
Add fields to the grid to suit your needs...

2. Same setup, but this time... relate Table 1 to Table2. Then you'll
see all records in 1, and all recs in 2 that match.

hth
Al Camp
 
Thanks Al, but unfortunately I have no idea how to do any of this?!!

I have created a CustID field in each table - but do I put any data in it or
not?

I have created a relationship between the two tables via this CustID field
as you said, but have no idea what to do next.

I don't know what the "design grid" is or where to find it!

Sorry, this is all new to me!!
 
Jen.
The CustID field is just an example of a key field I used in my response.
You didn't specify the name of a key field of your own, (a key field that
relates the data in Table1 to the data in Table2) so I used CustID as an
"example."

If you've never designed a query before, I'm afraid you'll have to do
some studying up on your own before we can help you. It isn't possible, or
desirable... in the context of a "text" based newsgroup... to teach someone
how to create and work with queries... from scratch... it's just too "broad"
a topic.

See Queries/Creating in Access Help, or even better... get yourself an
Access How To book, and read up on how to create a query, add/link tables in
a query, and how to use query criteria. For what you need now, as a newbie,
any Access book will do.
If you have more "specific" questions during this learning process,
please post those questions, and folks will be glad to help.
I'll bet that once you've learned how to work with "basic" queries...
you'll be able to answer your original question yourself!
hth
Al Camp
 
Jen,

Do you understand what was meant by the CustID field? Imagine cell phone
numbers, mine is unique to me and yours to you, just as my address is unique
to my house, and yours to your house. What he is saying is that you need to
create a unique identity key for each of the people in your database. After
that the rest is really very simple. You wuld creat a query using the
identity key as the relationship.

Now, it is possible to link your two tables on a combination of First name
AND Last name, if you have them in separate fields. Then you specify that
they have to match exactly by defining the relationship. When you do this
query, it will give all the names that are exactly the same in your two
tables. However, lost will be the names that don't match exactly, such as
Bill Thompson and William Thompson. To look at this you could run the query
showing both the first and last names from each table side by side, then
manually compare them to each other. If you know that Bill and William are
the same, then make the correction. You might consider doing this before you
create your ID field.

Then once you have the names straightened out, sort names alphabetically if
desired, create a new table with the ID field, and the fields from the other
tables, and append your query to the new table. If your ID field is set to
autonumber, you will automatically get ID numbers.

As previously mentioned, a good book will also be helpful or even the
Northwinds tutorial that goes with Access.
 
Back
Top