Sum of data based on criteria

[Vahagn]

Hello,

I have a problem with summing data based on criteria: I want the average of last available 3 figures in Data column (excluding Null) for certain UserID.

TableID UserID Year Month Data
1 1 2016 Nov 100
2 1 2016 Dec
3 1 2017 Jan 200
4 1 2017 Feb 300

The help is very appreciated.

 

[bob fitz]

First of all, "Year" and "Month" are reserved words in Access and should not be used as object names.

This seems to work using your field names:
Create querries:
Query1

SELECT TOP 3 YourTable.TableID, YourTable.UserID, YourTable.Year, YourTable.Month, YourTable.Data, DateSerial([Year],Month("1 " & [Month]),11) AS dat
FROM YourTable
WHERE (((YourTable.UserID)=1) AND ((YourTable.Data) Is Not Null))
ORDER BY DateSerial([Year],Month("1 " & [Month]),11) DESC;

AND
Query2

SELECT Query1.UserID, Avg(Query1.Data) AS AvgOfData
FROM Query1
GROUP BY Query1.UserID;

The first query is used by the second query which shows the results required

 

[June7]

Review http://allenbrowne.com/subquery-01.html.

SELECT TableID, UserID, [Year], [Month], Data, CDate([Month] & "/1/" & [Year]) AS DataDate
FROM tablename
WHERE (((tablename.[TableID]) In (SELECT TOP 3 TableID FROM tablename As Dupe WHERE Dupe.UserID=tablename.UserID AND NOT Dupe.Data Is Null
ORDER BY CDate([Month] & "/1/" & [Year]))));

Now use that query as the source for an aggregate query or a report.

Year and Month are reserved words. Should not use reserved words as names.

 

[Vahagn]

Dear June7, bob fitz

Thank you for replies, however.
I am lossed with & "/1/" & statement, Could you please explain.

P.S. I know about sub-queries, and about reserved words, this was just an example of my problem.

 

[Vahagn]

Oh, I've got it and I will try thank you