Help working w/ Multiple Tables

M

MarkDotCom

I have two tables that I have imported from Excel. What I need to do is cross
reference them for duplicates. I have been trying to do this via linking and
relationships, with no luck.

What am I doing wrong? Where do I start?

Thanks for any help!!

-Mark
 
S

Steve Huff

I can explain it best with an example:

If you have two tables and they have two fields lastname and firstname and
you want to check for duplicates where the lastname and firstname match in
both tables you would create a query and "link" the lastname field from
table1 to table2 and the same for firstname by dragging the fields from the
one table to the other, the default join it creates will work fine for our
proposes. Then drag down the * from table1 to have all the fields from
table1 show and run the query. The results would be only the records that
exist in table1 and table2 with those matching fields.

Hope that example helps...

-Steve Huff
http://www.huffs.us
 
M

Mark

One more question:
While waiting for a reply, I tried creating a query that I thought might
work, and it seemed to; it took a long time, then gave me 190 results.

I then read your response, tried that and it went extremely fast, but only
returned 117 results. Where are the extra 73 coming from?

Now I'm confused...
 
T

Tom Wickerath

Hi Mark,

Create a query that includes both tables. The tables should be joined with inner (equi) joins.
Then add some identifying fields from either table into the QBE grid. As an example, open the
sample Northwind database. Right-click on the Categories table and left-click on copy. Then
left-click in an open area and right-click on paste. Paste the structure and data into a new
table named Table1. Repeat this procedure to create Table2.

Now, delete the last three records from Table 1 and the first three records from Table 2. There
will still be two records that are common to both tables. The following SQL statement will find
these records:

SELECT Table1.CategoryID, Table1.CategoryName, Table1.Description
FROM Table1
INNER JOIN
Table2 ON Table1.CategoryID = Table2.CategoryID;


Tom
________________________________


I have two tables that I have imported from Excel. What I need to do is cross
reference them for duplicates. I have been trying to do this via linking and
relationships, with no luck.

What am I doing wrong? Where do I start?

Thanks for any help!!

-Mark
 
T

Tom Wickerath

Hi Mark,

That's a pretty tough question to answer, when you haven't included the SQL statements for the
two queries in question. Open each query in design view. Click on View > SQL View. Copy the SQL
statement for each query and paste it into a reply. Also, open each table and let us know how
many records are in each table.

Tom
_________________________________


One more question:
While waiting for a reply, I tried creating a query that I thought might
work, and it seemed to; it took a long time, then gave me 190 results.

I then read your response, tried that and it went extremely fast, but only
returned 117 results. Where are the extra 73 coming from?

Now I'm confused...

_________________________________
 
Top