CODEWITHSUNDEEP
sqlms-access
Gui
Gui

Reputation: 33

MS Access count the number of items in stock from a history table

I have a table that has a history of all changes in the serial code of each product.

tableHistory

id date        serial available
...............................
1  09/10/2020  1      True     
3  09/10/2020  2      True    
4  09/11/2020  1      False     
5  09/11/2020  2      False     
6  09/12/2020  1      True         
8  09/14/2020  1      False

tableSerial

serial sku  
............
1      100 
2      101

I have to count the number of serials in stock for a given date. Let's say I want to count the quantity for date '09/13/2020'. I expect to get:

sku    quantity
............
100    1 
101    0

I tried

select t3.sku, count(*)
from ((tableHistory as t1
left join  tableHistory as t2 on t1.serial = t2.serial and t1.id < t2.id)
inner join tableSerial as t3 on t1.serial = t3.serial)
where t2.date is NULL and t1.date<=cdate('09/13/2020') and t1.available = True
group by t3.sku

but this code only works for the most recent date. I couldn't find a way to filter the query before the join.

Upvotes: 1

Views: 99

Answers (2)

jbud
jbud

Reputation: 694

You can also try:

SELECT 
    tableSerial.sku, 
    Sum(IIf([available], 1, -1)) AS quantity
FROM 
    tableHistory 
INNER JOIN 
    tableSerial 
    ON tableHistory.serial = tableSerial.serial
WHERE 
    tableHistory.date <= #9/13/2020#
GROUP BY 
    tableSerial.sku;

Upvotes: 2

Gustav
Gustav

Reputation: 56006

You will have to sum, not count:

SELECT 
    tableSerial.sku, 
    Sum(-1 + 2 * Abs([available])) AS quantity
FROM 
    tableHistory 
INNER JOIN 
    tableSerial 
    ON tableHistory.serial = tableSerial.serial
WHERE 
    tableHistory.date <= #9/13/2020#
GROUP BY 
    tableSerial.sku;

Upvotes: 1

Related Questions