CODEWITHSUNDEEP
sqlms-accessjoin
wiz6
wiz6

Reputation: 51

MS Access: joining 3 tables and unique rows

I have 2 tables:

Table 1

|ID |Total |Name |
|1  |100   |Car  |
|2  |200   |House|
|6  |600   |Car  |

and

Table 2

|ID |Total |Type |
|1  |200   |A    |
|1  |300   |B    |
|3  |100   |A    |
|3  |150   |B    |
|4  |400   |A    |
|5  |500   |B    |
|6  |700   |A    |

Basically I want to create a query to join all IDs to one result. I know there are 2 tables but one type of result needs joining 3 tables.

Result should be:

|ID|Total |Total A|Total B|Name |
|1 |100   |200    |300    |Car  | - IDs from all tables
|2 |200   |-      |-      |House| - ID from table1
|3 |-     |100    |150    |-    | - IDs from table2
|4 |-     |400    |-      |-    | - ID from table2(type A)
|5 |-     |-      |500    |-    | - ID from table2(type B)
|6 |600   |700    |-      |Dog  | - ID from table1 and table2(type A)

This is trivial example, there can be duplicate rows in table2 which needs group by. Also there are long where clause so performance is very low. All ID in result must be unique. This query executes when clicking button on form. Is it possible to create that kind of query? Maybe i should create some temp table before or something to simplify the query? Or maybe my table data structure is wrong?

New additional data:

Table 1

|ID |Total |Name |
|1  |100   |Car  |
|2  |200   |House|
|6  |600   |Car  |
|1  |400   |House|

In Table1 can be multiple same ID rows. Also column name specifies the column 'Total' sign(negative or positive). Lets say value 'Car' is negative and 'House' positive.

So the result should be:

|ID|Total |Total A|Total B|
|1 |300   |200    |300    
|2 |200   |-      |-         
|3 |-     |100    |150    
|4 |-     |400    |-          
|5 |-     |-      |500
|6 |-600  |700    |-

So we see that ID=1 Total=400-100 and ID=6 Total=-600 due to column name 'value'. My problem is that if i select Table1.Name to query then i get two same ID rows(ID=1) :S Is it possible to create such query so simple?

Upvotes: 0

Views: 812

Answers (2)

mwolfe02
mwolfe02

Reputation: 24237

This reproduces your expected output based on your updated sample tables:

SELECT i.ID
 , First(T1.Total) AS [Total]
 , Sum(IIf(T2.Type='A',T2.Total,Null)) AS [Total A]
 , Sum(IIf(T2.Type='B',T2.Total,Null)) AS [Total B]

FROM ((SELECT DISTINCT ID FROM Table1
        UNION
       SELECT DISTINCT ID FROM Table2) AS i 
  LEFT JOIN (SELECT ID, Sum(IIf(Table1.Name='House',Table1.Total,-Table1.Total)) AS Total 
             FROM Table1
             GROUP BY ID) AS T1 ON i.ID=T1.ID)
  LEFT JOIN Table2 AS T2 ON i.ID=T2.ID
GROUP BY i.ID

Upvotes: 1

xQbert
xQbert

Reputation: 35343

From an example (Previous Stack Question) I found perhaps this... 

TRANSFORM max(t2Total) AS T2Total

SELECT B.ID, B.Total, B.Name
FROM (SELECT T1.ID, T1.Total, T1.Name, T2.Total as T2Total, T2.Type
      FROM table1 T1
      LEFT JOIN Table2 T2 ON T1.ID = T2.ID
      GROUP BY T1.ID, T1.Total, T1.Name
      UNION
      SELECT T1.ID, T1.Total, T1.Name, T2.Total, T2.Type
      FROM table1 T1
      RIGHT JOIN Table2 T2 ON T1.ID = T2.ID
      GROUP BY T1.ID, T1.Total, T1.Name) B
PIVOT B.Type;

However it depends on what: "there can be duplicate rows in table2 which needs group by" means... If you truly mean every value on the row is duplicated then you'd need to switch table2 in the above to:

(Select distinct * from table2) T2

Assuming you don't want the duplicates.

I am making an assumption that type and ID would be unique in table2. If not then the "Max" may not give the desired result.

Upvotes: 0

Related Questions