compare 2 tables

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

Guest

Hi!

I have 2 tables:

TABLE1
apple
ball
dog

TABLE2
apple
ball
cat
dog
egg

How do I compare TABLE1 and TABLE2, then store the difference (in this case,
cat and egg) into TABLE3?

Thanks!
 
Well there's probably a slicker way to do this, but this does work. In the
first query combine the two item lists into one union. Then call the first
query with the second, grouping items and counting them, with only counts of
1 surviving ... then sending the results to Table1 (make-table).

Query1:

SELECT Table1.Item
FROM Table1
UNION ALL
Select Table2.Item
FROM Table2

Query2:

SELECT Query1.Item INTO [Table3]
FROM Query1
GROUP BY Query1.Item
HAVING Count(Query3.Item)=1

Bound to be someone out there with a more elegant solution, but you can use
this until it appears.

HTH
 
Oops, the narrative should say that the results are sent to Table3. The code
says that already.
 
How do I run this SQL query?

I'm using DoCmd.RunSQL "DoCmd.RunSQL "SELECT Table1.F1 FROM Table1"

....and the following error occurs:
"A RunSQL action requires an argument consisting of an SQL statement"


Joe D said:
Well there's probably a slicker way to do this, but this does work. In the
first query combine the two item lists into one union. Then call the first
query with the second, grouping items and counting them, with only counts of
1 surviving ... then sending the results to Table1 (make-table).

Query1:

SELECT Table1.Item
FROM Table1
UNION ALL
Select Table2.Item
FROM Table2

Query2:

SELECT Query1.Item INTO [Table3]
FROM Query1
GROUP BY Query1.Item
HAVING Count(Query3.Item)=1

Bound to be someone out there with a more elegant solution, but you can use
this until it appears.

HTH


Anakin Moonwalker said:
Hi!

I have 2 tables:

TABLE1
apple
ball
dog

TABLE2
apple
ball
cat
dog
egg

How do I compare TABLE1 and TABLE2, then store the difference (in this case,
cat and egg) into TABLE3?

Thanks!
Click to expand...
Click to expand...
 
Actually, you can just create a couple of new queries, put them in SQL mode
(see View on the menu) and drop in the code - with your table and field names
- and save them with the appropriate query names. Then run the second query
(it will run the first). That you can do with a DoCmd.OpenQuery statement in
VBA if you like or from a macro or whatever you are comfortable with.

Anakin Moonwalker said:
How do I run this SQL query?

I'm using DoCmd.RunSQL "DoCmd.RunSQL "SELECT Table1.F1 FROM Table1"

...and the following error occurs:
"A RunSQL action requires an argument consisting of an SQL statement"


Joe D said:
Well there's probably a slicker way to do this, but this does work. In the
first query combine the two item lists into one union. Then call the first
query with the second, grouping items and counting them, with only counts of
1 surviving ... then sending the results to Table1 (make-table).

Query1:

SELECT Table1.Item
FROM Table1
UNION ALL
Select Table2.Item
FROM Table2

Query2:

SELECT Query1.Item INTO [Table3]
FROM Query1
GROUP BY Query1.Item
HAVING Count(Query3.Item)=1

Bound to be someone out there with a more elegant solution, but you can use
this until it appears.

HTH


Anakin Moonwalker said:
Hi!

I have 2 tables:

TABLE1
apple
ball
dog

TABLE2
apple
ball
cat
dog
egg

How do I compare TABLE1 and TABLE2, then store the difference (in this case,
cat and egg) into TABLE3?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
You should use an outer join and then a create/add query.

SELECT table2.field2 INTO table3
FROM table1 RIGHT JOIN table2 ON table1.field1 = table2.field2
WHERE (((table1.field1) Is Null));

This assumes you only want the differences from table1 compared to
table2. For the other way around use

SELECT table1.field1 INTO table3
FROM table1 LEFT JOIN table2 ON table1.field1 = table2.field2
WHERE (((table2.field2) Is Null));


I further assumed you wanted to create a new table. Otherwise use an
Add-query instead of Create-table.

Good Luck!
 
Good to see two different approaches for Anakin. From the original post I
had assumed differences in both tables ("How do I compare TABLE1 and TABLE2,
then store the difference") - I saw the "and" instead of a "with". Under my
assumption (which, of course, may be incorrect), if Table1 has A,B and Table2
has A,C, then the both B and C would show up in the difference table. Thus
the UNION and the grouping and count criteria.

We both, I think correctly, assumed the tables were dynamic and that the
difference table should be re-created each time.

Anakin has now to apply the solution that best fits the requirements.

For my own edification, can you see a way to merge a UNION with GROUP BY and
HAVING in the same query instead of a two-step process as I suggested? I
wrestled with that for a while but couldn't find an appropriate syntax. I've
not needed it in the past, but I'm always looking for more approaches for my
Methods file.

Joe
 
Back
Top