Access SQL query that uses a field from one table within where clause to create a calculated field

Question

This really isn't that complicated but I can't wrap my head around it. Ive searched thoroughly but even coming up with a search string or title for this question was hard enough. The explanation will tell all:

Table "labelDrops" has fields "PO" and "Qty" (among other unrelated fields)
Table "imageLiveCount" has field "PO"
I want to count instances (count(imageLiveCount.id)) where imageLiveCoount.PO = labelDrops.PO and then subtract that from the "QTY" to create a calculated field called "QtyLeft".
The result would basically look like "SELECT PO, Qty, answerToThisQuestion AS QtyLeft FROM labelDrops"

You're trying to do a calculation that is undefined. You want want to count id from your imageLiveCount table where PO = PO, this gives you an aggregate count. Then you want to subract QTY, but QTY is not an aggregate. There is no reference for which QTY you want to subtract from your aggregate count? So you mean that you want to to subtract all of your QTY as separate records from your aggregate? Are you counting all of imageLiveCount.id, regardless of grouping, or are you only counting the ones that match PO? Is PO a key fields? or some other value? — Gene

HansUp HansUp · Accepted Answer · 2015-10-12T14:12:26

Start by creating a query which gives you the count for each PO in your imageLiveCount table:

SELECT i.PO, Count(*) AS CountOfInstances
FROM imageLiveCount AS i
GROUP BY i.PO

If that query returns what you want, join it to the labelDrops table and compute QtyLeft:

SELECT
    l.PO,
    l.Qty,
    (l.Qty - sub.CountOfInstances) AS QtyLeft
FROM
    labelDrops AS l
    INNER JOIN
        (
            SELECT i.PO, Count(*) AS CountOfInstances
            FROM imageLiveCount AS i
            GROUP BY i.PO
        ) AS sub
    ON l.PO = sub.PO

Access SQL query that uses a field from one table within where clause to create a calculated field

1 Answers