Creating queries to track changes in data

[dml2000]

I am trying to create several queries that will consist of large amount of 'changing' data. I'm going to upload raw data from our an external system into Access each week via table format and then I'd like to create queries that would compare the data and see what has been changed. Essentially, I'd like to track what has been added and deleted week-over-week. For example, if I run data for week 1 that has A, B, C, D, E, F; then I run data for week 2 and the result would be A, C, D, F, J, K; week 3's data would be A, C, D, E, F, J, K, etc. Is there a way in Access where I can do this? I'm not too familiar with coding/developing, just the basics in Access (i.e., writing simple queries using the query view). Any feedback would be appreciated. Thanks!

[CJ_London]

yes it can be done - but depends how your data is presented as to the solution. Provide some examples of how your data is presented. Note if it is a 'spreadsheet' row view i.e.

wk1 A B C D
wk2 D A C E

rather than a database column view

wk1 A
wk1 B
wk1 C
wk1 D
wk2 D
wk2 A
wk2 C
wk2 E

it will need to be converted to the latter database column view first otherwise it will be a significantly harder if not impossible job.

[dml2000]

It will be in a column. Here's an example of a shortened version of the data:

MANAGER BUSINESS COMPONENTS
Smith Finance Basel
Smith Finance SOX
Jones Compliance AML
Jones Compliance Anti-Tying
Smith Compliance AML
Smith Finance Basel
John Risk Models
John Risk Market
John Finance Basel


I expect the components data to change as people may be adding/deleting them week-over-week and would like to capture what's been added and deleted by creating queries. I'll have the raw data and will upload to Access every week into a table and will adjust the queries, but how would I setup the query to capture what's been changed (i.e., added/deleted) since the previous week?

yes it can be done - but depends how your data is presented as to the solution. Provide some examples of how your data is presented. Note if it is a 'spreadsheet' row view i.e.

wk1 A B C D
wk2 D A C E

rather than a database column view

wk1 A
wk1 B
wk1 C
wk1 D
wk2 D
wk2 A
wk2 C
wk2 E

it will need to be converted to the latter database column view first otherwise it will be a significantly harder if not impossible job.

[June7]

Need a date field.

Then I think just a query will not be adequate for reviewing data.

Build a report that organizes the records. Report can group by components and sort the records by date within the grouping so you see the change of manager for each component. Or group by manager and sort records by date to see change in components.

[CJ_London]

You will also need to clarify what happens with these two records

Jones Compliance AML
Jones Compliance Anti-Tying

without a unique ID and/or date, how do you decide which of the above two records is the latest one? Remember databases have no concept of next/previous/first/last without an order

[dml2000]

I do have Reference #s and the date for when the report was created.

For the example that was reference, a Jones Compliance SOX may get added and a Jones Compliance AML may be removed. I was hoping to create two queries-- one for new/add and the other for what was removed/deleted. I think linking ref # in the two tables in the query may provide this info.

I was playing around with the 'Find Unmatched Query Wizard' but it seems to include both additions and deletes of the data. Is there a way to show it separate

You will also need to clarify what happens with these two records

Jones Compliance AML
Jones Compliance Anti-Tying

without a unique ID and/or date, how do you decide which of the above two records is the latest one? Remember databases have no concept of next/previous/first/last without an order

[dml2000]

I tried to create this using the 'Find Unmatched Query Wizard' and it isn't giving me the results that I want.
I also do have Ref #s included in the data.
Maybe to clarify, the raw data would look like this:


Week 1 Data:
Ref # | Activity | Coverage
1234 | Finance | <<blank>>
2321 | Finance | Basel
3213 | Compliance | AML
3293 | Finance | SOX

Week 2 Data:
Ref # | Activity | Coverage
1234 | Finance | SOX <-- note that this was added in week 2
2321 | Finance | Basel
3213 | Compliance | AML
3213 | Compliance | Privacy <-- note that this was added in week 2
3293 | Finance | SOX

Is there a way to capture what was added and deleted week-over-week?

I do have Reference #s and the date for when the report was created.

For the example that was reference, a Jones Compliance SOX may get added and a Jones Compliance AML may be removed. I was hoping to create two queries-- one for new/add and the other for what was removed/deleted. I think linking ref # in the two tables in the query may provide this info.

I was playing around with the 'Find Unmatched Query Wizard' but it seems to include both additions and deletes of the data. Is there a way to show it separate

[CJ_London]

you can try a simple compare query to find records that have changed, but your ref field is not unique (3213 appear twice in week2) so not sure you will get the result you want

SELECT Week2.*
FROM Week2 inner join week1 on Week2.ref=week1.ref
WHERE week2.activity<>week1.activity or week2.coverage<>week21.coverage

and a left join to find new records in week2

SELECT Week2.*
FROM FROM Week2 left join week1 on Week2.ref=week1.ref
WHERE WEEK1.Ref is null

[dml2000]

If I do a concatenate with the Ref # and Coverage fields, would that help? What I'm worried with this is what if there is a <<blank>> in the coverage field.

Also, for the two SQL codes that you mentioned, is one for 'add' changes and the other one 'deleted/removed' changes?

you can try a simple compare query to find records that have changed, but your ref field is not unique (3213 appear twice in week2) so not sure you will get the result you want

SELECT Week2.*
FROM Week2 inner join week1 on Week2.ref=week1.ref
WHERE week2.activity<>week1.activity or week2.coverage<>week21.coverage

and a left join to find new records in week2

SELECT Week2.*
FROM FROM Week2 left join week1 on Week2.ref=week1.ref
WHERE WEEK1.Ref is null

[CJ_London]

neither - just shows the changes but once written you can convert them to update or insert queries. It is not at all clear from your descriptions what you are trying to achieve. You might as well just delete the week1 records and inset the week2 records

What I'm worried with this is what if there is a <<blank>> in the coverage field.

there are two types of 'blank' in databases, nulls and zero length strings, best thing you can do is to try it and see what happens (take a backup of week 1 first, just in case)