compare two tables 2

[mmaginniss]

I have two tables - one for the month of June and one for the month of May - and each table has the exact same data fields (and very similar data). I am trying to determine what records have changed from May to Jun using a query. Here is an example of the two tables:

Table May Table June
1 1
2 2
3 3
4 5

I would like my query to compare the two tables and return the fourth record in table June - 5. I have been working on this for a while and haven't been able to come up with any ideas. Any help would be appreciated.

 

[Crowley16]

ok, assuming that the 2 fields that you're comapring is storing numbers...

select tblMay - tblJune
from tblMay Join tblJune on keyfield = keyfield
where tblMay - tblJune <> 0

of course you'll need to change it a bit...

Crowley - as in like a Crow

 

[dhookom]

Try:
SELECT June.FieldA
FROM June
WHERE FieldA Not In (SELECT FieldA FROM May);

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[CosmoKramer]

Or, you could use the Find Unmatched Query Wizard which would create something like this:

SELECT DISTINCTROW [June].[FieldA]
FROM June LEFT JOIN May ON [June].[FieldA] = [May].[FieldA]
WHERE ([May].[FieldA] Is Null);

Hoc nomen meum verum non est.

 

[Crowley16]

I'd use cosmoKramer's suggestion myself

Crowley - as in like a Crow

 

[mmaginniss]

thx

 

[dhookom]

Cosmo's solution will run more efficiently especially if you have more than 5 records ;-)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[SalGal]

I'm bumping this thread - working on a similar situation, and the suggested fixes aren't working for me.

I have a SQL table that has exhibitor information (cust_id, SIZE, and some other information); I need to UPDATE the size field from another table (tbl04) ONLY if the SIZE field changes.

Suggestions? Or am I just better off updating all the fields that have a matching record, and not worry about trying to ferret out only those that have changed?

Thanks for any suggestions, comments, etc.

 

[dhookom]

Are you attempting to update or append? Could you provide some sample before and after records? Do you have primary keys defined in both tables to create a join?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[SalGal]

I'm attempting to update. For example, here's what the SQL table looks like

CUST_ID COMPANY YEAR SIZE SPONSOR
T1001 Sal's Garage 2004 200 5000
T1002 B&B Track 2004 100 0

Say Sal's Garage wants to upgrade to 500 sq. ft. of exhibit space; in the legacy system, the 200 is changed to 500. I need to identify that the record in the legacy system is now different from the SQL table, then update only the SIZE field in the SQL table.

Both tables have CUST_ID as the primary key.

I'm thinking I should just update everyone's size, whether it has changed or not.

Hope this helps. Thanks!

 

[dhookom]

I would create an update query that would have a criteria under the SQL.SIZE of <> Legacy.Size and update the SQL.Size to the Legacy.Size.

This would only affect the records where they are different.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jojones]

I have two tables which I want to compare to see where data has changed, but it could have changed in any one of a number of fields, except the ID (perhaps 20 fields). It is a check to make sure historical data has not changed. There is one Float field and the rest are Ints or VarChars.

What is the best way to compare these tables and return a list of ID numbers that have changed? It is not necessary to know what the change is although it might be handy in the future...

Thanks
Jo

 

[dhookom]

You can set up a query like the following:
[blue][tt]
+----------+ +----------+
|Table1 | |Table2 |
+----------+ +----------+
|ID (PK) |--|ID (PK) |
|FieldA | |FieldA |
|FieldB | |FieldB |
|FieldC | |FieldC |
|etc | |etc |
+----------+ +----------+
============================================================
+--------+----------------+----------------+----------------
| Field|FieldA |FieldB |FieldC
+--------+----------------+----------------+----------------
| Table|Table1 |Table1 |Table1
+--------+----------------+----------------+----------------
|Criteria|<>TableB.FieldA | |
| or| |<>TableB.FieldB |
| or| | |<>TableB.FieldC
[/tt][/blue]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.