I have sample data in the following form (dd/mm/yyyy):
DateTransaction
InvCodeID
QtyOut
06/11/2023
YHH308B
2.5
12/10/2023
YHH308B
10
13/09/2023
YHH308B
12
09/11/2023
YHH200A
56
15/10/2023
YHH200A
22
06/09/2023
YHH100F
3
03/11/2023
YHH500F
8
I want to create a report based on a query that calculates TOTAL Inventory sold and AVERAGE Inventory sold of the InvCodeID by a rolling daily, 30-day, 90-day, 365-day period.
Any assistance appreciated, as simple query does not work, and I managed crosstab query only on a monthly basis (not a rolling 30-day period).
Depending on how much records you have to process, if you want all those rolling averages in the same query, the calculation could potentially take a long time.
Not very helpful. Wrong results? Error message? Caused computer to become sentient and tried to overthrow humanity? What does "not work" mean?
Usually people try to eat the elephant in one big gulp and choke on the scope of their problem. So, take this in small bites. Can you get the Average for 30 days?
Depending on how much records you have to process, if you want all those rolling averages in the same query, the calculation could potentially take a long time.
Not very helpful. Wrong results? Error message? Caused computer to become sentient and tried to overthrow humanity? What does "not work" mean?
Usually people try to eat the elephant in one big gulp and choke on the scope of their problem. So, take this in small bites. Can you get the Average for 30 days?
Sorry - will try to be more clear... With the simple query, I can only seem to get the sums/averages by date (so repeating InvCodeID). I wanted Each InvCodeID to be a row item, and the column item to be the 30-day, 90-day, etc., rolling period Sum and Average. I hope i was more clear now why I was looking at crosstab query instead of simple query.
I am sure there is a simple way of doing this - and I am overthinking it...
A daily average requires a different query than a 30-day average.
However, you can combine several queries into one query.
Step by step - the execution itself is easy.
The difficulty is due to the multiple time period in the same question. You have to divide and conquer this problem because to give you separate numbers, you need to do separate computations.
Unfortunately, that is exactly what it must do because all of your data resides (correctly) in one table. Your problem is an expectation of being able to do something that you cannot actually do (i.e. not touch ALL the data). In order for Access to compute the total and average values for a given period, you have to touch everything to see if it is in or out of the time range. So you (well..., Access) must touch everything to determine if it is in the current grouping of interest.
This is a divide-and-conquer problem. There are two ways to approach it. One is via complex sub-queries where you compute the totals and averages in multiple sub-queries. In this case, that might be more complex than you want. (Just guessing.)
The other method is to do a computation on the date to decide whether the date is or is not within a given range. If you could write a query to do the SELECT portion to include "flags" for "In last 30 days", "In last 90 days", "In last year" as part of the record, then you would have something for the crosstab to "grab" on. And you can do crosstabs of queries as well as of tables.
The IS/IS NOT computation might be so simple as an IIF based on a DateDiff operation in days, like
And of course, DateDiffs in days for 90 and 365 days as well. So have your other fields in the query but also have these. There are other ways to do this, I'm just introducing the concept. Which is that if you want Access to compute something, you have to TELL it how to do so. And in your case, you have THREE things to be computed: Is last 30 days, Is last 90 days, Is last year. If you compute these things FIRST, then the crosstab has something on which to base the various things you are trying to identify.
I would do this with a subquery. The formula for an average is the sum of the total value divided by all the instances that went into it. So instead of using the built in AVG formula I would calculate the sum for the range you want and then count the number of instances that fall into that ran for a Code.
Every date range you want could be put into this one subquery, I have just done the 90 days as an example:
Code:
SELECT YourTable.InvCodeID, Sum(IIf([DateTransaction]>(Date()-90),[QtyOut],0)) AS num90, Sum(IIf([DateTransaction]>(Date()-90),1,0)) AS den90
FROM YourTable
GROUP BY YourTable.InvCodeID;
Then in the main query we can do the division and get our final results:
Code:
SELECT subA.InvCodeID, IIf([den90]>0,[num90]/[den90],0) AS Avg90
FROM subA;
Again, add more date ranges to the sub query and then you can repeat it in the main query. And like dbguy said, this could take a while to process depending on the amount of data.
If you're saying each InvCodeID is shown only in a single row, then that won't be a "rolling" sum or average. If that's what you want but still have it based on a rolling count, then you'll have to create those rolling queries first and then use them to create your final query based on those rolling queries to get your single row per InvCodeID with the rolling values.
I would create a separate table called say 'tblPeriods' with a single number field populated with your period length i.e. 1, 30, 60 and 365
Assuming your table contains a transaction date and something to group on such as an item then your query would be something like
Code:
TRANSFORM Avg(amount) AS avgAmount
SELECT myTable.item
FROM myTable, tblPeriods
WHERE myTable.tranDate>=DateAdd('d',-[period],Date())
GROUP BY item
PIVOT tblPeriods.Period
benefit - no need to recode if your periods change - just modify the records in tblPeriods
will produce something like this (nothing for 1 day as there were no transactions for today in my example data)
Query4 Query4
