Share via

Query/Report Generation

Randal Flinn 311 Reputation points
2021-05-26T19:39:19.907+00:00

Hello,

I have two tables tblA and tblB that contain a common field called WO. WO is not unique and can have multiple repeats occurrences in each table. In tblB there is also a field (QTY) that I would like to sum based on equal WO values in tblA and tblB. What I was attempting was to generate a Query for tblA that "groups" the WO values. I then thought I would run a 2nd Query using the first Query and tblB (related by the WO value).

tblA - WO values tblB (WO) tblB (QTY)
1 1 10
1 1 20
1 1 10
2 2 30
3 2 40
4 9 50

Query 1
WO
1
2
3
4

Query 2
WO QTY
1 40
2 70
3 -
4 -

Is this the appropriate method? I am not receiving QTY values in the 2nd Query.

Thanks

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
859 questions
0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. DBG 2,301 Reputation points
    2021-05-27T01:01:57.633+00:00

    Hi. Not sure I understand. How could 1 2 40 and 4 9 50 be matching WOs?

  2. Natiq Rzazade 1 Reputation point
    2021-05-27T12:35:00.983+00:00

    Do you need sql query for this or I can't right understand?

  3. DBG 2,301 Reputation points
    2021-05-27T14:30:12.5+00:00

    Can you also show us the final result you expect to get? Thanks.

  4. DBG 2,301 Reputation points
    2021-05-27T16:06:44.467+00:00

    Hi. Thanks for the additional information. So, are you saying you don't need to see WOAs 3 & 4 in the result since they don't have a matching QTY in WOB? How about WOB 9, you don't want it listed in the result either?

  5. DBG 2,301 Reputation points
    2021-05-27T17:50:17.803+00:00

    Alright, I think I fully understand now. If you're using SQL Server, you can use a FULL OUTER JOIN to get that result. But, if you're using Access, then you'll have to use a UNION query.

    For example: 

    SELECT T1.WO AS A, T2.WO AS B, Sum(T2.QTY) AS Total
FROM (SELECT DISTINCT TableA.WO FROM TableA) AS T1
LEFT JOIN TableB AS T2
ON T1.WO=T2.WO
GROUP BY T1.WO, T2.WO
UNION
SELECT Null, T3.WO, Sum(T3.QTY)
FROM TableB AS T3
WHERE T3.WO NOT IN (SELECT DISTINCT TableA.WO FROM TableA)
GROUP BY T3.WO
ORDER BY 1, 2

    (untested)
    Hope that helps...

    0 comments