Identifying Differences within values of few columns inside same Table

[saketg]

Hi,



I am using MSAccess 2010. I have one table having 24 columns - ID (primary key), IgnoreColumn1, IgnoreColumn2, AnalysisColumn (not unique), Column5, Column6, ..., Column24. As the name suggests, ID and IgnoreColumns are the ones we are not bothered about.

The goal is to compare Column5, Column6, ..., Column24 in all rows having same value for AnalysisColumn and report differences.

Let's assume that for some value of AnalysisColumn we get 25 records. We can take values for Column 5 through Column 24 from 1st record as standard and compare them to all other rows to report differences (ideally highlight the differences).

Initially, I tried GROUP BY and grouped all the 20 columns in question. It helps me find different categories of values and not the actual differences. Moreover I also cannot track all IDs for the rows that are different.

I do not see a Cursor in MSAccess. I tried using ADODB Recordset and running a loop but for some reason it keeps failing.

Please direct me in which direction should I channelize my efforts?

[Rod]

Hi,

I'm sure there may be smart, elegant ways to do what you want using transform or shaping but I can't get my head around it.




However what I suggest you want to do is:

1. Devise a subquery that extracts one and only one row for a given value of Analysis Column.
2. Join this one row result set to the original table and extract calculated differences for each matching row in the original table. These differences would be zero when matched with the row extracted by the subquery.

A drawback (?) with this solution is that you have to repeat this for each and every value of Analysis Column. If you want to do it for everything at once, I can think of no way other than write a VBA routine to build a new table in the form that you want. Cursor? I seem to remember something like that, in DB2 was it? No nothing like that in VBA per se.

Now I shall try to construct the SQL. Here's the subquery:

 
PARAMETERS [Enter Analysis Value] Long;
SELECT TOP 1 * FROM tblName WHERE AnalysisColumn = [Enter Analysis Value];

I have defined the parameter data type as long, it may be different for you.

Now to insert the subquery into the main query. For brevity I name the subquery as S and the main query as M.

 
PARAMETERS [Enter Analysis Value] Long;
SELECT S.Column5 - M.Column5 As C5, S.Column6 - M.Column6 AS C6 
FROM tblName AS M 
INNER JOIN
(SELECT TOP 1 * FROM tblName 
WHERE AnalysisColumn = [Enter Analysis Value]) AS S 
ON M.AnalysisColumn = S.AnalysisColumn;

Hopefully you have understood the pattern from the above.

If you want a VBA solution then repost stating whether you prefer DAO or ADO.

[Rod]

I have been thinking - a beer always helps, well, three actually - and I believe your requirement can be done in one SQL for all values. I hope the table is not too big otherwise the solution may not be that efficient. It involves a three-level nested query.

The innermost subquery extracts a list of valid IDs, one for each different value of AnalysisColumn.

 
SELECT MIN(ID) 
FROM tblName 
GROUP BY AnalysisColumn;

You could of course use MAX or even FIRST and ORDER BY if you are interested in which row to select as your 'standard.'

The intermediate query appends the column values.

 
SELECT B.AnalysisColumn, B.Column5, B.Column6, ...
FROM tblName AS B 
INNER JOIN 
(SELECT MIN(ID) 
FROM tblName 
GROUP BY AnalysisColumn) AS C 
ON B.ID = C.ID;

Now put this intermediate query inside the outer query.

 
SELECT X.Column5 - A.Column5 AS C5, ...
FROM tblName AS A
INNER JOIN 
(SELECT B.AnalysisColumn, B.Column5, B.Column6, ...
FROM tblName AS B 
INNER JOIN 
(SELECT MIN(ID) 
FROM tblName 
GROUP BY AnalysisColumn) AS C 
ON B.ID = C.ID) AS X
ON A.AnalysisColumn = X.AnalysisColumn;

Obviously I have not tested this.

[Rod]

I was bored so I tested it.

 
SELECT tblAnalysis.AnalysisColumn, tblAnalysis.ID, [tblAnalysis].[Column5]-[B].[Column5] AS Diff5, [tblAnalysis].[Column6]-[B].[Column6] AS Diff6
FROM tblAnalysis 
INNER JOIN 
(SELECT tblAnalysis.ID, tblAnalysis.AnalysisColumn, tblAnalysis.Column5, tblAnalysis.Column6
FROM tblAnalysis 
INNER JOIN 
(SELECT Min(tblAnalysis.ID) AS ID, tblAnalysis.AnalysisColumn 
FROM tblAnalysis 
GROUP BY tblAnalysis.AnalysisColumn 
ORDER BY Min(tblAnalysis.ID)) AS A 
ON tblAnalysis.ID=A.ID) AS B 
ON tblAnalysis.AnalysisColumn = B.AnalysisColumn
ORDER BY tblAnalysis.AnalysisColumn, tblAnalysis.ID;

Where tblAnalysis is the name of your table. Here's the result using my test data.

Attachment 3786

Here's the test data.

Attachment 3787