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

Kamis, 14 Maret 2013

RE: [MS_AccessPros] Comparing two tables in Access

So this will set all fields to the new values if any of them are different? Would this be what you would want Sally if I am understanding this correctly?

Respectfully,
Liz Ravenwood
Programmer/Analyst
Super First Class Products
B/E Aerospace
O: 1.520.239.4808
www.beaerospace.com

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Wednesday, March 13, 2013 10:48 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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]

------------------------------------

Yahoo! Groups Links

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.