I need some help to perform the following actions in MS Access with a SQL query.
The operations I would like to perform are illustrated in the following example:
Initial tables
TABLE A
Name H1 H2 H3
A 5 10 5
B 1 2 3
C 7 3 1
TABLE B:
Name H1 H2 H3
1 1 1 1
2 2 2 2
1) First step: Results
NAME TABLE A NAME TABLE B H1 H2 H3
A 1 4 9 4
A 2 3 8 3
B 1 0 1 2
B 2 1 0 1
C 1 6 2 0
C 2 5 1 1
So, the first row of this new table is calculated as the ABSOLUTEVALUE( TABLE A (row A)-TABLE B(row1)), the second row of this table would be ABSOLUTEVALUE( TABLE A (row A)-TABLE B(row2)) and so on.
2) Second step: Results
NAME TABLE A NAME TABLE B H1 H2 H3 Total
A 1 4 9 4 17
A 2 3 8 3 14
B 1 0 1 2 3
B 2 1 0 1 2
C 1 6 2 0 8
C 2 5 1 1 7
So in this step, I will need to add a field whis is calculated as the sum of values H1, H2 and H3 of each row
3) Final step: Results
Name H1 H2 H3
A 3 8 3
B 1 0 1
C 5 1 1
And in the final step, we select those A, B & C rows from the previous table in which the field Total has the minimum value.
Thanks!
For Step 1 please try...
SELECT A.NameA AS [NAME TABLE A],
B.NameB AS [NAME TABLE B],
ABS( A.H1 - B.H1 ) AS H1,
ABS( A.H2 - B.H2 ) AS H2,
ABS( A.H3 - B.H3 ) AS H3
FROM A,
B;
For Step 2 please try...
SELECT A.NameA AS [NAME TABLE A],
B.NameB AS [NAME TABLE B],
ABS( A.H1 - B.H1 ) AS H1,
ABS( A.H2 - B.H2 ) AS H2,
ABS( A.H3 - B.H3 ) AS H3,
H1 + H2 + H3 AS [Total]
FROM A,
B;
For Step 3 please try either...
SELECT A.NameA AS [NAME TABLE A],
MIN( ABS( A.H1 - B.H1 ) ) AS H1,
MIN( ABS( A.H2 - B.H2 ) ) AS H2,
MIN( ABS( A.H3 - B.H3 ) ) AS H3
FROM A,
B
GROUP BY A.NameA;
As per my comment to AVG, this situation uses the Cartesian product of two tables, which is where each record in the first table is joined to each of the records from the second table. This can be achieved by performing a CROSS JOIN as I have done by placing FROM A, B in each of my statements. This join gives us the following dataset...
NameA | A.H1 | A.H2 | A.H3 | NameB | B.H1 | B.H2 | B.H3
------|------|------|------|-------|------|------|-----
A | 5 | 10 | 5 | 1 | 1 | 1 | 1
A | 5 | 10 | 5 | 2 | 2 | 2 | 2
A | 5 | 10 | 5 | 1 | 1 | 1 | 1
A | 5 | 10 | 5 | 2 | 2 | 2 | 2
A | 5 | 10 | 5 | 1 | 1 | 1 | 1
A | 5 | 10 | 5 | 2 | 2 | 2 | 2
(Please note that when a field is joined to another table and its name does not exist in that other table, then you will be able to continue referring to it just by its name without needing to specify the table name (though you can still do that if you choose). If the new field does share a name with a field in the other table, then each field will need to be referred to by both the table name and the field name.)
This dataset can be used for all three tasks.
For the first task the ABS() function can be used on the difference between the H1 values, etc. Please note that if you generate a field, such as with ABS( A.H1 - B.H1 ), and do not give it a name using AS, then the new field will be arbitrarily given a name, which is often the expression that generated the field (ABS( A.H1 - B.H1 ) in this case) or something else unwieldy. As such it is strongly recommended that you name all generated fields if you intend to refer to them in other parts of the equation (or elsewhere).
For the second task an expression that simply adds up the computed H fields, such as H1 + H2 + H3, will suffice.
For the third task we can use the dataset generated in the first task, sans the NameB column. We can then group the rows together by the value of NameA, and use the aggregate function MIN() to choose the minimum value from each H column.
If you have any questions or comments, then please feel free to post a Comment accordingly.
Further Reading
How to include this SQL subquery for absolute number's value? (on ABS())
How to use cross join in access? (on using a CROSS JOIN in Access)
http://www.w3resource.com/sql/joins/cross-join.php (on SQL Cross Joins in general)