SQL MS ACCESS: selective operations between tables and filtering results

Question

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!

A database is not a spreadsheet and does not work like one. Tables need to be related by common values not row position. Additionally, 'Name' is a reserved word and should not be used as a field name. You should do some research on database design and normalization and redesign your tables. — AVG, Apr 15 '17 at 12:22
AVG, Although they are often used poorly, `CROSS JOIN`'s are still sometimes an appropriate tool for a situation. Since this situation uses the Cartesian product of these two tables without the need for exclusionary `WHERE`'s, a `CROSS JOIN` is ideal for this situation. They are normally written as `FROM A, B`, though some languages do allow `A CROSS JOIN B`. I agree with you about the use of `Name`. And although I too believe in the strengths of normalisation, etc., I do not believe the tables in this situation can be normalised any further (which happens sometimes). — toonice, Apr 15 '17 at 12:34

score 0 · Accepted Answer · edited May 23 '17 at 12:02

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)

SQL MS ACCESS: selective operations between tables and filtering results

1 Answers1