ACCESS FEED: RE: [MS_AccessPros] Comparing two tables in Access

Rabu, 13 Maret 2013

RE: [MS_AccessPros] Comparing two tables in Access

Sally-

If you want to update ONLY the changed fields, you would need to run "about
a dozen" individual queries, like this:

UPDATE ProductionTable INNER JOIN UpdateTable

ON (ProductionTable.PKey = UpdateTable.PKey)

SET ProductionTable.Region = UpdateTable.Region

WHERE ProductionTable.Region <> UpdateTable.Region

But you can really do it in one pass, like this:

UPDATE ProductionTable INNER JOIN UpdateTable

ON (ProductionTable.PKey = UpdateTable.PKey)

SET ProductionTable.Region = UpdateTable.Region, ProductionTable.Quantity =
UpdateTable.Quantity, .

WHERE (ProductionTable.Region <> UpdateTable.Region) OR

(ProductionTable.Quantity <> UpdateTable.Quantity) OR .

In other words, the predicate you were attempting should work, but may need
parens for clarity.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Sally Vega
Sent: Wednesday, March 13, 2013 10:55 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Comparing two tables in Access

Hi All,

There is probably an easy answer to this but I haven't found it.

I am comparing two tables. One table is the Production table and the other
is the Update table. I have fields that are being compared and if there is a
change from what is in the Production (LIVE) table and what is in the Update
table then I want to change only the field that changed.

There are about a dozen fields that need to be compared and updated if
changed.

I was using UpdateTable.Region <> ProductionTable.Region or
UpdateTable.Quantity <> ProductionTable.Quantity, etc but this isn't
working.

I can see that there are difference fields but this logic isn't picking it
up. My question is why doesn't this work and what kind of query logic do I
need to use to be able to filter out the differences?

Thank you,

Sally Vega, MBA
Supply Chain Data Analyst
Super First Class Products
B/E Aerospace, Inc.
1851 S. Pantano Rd.
Tucson, AZ 85710 USA
(520) 239-4812

This email (and all attachments) is for the sole use of the intended
recipient(s) and may contain privileged and/or proprietary information. Any
unauthorized review, use, disclosure or distribution is prohibited. If you
are not the intended recipient, please contact the sender by reply e-mail
and destroy all copies of the original message.

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]