Counting changes in fields

[mrsanzoo]

Hello all,
As Im just beginner in access and related stuff I would really appreciate your help with my problem which is lasting for a few days already. Im working on a changes report and my purpose is to look for changed data in table from last month compared with current one. Previously I used excel for this report but as it getting bigger its more comfortable to use access. So to explain it a little bit I took data from last month and pasted it next to the data from this month and then I used this array formula in excel in order to count the changes in related rows, here it is : {=IF(C3="",SUM(IF(D3:CI3<>CK3:FP3,1,0)),"")}. My question would be if I could do something like this in access ? If better explanation is needed please let me know.
All your help is much appreciated.

Martin

 

[VilaRestal]

You have two tables, one with the old data, one with the new? Structure exactly the same, only data different?

 

[mrsanzoo]

yes exactly fields and structure is the same only data are different, fields are related to suppliers setup so need to count how many changes have been performed during a month.
Thanks and regards

 

[VilaRestal]

How many records have changed? Or how many changes? (And if the latter: what about new or deleted records?)

Either way could you give the name of the tables and all fields that need to be compared and primary key field? (Is PK field autonumber, or otherwise can be guaranteed not to be reused if record is deleted?)

 

[mrsanzoo]

Hi VilaRestal,
I`ve already created query to look for new suppliers as well as for deleted one and put them together into one query called "qryNEW=OLD". Attached you will find an excel example with executed and pasted query. Note that grey header columns refer to query headers also first 3 columns(yellow) are populated with formulas which Id like to somehow put into access. Hope I was clear enough if not please let me know.
Thanks for your help.

 

[VilaRestal]

So this is another query that ignores new and deleted records and just compares records that exist in both tables? (And we're sure that if they have the same primary key they are referring to the same record - albeit with possible differences in other fields?)

If so, I think the way you'd have to do it (and if anyone can think of a better way please say) is:

SELECT TableOld.Unique, 'ID' AS [Changed Field] FROM TableOld INNER JOIN TableNew ON TableOld.Unique = TableNew.Unique WHERE Nz(TableOld.ID,0) <> Nz(TableNew.ID,0)
UNION ALL
SELECT TableOld.Unique, 'Country' AS [Changed Field] FROM TableOld INNER JOIN TableNew ON TableOld.Unique = TableNew.Unique WHERE Nz(TableOld.Country,"") <> Nz(TableNew.Country,"")
UNION ALL
SELECT TableOld.Unique, 'Supplier Code' AS [Changed Field] FROM TableOld INNER JOIN TableNew ON TableOld.Unique = TableNew.Unique WHERE Nz(TableOld.[Supplier Code],"") <> Nz(TableNew.[Supplier Code],"")
UNION ALL
SELECT TableOld.Unique, 'Name' AS [Changed Field] FROM TableOld INNER JOIN TableNew ON TableOld.Unique = TableNew.Unique WHERE Nz(TableOld.Name,"") <> Nz(TableNew.Name,"")

etc.

A Union All for each field comparison.

If no fields are ever Null then you can do without the Nz's.

The number of records returned would be the number of field changes.

 

[RainLover]

Vila, I know this is mrsanzoo's question but why aren't you looking at Tagging each record change as it happens. I was thinking of a Date Stamp.

Most likely I am barking up the wrong tree.

 

[VilaRestal]

For future yes possibly; although it would need to be a Date Stamp on each field and you'd be comparing each field's Date Stamps rather than their values: it doesn't simplify the calculation.

But anyway, as it is there's no such system to go by...

 

[mrsanzoo]

Hi VilaRestal,
Thanks you for the code Iam about to test it out. To answer your question yes supplier on the left side is referring to the same supplier on the right side. The only difference is that on the right side is the old data for this supplier from previous month and deleted and added suppliers are already covered.The only think I need to know is how many and what fields were changed except those added and deleted that`s why it brings me to the question if it woudn`t be easier to work with this query where both old and new data is already matched together next to each other because when I look on your script it looks like I have to break it down again into 2 tables and then run the query. Am I right ? Please correct me if Im wrong.
Thanks and regards,

 

[VilaRestal]

No, you could do it from the one query (although you presumably do have the two tables and those inner joins will not include new and deleted records). It would be something like this (and I suspect Access would want an alias for every qryOldNew so I've put those in):


SELECT Q1.Unique, 'ID' AS [Changed Field] FROM qryOldNew AS Q1 WHERE Nz(Q1.ID_Old,0) <> Nz(Q1.ID_New,0)
UNION ALL
SELECT Q2.Unique, 'Country' AS [Changed Field] FROM qryOldNew AS Q2 WHERE Nz(Q2.Country_Old,"") <> Nz(Q2.Country_New,"")
UNION ALL
SELECT Q3.Unique, 'Supplier Code' AS [Changed Field] FROM qryOldNew AS Q3 WHERE Nz(Q3.[Supplier Code_Old],"") <> Nz(Q3.[Supplier Code_New],"")
UNION ALL
SELECT Q4.Unique, 'Name' AS [Changed Field] FROM qryOldNew AS Q4 WHERE Nz(Q4.Name_Old,"") <> Nz(Q4.Name_New,"")

etc.

 

[mrsanzoo]

I have corrected the filed names after clause "FROM" according my names so it should work like this:
SELECT Q1.Unique, 'ID' AS [Changed Field] FROM qryNewOld AS Q1 WHERE Nz(Q1.IDOLD,0) <> Nz(Q1.IDNEW,0)

but once I click run it asks me to enter parameter value so assuming "Q1.Unique" isn`t correct but don`t know why and what to put there. Could you please advise ?

 

[VilaRestal]

I thought from the spreadsheet that was the name of your primary key. Whatever the primary key field is in the table (perhaps with OLD or NEW after it - they should be equal).

 

[mrsanzoo]

Hi VilaRestal,
Thanks for the SQL however when I came to Q14 message box popped up with note "Query is too complex" so most likely its too much to process it and there are at least another 30 fields to add and compare so don`t know if you have any other idea how to solve this but I would appreciate if you have one.
Thanks for all your support.

 

[VilaRestal]

You could try building a few separate queries that aren't 'too complex' and then union them in another query.

The alternative method would be with VBA, which would be more robust but wouldn't be a query:

Open a recordset based on qryNewOld and loop through each record and for each record compare each pair of fields incrementing a counter if they're different.

Or, less efficiently but easier to write: Open two recordsets based on the two tables, loop through the records of the first, for each find the record in the second and then loop through the fields comparing the two and incrementing the counter if they're different.

 

[mrsanzoo]

Well as I have no VBA knowledge I have spit them into 2 queries and it works but I have difficulties to put hem together as if I use UNION ALL between them its shows again note "Query is too comple" or when I use UNION there is not the same number of columns in each of them. Any idea how to do it without using VBA ?
Thanks

 

[VilaRestal]

There should be two columns in each: Unique and [Changed Field]:

SELECT * FROM Qry1
UNION ALL
SELECT * FROM Qry2