Audit Trail using Queries

[Bombshell Disaster]

Hi - This is what I am trying to do, any help greatly appreciated...
i.e.
Customer Table
Customer History Table
Import Table

1 Insert New Cust from Import Table into Cust table - Yes can do
2 Insert New Cust History from Import Table into Cust History Table - Yes can do
3 Update changes in customer table from Import table - yes can do

Now comes the tricky part

4 Next step is to insert a record into the customer history table where customer changes are happening - yes can do for the first time

HOWEVER this will keep updating same changes!!! If I then run the query again - see below

I want to take the SQL into vb and put all together and click button to run import as the import table will come from another database every day

Once I have run step 4 i don't want it to update the customer history table again next time that I run above said button...

There are other issues but this is my first stuck step and having been banging my head for two days...

Can upload a sample test of required??

Thanks anyone that is listening?

 

[Rabbie]

What you would need is to set a timestamp when you update the customer history so you can check when you last updated this for a particular customer and then decide to skip the update if required.

 

[Bombshell Disaster]

Jet!!!

Hi Rabbie

Thanks for your reply.

No updates will be done in the forms only when new records or changes to existing records have been udpated and then imported back into Access.

I now realise that Jet will not allow for capture udpates in the table level but only the form.

And as I am using just tables for daily import procedure it is back to the drawing board for me....

Thanks again

 

[Bombshell Disaster]

Jet

How would I skip record updates if the data had not changed? There are already action fields set up ie. ActionDate

Cheers

 

[Rabbie]

The easiest way would be to use some VBA code to do this. I can help if you are not confident about doing it yourself.

 

[Bombshell Disaster]

Hi Rabbie

That would be great if you could help. I have put together the code to run the SQL action queries
i.e.

Insert New Cust - Cust Table
Insert Cust Hist - Cust History Table
Insert Cust Updates - Cust Tables

Now for the problem, because I am doing this in tables only and the data will get imported everyday. An insert will just keep inserting the same records with no changes.

Insert Cust Hist Updates - Cust History Table

If you could help me to write the code for skipping that last part if the records have not changed I will be eternally grateful...

Thanks

 

[Bombshell Disaster]

Sample of data

Hi Rabbie

Give me a shout if you need any clarification

Cheers

 

[Rabbie]

Hi

Here is new code for your import button which stops the code being run more than once a day

Public Sub Import()

Dim InsertCust As String
Dim InsertCustHist As String
Dim UpdateCust As String
Dim UpdateCustHist As String
'Test if imports already applies today
If DMax("Actiondate", "tblCustomerHistory") = Date Then
MsgBox ("Imports already applied today")
Return
End If
'Insert new customers

InsertCust = "INSERT INTO tblCustomers ( NavAccNo, CustomerName, ContactName ) " & _
"SELECT tblImport.NavAccNo, tblImport.CustomerName, tblImport.ContactName  " & _
"FROM tblCustomers RIGHT JOIN tblImport ON tblCustomers.NavAccNo = tblImport.NavAccNo " & _
"WHERE (((tblCustomers.NavAccNo) Is Null))"

DoCmd.RunSQL InsertCust

'Insert customer history

InsertCustHist = "INSERT INTO tblCustomerHistory ( [Action], ActionBy, ActionDate, NavAccNo, CustomerName, ContactName ) " & _
"SELECT 'Insert' AS [Action], CurrentUser() AS ActionBy, Date() AS ActionDate, tblCustomers.NavAccNo, tblCustomers.CustomerName, tblCustomers.ContactName " & _
"FROM tblCustomers LEFT JOIN tblCustomerHistory ON tblCustomers.NavAccNo = tblCustomerHistory.NavAccNo " & _
"WHERE (((tblCustomerHistory.NavAccNo) Is Null)) " & _
"ORDER BY tblCustomers.NavAccNo"

DoCmd.RunSQL InsertCustHist

'Update customers

UpdateCust = "UPDATE tblCustomers INNER JOIN tblImport ON tblCustomers.NavAccNo = tblImport.NavAccNo SET tblCustomers.NavAccNo = tblImport!NavAccNo, tblCustomers.CustomerName = tblImport!CustomerName, tblCustomers.ContactName = tblImport!ContactName "

DoCmd.RunSQL UpdateCust

'Update customer history

UpdateCustHist = "INSERT INTO tblCustomerHistory ( [Action], ActionBy, ActionDate, NavAccNo, CustomerName, ContactName ) " & _
"SELECT 'Update' AS [Action], CurrentUser() AS ActionBy, Date() AS ActionDate, tblCustomers.NavAccNo, tblCustomers.CustomerName, tblCustomers.ContactName " & _
"FROM tblCustomers INNER JOIN tblCustomerHistory ON tblCustomers.NavAccNo = tblCustomerHistory.NavAccNo " & _
"WHERE ((([tblCustomers]![CustomerName])<>[tblCustomerHistory]![CustomerName])) OR ((([tblCustomers]![ContactName])<>[tblCustomerHistory]![ContactName]))"

DoCmd.RunSQL UpdateCustHist

End Sub

 

[Bombshell Disaster]

Hi Rabbie

Thanks for your input of Dmax function for my code, but that was not what I was really looking for.

The text file imported everyday will still have the same records with no changes in the text file. I only want to insert an update record into the customer history table if there have been changes. But it inserts everything i.e. when I run an insert into the customer table to show 'updates' do not insert duplicated records.

Dmax will stop it being imported more than twice a day, but I need the code as explained above....

Thanks, hoping you can help. Not sure if this is possible??

 

[Rabbie]

Beginning to get a clearer picture of what you want. the quick answer is that it is possible. However if you are apply all changes every day then the execution time will increase considerably as time goes on. This will occur even if we don't apply the changes because we will have to check if each entry in the import table has already been applied or not.

You can simplify this if you look at your design and don't have a bigger import file each day. Ie only import the new changes each day. Maybe I still don't get what your are trying to acheive so If I am wrong please let me know what is really wanted

 

[Bombshell Disaster]

Ok Rabbie

Here it goes in a nutshell. How can I skip importing duplicated records into a history table. The duplication will not be in the PK but in all the fields in the table. ie check that no records are exactly the same in all fields....

Cheers my dears...