Query to update a field from a reference table

[PCartland]

I have an Access 2010 dB that has a table that is linked to a SharePoint list. I need to be able to track changes in the fields in the SP list table.


I created a table to store the current values of the list records.
I created another table to store a record of the current and new values of three fields and the date of the change.

I created a query to populate the table of changes

INSERT INTO CRChanges ( CRID, NewCRStatus, [Admin Only],Decision, ChangeDate, OldCRStatus )
SELECT [Change Request Intake].ID, [Change RequestIntake].[Change Request Status], [Change Request Intake].[For Admin Only],[Change Request Intake].Decision, Date() AS Expr1, CurrentStatus.[ChangeRequest Status]
FROM [Change Request Intake] INNER JOIN CurrentStatus ON[Change Request Intake].ID = CurrentStatus.ID
WHERE ((([Change Request Intake].[Change RequestStatus])<>[CurrentStatus]![Change Request Status])) OR ((([Change RequestIntake].[For Admin Only])<>[CurrentStatus]![For Admin Only])) OR((([Change Request Intake].Decision)<>[CurrentStatus]![Decision]));

When I run the query changes to [Change Request Status] are recorded but changes to [For Admin Only] and to [Decision] are not. All three are text fields. I've verified that the values in the [CurrentStatus] table are in fact different. I've tried isolating the three Where conditions into separate queries and the only one that works is the Change Request Status query.

I think it should work as is since the [Change Request Status] comparison works. Is there a function I should be using instead of <> for comparing text values?

Thanks
Paul

[NTC]

Not Like

The concept you are designing is very cumbersome. For all intents and purposes one might as well just write the record to your change log table if the record is Dirty. It is a simpler implementation and serves the same purpose - which I assume is the occasional need to look back and view what the prior record data looked like before a change of some sort.

[PCartland]

Thanks but even if I write the entire record each time there's a change I have to get the query to recognize that there's been a change. The issue is why the <> works on one field but not the others.

[aytee111]

Sometimes you use "!" and sometimes you use "." for field names, don't know if it will make any difference but change them all to "."

There are spaces missing, again not sure of the impact - such as "INNER JOIN CurrentStatus ON[Change Request Intake].ID" and "OR((([Change Request Intake].Decision)" and "[Change RequestIntake].[Change Request Status]"

This was not created by Access, I suggest you recreate the query using Design View.

[PCartland]

Thanks again, NTC. I totally missed your Not Like statement. I tried using it instead of <> with the same result as before.
Thanks Aytee111. The query was built using the build functionality within design view. I'm not sure why some use "!" and other "."

[aytee111]

And the multiple table names? Sorry, Access doesn't make mistakes like that!

[Bulzie]

Are you saying it is not checking if the other 2 fields are different or that it is not saving those original values for Decision and Admin Only? I added those 2 other fOld.. fields to the CRChanges table. Also make sure your names of tables and fields are exact, I found some errors in the query. Best to not use spaces, if you want a break in name use an underscore. Also are you sure the datatypes of the fields in the 2 tables are the same for each field? The below worked for me in testing.

INSERT INTO CRChanges ( CRID, NewCRStatus, Decision, [Admin Only], ChangeDate, OldCRStatus, OldAdminOnly, OldDecision )
SELECT [Change Request Intake].ID, [Change Request Intake].[Change Request Status], [Change Request Intake].Decision, [Change Request Intake].[For Admin Only], Date() AS Expr1, CurrentStatus.[Change Request Status], CurrentStatus.[For Admin Only], CurrentStatus.Decision
FROM [Change Request Intake] INNER JOIN CurrentStatus ON [Change Request Intake].ID = CurrentStatus.ID
WHERE ((([Change Request Intake].[Change Request Status])<>[CurrentStatus]![Change Request Status])) OR ((([Change Request Intake].[For Admin Only])<>[CurrentStatus]![For Admin Only])) OR ((([Change Request Intake].Decision)<>[CurrentStatus]![Decision]));

[PCartland]

Thanks Bulzie, I've done some rework of the query and it is now working for differences between the status fields and the decision fields between the two tables. It still isn't adding a record to the changes table when [Change Request Intake].[For Admin Only] is Not Like [CurrentStatus].[For Admin Only]. I agree on the using spaces issue, but as most of them come from the SharePoint List table I can't change them. I can live with it as is for now. Thanks

[Bulzie]

Make sure the For Admin Only datatype in both tables is the same. Also make sure you have a record where these are different so the code triggers. And make sure your condition is accurate with spelling and such:

([Change Request Intake].[For Admin Only])<>[CurrentStatus]![For Admin Only]

Also to test it out, remove the other 2 conditions and just make it write when For Admin Only is different.