CODEWITHSUNDEEP
sqlms-accessms-access-2007
spriore
spriore

Reputation: 613

Access UNION and JOIN query

Situation: The Access database I'm working on separates parts into left and right parts. The same part can be used on both sides. I am trying to create a query to count the total number of individual parts needed per week.

Question: How do I create a query that allows to UNION multiple fields and JOIN as shown below?

Table 1:
Part # |Left Part | Left Part Qty | Right Part | Right Part Quantity
1        xyz         5               lmn          7
2        abc         8               xyz          4

Table 2:
Part # | Needed
1        10
2        25

Query:
Part | Quantity
xyz    150 (5 * 10) + (4 * 25) 
abc    200 (8 * 25) 
lmn    70 (7 * 10)

Upvotes: 2

Views: 1195

Answers (2)

Maciej Los
Maciej Los

Reputation: 8591

This should work as well:

SELECT T.Part, SUM(T.Qty * S.Needed) AS Quantity
FROM (
SELECT A.[Part#] AS PartNo, A.LeftPart As Part, A.LeftPartQty As Qty
FROM Table1 AS A
UNION ALL
SELECT B.[Part#] AS PartNo, B.RightPart As Part, B.RightPartQty As Qty
FROM Table1 AS B
) AS T INNER JOIN Table2 AS S ON T.PartNo = S.[Part#]
GROUP BY T.Part
ORDER BY T.Part

Result:

Part    Quantity
abx     200
lmn     70
xyz     150

For further information, please see:
UNION (MS Access)
Subqueries (MS Access)
INNER JOIN Operations (MS Access)
LEFT JOIN, RIGHT JOIN Operations (MS Access)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269453

You have a problem in MS Access, because you cannot put a union/union all into a subquery. If you have a table of parts (which you should have), then you can use left join, so I think the following does what you want:

select p.partname,
       sum(nz(t1l.leftpartqty * t2.needed) + nz(t1r.rightpartqty * t2.needed))
from ((part as p inner join
       table2 as t2
       on p.part# - t2.part# 
      ) left join
      table1 as t1l
      on t1l.leftpart = p.partname 
     ) left join
     table1 as t1r
     on t1r.rightpart = p.partname
group by p.partname;

If you don't have such a table, you can use a view to do the union all and proceed from there.

Upvotes: 1

Related Questions