Query to get Total Purchases and Closing Stock for Period

Question

My current setup:

Products Table

UPC(Number)
PNAME(Text)

Purchases Table

purDate(Date)
PNAME(combo box)
Quantity(Number)

Closing Stock

endDate(Date)
PNAME(combo box)
Quantity(Number)

I want to create a query that list all the PNAME from Products, Total Purchased and the Closing Quantity for each month end. On the following month end, the closing stock for the previous month will be the opening stock for that month.

The purchases occur throughout the month and we take the closing stock on the last day of every month. I tried using the query wizard and importing the PNAME from Products, Quantity from Purchases and Quantity from Closing Stock but I am only getting the totals for the Purchases and the Total for the Closing Stock is blank.

Lee Mac · Accepted Answer

Firstly, a brief word of advice:

Your question is very broad in that you have stated what you want but haven't anted up any code showing your efforts to get there - this is likely to discourage members from supplying you with ready-made solutions, and your question is more likely to be voted closed.

Nevertheless, I'll throw you a bone to point you in the right direction...

The Queries

Assuming that the UPC field is the primary key in your Products table, you should use this field (as opposed to the PNAME field) to reference your products in both your Purchases table & Closing Stock table, so that each item can be uniquely identified.
Assuming that you implement the above advice, to produce the desired result, you'll want to constrct three separate queries:
1. Purchases within the reported month
2. Opening stock
3. Closing stock

You can then construct a 4^th query to display the product information alongside the data from these three queries. Both of the stock queries (2) & (3) will obviously source their data from the Closing Stock table, but with criteria configured for different months.

1. Purchases

Assuming you are reporting on the previous month, the query for purchases might look something like:

select 
    pu.upc, sum(pu.quantity) as puqty
from 
    purchases pu
where 
    pu.purdate >= dateserial(year(date),month(date)-1,1) and 
    pu.purdate <  dateserial(year(date),month(date),1)
group by 
    pu.upc

Here, the DateSerial function is used to calculate the start date of the previous & current month, forming the date boundaries for the Purchase Date selection criteria.

2. Opening Stock

The query for the opening stock is even simpler, as no aggregation is required since, whereas a product could be purchased multiple times within a month, a product will only ever have a single closing stock figure for any given month.

As such, the Opening Stock query could be something like:

select 
    os.upc, os.quantity as osqty
from 
    [closing stock] os
where 
    os.enddate >= dateserial(year(date),month(date)-2,1) and 
    os.enddate <  dateserial(year(date),month(date)-1,1)

Here, the date boundaries are calculated to be the month previous to last month (i.e. two months ago), since the closing stock for one month will be the opening stock of the next.

3. Closing Stock.

Given the above, this should now be relatively simple - just tweaking the above query so that the date boundaries fall within the last month:

select 
    cs.upc, cs.quantity as csqty
from 
    [closing stock] cs
where 
    cs.enddate >= dateserial(year(date),month(date)-1,1) and 
    cs.enddate <  dateserial(year(date),month(date),1)

Putting it all Together

Now that you have constructed the three above queries to report on the purchases, opening & closing stock within the previous month, we can now tie all three together using one final query.

For this, we will use the Products table with a LEFT JOIN on each of the queries constructed above, since we always want every product to appear in the result, regardless of whether the product had been purchased within the previous month.

So, in pseudocode, the query is going to look something like:

select
    p.upc, 
    p.pname, 
    purchases.puqty, 
    openingstock.osqty, 
    closingstock.csqty
from
    (
        (
            products p left join purchases on p.upc = purchases.upc
        )
        left join openingstock on p.upc = openingstock.upc
    )
    left join closingstock on p.upc = closingstock.upc

We can then inject our earlier definitions of each query in this code to produce the final result (which hopefully works since I've tested absolutely none of this!):

select
    p.upc, 
    p.pname, 
    purchases.puqty as [Purchased Qty], 
    openingstock.osqty as [Opening Stock], 
    closingstock.csqty as [Closing Stock]
from
    (
        (
            products p left join 
            (
                select 
                    pu.upc, sum(pu.quantity) as puqty
                from 
                    purchases pu
                where 
                    pu.purdate >= dateserial(year(date),month(date)-1,1) and 
                    pu.purdate <  dateserial(year(date),month(date),1)
                group by 
                    pu.upc
            ) 
            purchases on p.upc = purchases.upc
        )
        left join 
        (
            select 
                os.upc, os.quantity as osqty
            from 
                [closing stock] os
            where 
                os.enddate >= dateserial(year(date),month(date)-2,1) and 
                os.enddate <  dateserial(year(date),month(date)-1,1)
        )
        openingstock on p.upc = openingstock.upc
    )
    left join 
    (
        select 
            cs.upc, cs.quantity as csqty
        from 
            [closing stock] cs
        where 
            cs.enddate >= dateserial(year(date),month(date)-1,1) and 
            cs.enddate <  dateserial(year(date),month(date),1)
    )
    closingstock on p.upc = closingstock.upc