Generating Date ranges for graphing

[emueller]

I'm missing some small piece of my puzzle to solve this issue.

I currently have a database that tracks an average number of days on a daily basis for a number of parameters when the database backs up. These daily statistical numbers archive to a table, along with the applicable date. So, for example, there will be a date with an ATDays All piece of data (along with others).

My goal for this data is to be able to create a graph from the archived data that consolidates this information into different ranges, such as in 7-day chunks for a 12-week period, or 30-day chunks for a 12-month period.

I'd like a prompt to show asking for the number of days range data to be graphed... something like Range: [Enter days range for graph], and then to utilize that data to average the data for that period for 12 cycles.

Which is where the issue comes in.

Is there a way to continually compound this range, and utilize that to average for 12 cycles in order to then have the data I need to create a graph?

Such as Avg([ATDays All], Date(), Date()-[Range]), and then utilize that range to create a second number that starts at Date()-[Range], Date()-[Range*2] and so on for 12 cycles.

This seems like it should be a simple thing to do, but the syntax has me totally stumped.

Thanks for the help!

 

[lameid]

I am not sure I completely follow what you want to do but I think the solution involves the DateDiff function to get the number of days and integer division "\" not the traditional division of "/" to group.

My pivotal question would be what is the first day? The first day would be used with datediff. You might pull that value from a form text box with a control source that uses dmin to determine the first day.

I have no idea what your result is supposed to look like, so it is hard to tell.

 

[emueller]

Thanks for asking for the clarification... I realize this might be a bit difficult to get across.

The key to this is that the data that I'm working with starts from the current date and works backwards. As I have daily data, I'm looking to get an average based upon the 12 cycles of 7 days and 12 cycles of the 30 days.

So, there'd be a range of Today to Today-7, Today-8 to Today-14, etc. to cover 12 periods.

 

[lameid]

I'm not sure of your table and field names but I'm thinking the below should work. I would replace the paramter with a form control reference.

Select (Datediff("d", ATDays.DateField , Date()) + 1) \ [Enter days range for graph] As Cycle, AVG(ATDays.FieldToAverage)
From ATDays
Group By (Datediff("d", ATDays.DateField , Date()) + 1) \ [Enter days range for graph]
Having (Datediff("d", ATDays.DateField , Date()) + 1) \ [Enter days range for graph]  <=12

 

[lameid]

Oops... The math I did will start at 0. So you either need to add 1 to the value or use <12 for the criteria.

 

[emueller]

As I expected, this would be a big function

Can you explain the "Group By" and "Having" functions (as these are things I haven't used before)? And this is going to become an actual query?

 

[lameid]

Yes it is an actual query.

The Group by and having clauses are related to Total or aggregating queries.

Group By says to group on the value(s) in the clause and is a list much like the select clause. Every field you include in your select that does not involve an aggregate function (Min, Max, First, Last, Sum, Average etc.) has to be int he group by.

Having is for criteria much like where. The difference is that Where happens before grouping and Having happens after grouping. I wasn't thinking about it before but really the Having Clause in this case could be a where clause. And really should be because grouping is more resource intensive than where (limit the records first then processs them).

In most cases you would group on an existing field instead of an expression which makes this less intuitive to read especially for learning.

Select (Datediff("d", ATDays.DateField , Date()) + 1) \ [Enter days range for graph]  + 1 As Cycle, AVG(ATDays.FieldToAverage)
From ATDays
Where (Datediff("d", ATDays.DateField , Date()) + 1) \ [Enter days range for graph] + 1 <=12
Group By (Datediff("d", ATDays.DateField , Date()) + 1) \ [Enter days range for graph]  + 1

 

[emueller]

That is a great help, although I am now rethinking my approach to this, as it it now has been decided that only a monthly average is needed. I think this will (should) be easier to implement.

To hopefully make this easier, the table that consolodates the data is "Stats", there is a [Date] field for the applicable date, and (to make this shorter) there is a daily statistic field called [ATDaysAll].

The goal is now to have a query that will average each month's ATDaysAll in order to create a graph of this statistic by month. I'd like the query to create a summary of Month and the applicable ATDaysMonth field.

I've tried the following:

SELECT DISTINCTROW Format([Date],"yyyy-mm") AS [Month],
AVG(ATDaysAll) AS ATDaysMonth
FROM Stats
GROUP BY Format([Date],"yyyy-mm");

but the query prompts me to input a value for ATDaysAll.

I feel like I'm close... but just not quite there...

 

[lameid]

I can't think of a reason you would want the distinctrow keyword. Other than that, Month is a function so you should avoid using that exact name but enclosing in square brackets is sufficient.

The prompt indicates that ATDaysAll is not in Stats.

 

[emueller]

I just solved my problem just as you replied... I don't know if it's the most efficient, but it works, by using [Stats]![ATDaysAll]

Thanks for all your help!

 

[lameid]

Change the ! to a . and you will be good.

[Stats].[ATDaysAll]