Possible to query the changes data?

[calvinle]

Hi,

On a monthly basis, I need to load a list of client to the database.
Is it possible to create a query to identify if a field [status] has been changed?
or what will be the best way to do it in Access?

Thanks

 

[gemma-the-husky]

if you want to track changes, you need to log the changes at the time it happens. Either build a history into the table, or store changes in a separate table - research "audit trails"

 

[The_Doc_Man]

If I understand your question, there is an implication that you are actually RELOADING client data. If so, then you need to import this in a temporary table because the only way to know if a status has changed is to keep the old status. Therefore, what you would do is first import to the temp table, then do a side-by-side comparison between the permanent and temporary tables, perhaps using a JOIN between them, and selecting those records where the status differ.

This is "air code" but after the import, something resembling this:

SELECT P.ClientID, other client data, P.Status As OldStatus, T.Status As NewStatus
FROM PermTable As P INNER JOIN TempTable As T ON P.ClientID = T.ClientID
WHERE P.Status <> T.Status ;

This would produce a list for you where the statuses had changed.

When this comparison was done AND you have acted on the changed statuses, you would then AND ONLY THEN complete the import by updating the permanent table and then deleting the contents of the temporary table.

 

[jdraw]

As others have indicated, it would be helpful to know more details of your application. Readers have to guess some critical specifics, so more info and/or examples would be helpful. Clarity of requirements is key to a focused response.
Good luck.

 

[calvinle]

Hi,

Sorry, I have include a sample database with a query that I created it, but it's taking too long to load the query. Any other way to do so?

 

[Cronk]

No wonder it takes so long with all the dLookups and grouping.

Join the temp table with the main table on both mem_id and report_month. This matches equivalent records.

select P.id from tmpData as T inner join tbl_data as P  on T.mem_id = P.mem_id and T.report_month = P.report_month where T.emp_status <> P.emp_status

 

[calvinle]

If I understand your question, there is an implication that you are actually RELOADING client data. If so, then you need to import this in a temporary table because the only way to know if a status has changed is to keep the old status. Therefore, what you would do is first import to the temp table, then do a side-by-side comparison between the permanent and temporary tables, perhaps using a JOIN between them, and selecting those records where the status differ.

This is "air code" but after the import, something resembling this:

SELECT P.ClientID, other client data, P.Status As OldStatus, T.Status As NewStatus
FROM PermTable As P INNER JOIN TempTable As T ON P.ClientID = T.ClientID
WHERE P.Status <> T.Status ;

This would produce a list for you where the statuses had changed.

When this comparison was done AND you have acted on the changed statuses, you would then AND ONLY THEN complete the import by updating the permanent table and then deleting the contents of the temporary table.

I get what you are trying to achieve here, but I want to be able to keep all the data of every month for audit purposes and not only import data that has changes.

Basically, import all data on monthly basis, then like my sample database, create something that looks the query. I did the same exercise using unbound field and it loads faster. See attachment.

The only thing I cannot make it work is the filtering of that unbound field "Diff". Any way I can filter that table for <> 0?