Search code examples
ms-access-2016

Access Query to calculate last 2 Number data with condition

I need your help on this:

Data

I was thinking about this but have no idea to write it properly in access query:

Select ID, SUM(Amount) from tblRawData Where Years = '2017' and [Week num] = Max([Week Num]) - 2 < Max([Week Num] ) Group by ID

I think i need a subquery but can't think one to apply..

Any help would be appreciated!

Thanks!

Solution

  • Here is a query that should work:

    SELECT r.ID, SUM(r.Amount) 
FROM tblRawData r
LEFT JOIN (
    SELECT id, Max([Week Num]) as maxWeek
    FROM tblRawData
    WHERE Years = '2017' 
    GROUP BY ID
) w ON r.id = w.id
WHERE r.Years = '2017' 
AND r.[Week num] >= w.maxWeek-1
GROUP BY r.ID

    I've assumed that you want to use the max week for each ID. If that's not the case, then I can show you how to rework the query.

    A couple of notes about your table design:

    1) I would strongly advise you to add a primary key to your tables. It will save you an incredible amount of headache later. It is easier to delete safely with a unique ID. It will make it easier to straighten out if you ever get accidental duplicates in your data. It may even stop bad queries from messing up your data.

    2) You seem to have Years as a text field (you have the value wrapped in single quotes in your query). I would change that to a number. Numbers save space and query time, since they take up fewer bytes and are easier to index.

    3) Don't use spaces in table names, like Week Num. That is harder to read, harder to type (because you have to wrap the table name in brackets), and harder to debug. Also don't use reserved words for table names. You can escape them, but it will be a lot easier if you don't. I would use camel casing or some variant for names, in which case your fields will be amount, years, and weekNum.