Cross Reference Tables in Access

[jmorris]

Hello,
I will have two master tables A-L and M-Z which contain ticket holders.
I will have several tables which list employees of different employers.

I need to run a query on each employers table to find employees that are also in the ticketholders tables.

I started with this SQL statement but got a long list of duplicates:

SELECT Rewards.[Last Name], Rewards.[First Name], Rewards.Middle, Rewards.Address, Rewards.Address2, Rewards.City, Rewards.State, Rewards.Zip, Rewards.Phone, Rewards.SSN, Rewards.DOB
FROM [M-Z] LEFT JOIN Rewards ON [M-Z].[SSN]=Rewards.[SSN]
WHERE (((Rewards.[Last Name]) Is Null)); 

So for each employer I would run a query on A-L and another on M-Z to see if any of their employees are listed on either of the ticket holders tables.

I am new to Access and still learning so any help at all in layman's terms would be greatly appreciated.

[Jim Doherty]

@jmorris
Welcome to Bytes :)

I see you are new :) Friendly word of advice - get to use a standard naming convention for your table and field names. Research Lechinsky/Roddick naming convention if you intend to use Access a lot, Spaces in field names generally are a pain in the proverbial, as you will doubtless learn as you progress.

Without being too verbose with this as I think I understand your need, take a look your syntax predicate at the SELECT portion of your query syntax

if you use SELECT DISTINCT it will return unique values in your resultset
If you use SELECT DISTINCTROW it will return unique records eliminating the duplication you speak about.

These settings incidentally can be got at in the query design window of access using the menubar VIEW - PROPERTIES which pops up the properties window

You might wonder the differences but it is documented in the help files in Access so I wont repeat that

Regards

Jim

[NeoPa]

May I ask why the [M-Z] table is included in the SQL at all, as the data and all references appear to be from [Rewards]?

Excluding this gives you the full set only once of course if my guess as to your structure is accurate.

[jmorris]

@NeoPa
Well, what I want to do is see if people in the Rewards table are in the M-Z table.

[jmorris]

@Jim Doherty
if you use SELECT DISTINCT it will return unique values in your resultset
If you use SELECT DISTINCTROW it will return unique records eliminating the duplication you speak about.

Both worked fine thank you so much. I know I have a long way to go and I'll rely on these forums for information.

[jmorris]

@NeoPa
I think what your saying is change

FROM [M-Z]

TO

FROM Rewards

[jmorris]

Thanks to both of you very much!!!!

[NeoPa]

I think what you're saying is ...

Maybe I'm confused as to what you're actually asking for help with. I assumed from your first post that it was to include the [A-L] data in with the [M-Z] data that you already had. My suggestion, to remove the [M-Z] part rather than changing it, as [Rewards] is already included in the FROM clause, would satisfy that requirement.

Rereading, that doesn't seem to be the question, but I can't really understand why that was introduced into the post at all if, as seems to be the case, you're asking specifically about removing duplicates.

As for the duplicates, I think Jim has given some good advice on that one. However, I can't help thinking that there may be a more fundamental issue here with your sructure, or maybe even just the data itself. I suspect the duplicates occur because there are multiple [M-Z] records for each SSN. Without access to your data this is hard to know. It may help to have a look through SQL JOINs to get a fuller understanding of what actually happens when JOINing tables together within a query.

[NeoPa]

jmorris: Thanks to both of you very much!!!!

You're very welcome.

We'll be happy to see more of your questions :)