CODEWITHSUNDEEP
sqlms-access
Brennie Khaw
Brennie Khaw

Reputation: 1

Getting previous month data

I have a transaction records table in ms-access with item field, date field and quantity field. I would like to add a new column, with transaction quantity for the previous month. For example, I have a row that looks like this: 

+----------------------------+
| Item A | 1/11/2018 | 8,000 |
+----------------------------+
| Item A | 1/12/2018 | 7,000 |
+----------------------------+

And I want to add in a new column that shows previous month's transaction quantity, which will look like this:

+------------------------------------+
| Item A | 1/11/2018 | 8,000 | -     |
+------------------------------------+
| Item A | 1/12/2018 | 7,000 | 8,000 |
+------------------------------------+

Can anyone suggest how do I query this in access?

Upvotes: 0

Views: 160

Answers (1)

Venkataraman R
Venkataraman R

Reputation: 13009

In MS Access, you can do something like this. MS Access does not have LAG function, as similar to T-SQL. 

SELECT ItemA, Date, Quantity as currentQuantity, 
(SELECT top 1 Quantity FROM Table1 new WHERE new.Date = dateadd("m",-1,tbl.Date)) as previousQuantity
FROM Table1 tbl 
ORDER BY tbl.Date

Upvotes: 1

Related Questions