Use an Append Query to update a Table? 1

[Valeriya]

Hey, Guys!
Trying to figure out how to write (maybe an append query)which is going to update my Access_Table if Particular Fields from dbo_Table are not matching with my Access_Table.

I have two tables:
1st table- is dbo_Customer linked to my Acces Database and I don't know who exactly and when makes an updates to this table. I know it happens overnight...

2nd table- is0000 CONS ACCOUNT my own Access Table that partially gets the info from dbo_CustomerTable, and has different field's names for the entity than in dbo_Customer. For example:
[dbo_Customer].[CustName]=[0000 CONS ACCOUNT].[AccountName]
[dbo_Customer].[REP_Name]=[0000 CONS ACCOUNT].[REP] and so on...

1. What kind of tool can I use to track all the changes happened overnight?
Maybe an Append Query:
Append To AccountName: IIf([dbo_Customer].[CustName]<>[0000 CONS ACCOUNT].[AccountName],[dbo_Customer].[CustName])?

2. Do I really have to specify each field that should be matching ( maybe there is another more effective way to do this?)

3. How can I produce any kind of output saying those particular changes were apllied (giving some kind of list of when and what exactly have been changed, like a macro that would automatically sends the e-mail to certain people, saying that Account Manager has been changed from ...to...).)?

Thank you in advance,
Valeriya

 

[rac2]

Do I really have to specify each field that should be matching "

It sounds like you are at somewhat of a disadvantage here, needing to know some rather nitty gritty facts about a database which is not under your control. If it were, you could add a TIMESTAMP column if it is a MySQL database. This is a datatype that can be used to keep track of when a row changes. Or in SQL Server you could write a TRIGGER, a stored procedure that runs whenever a row is changed; the procedure could log who and when and what the change was.

Otherwise, I think you will need to compare every value in every row in order to find out what has changed.


" What kind of tool can I use to track all the changes ...Maybe an Append Query"

Yes, an Append Query. You will be in good shape if there is a primary key column in dbo_Customer which you also have in your [0000 CONS ACCOUNT]. You can JOIN these two tables, in fact you must JOIN them in order to compare the right rows.

SELECT *
FROM [0000 CONS ACCOUNT] a
JOIN dbo_Customer b ON b.customer_id = a.customer_id
WHERE a.[AccountName] <> b.[CustName]

A query like this will show the rows where the customer name has changed. Use a logical OR to add a condition comparing each column to the WHERE clause.


A different query will show the new customers added.

SELECT *
FROM dbo_Customer b
LEFT JOIN [0000 CONS ACCOUNT] a ON a.customer_id = b.customer_id
WHERE a.customer_id IS NULL

And those which have been deleted

SELECT *
FROM [0000 CONS ACCOUNT] a
LEFT JOIN dbo_Customer b ON b.customer_id = a.customer_id
WHERE a.customer_id IS NULL

It may be possible to combine these two using a FULL JOIN, not sure about that.


"How can I produce any kind of output ... automatically sends the e-mail "

I have no idea how to do that with Access but it seems like it might be possible.


HTH.

 

[Valeriya]

Thanks Rac2!
I'm going to try it out now and will let you know how it goes.

I really really appreciate all your help!!!

Valeriya.