Easy way to calculate average number of daily records (1 Viewer)

[gojets1721]

I have a query of complaints YTD. On a report, I'm trying to code a field to show the average daily number of complaints? Any suggestions on how to code that? I'm having a brain fart on how to do that tbh

 

[plog]

Depends on if we are counting or summing to get total complaints:

=SUM([Complaints])/DatePart("y", Date())

 

[gojets1721]

Depends on if we are counting or summing to get total complaints:

=SUM([Complaints])/DatePart("y", Date())

We would be counting

 

[Pat Hartman]

Count(*) / dCount("*", "qDistinctDays")
You need a query with the count of distinct days.

 

[arnelgp]

create first a Query that will Count How Many Days you already has (YTD).

1. qryNumDays
SELECT COUNT("1") AS NumDays FROM (SELECT [DateField] FROM yourTable GROUP BY [DateField]);

2. qryAverageDailyComplaint:

SELECT DSum("ComplaintQtyField", "yourTable") / DLookup("NumDays", "qryNumDays") As AvgDailyComplaint;

 

[gojets1721]

create first a Query that will Count How Many Days you already has (YTD).

1. qryNumDays
SELECT COUNT("1") AS NumDays FROM (SELECT [DateField] FROM yourTable GROUP BY [DateField]);

2. qryAverageDailyComplaint:

SELECT DSum("ComplaintQtyField", "yourTable") / DLookup("NumDays", "qryNumDays") As AvgDailyComplaint;

Gotcha. However, step one isn't working for me. I think it's because my date field also includes the time. It's formatted like: mm/dd/yyyy 00:00am/pm

 

[MarkK]

If there is one row for each complaint, then to count your complaints YTD you would do...

DCount("*", "ComplaintTable", "ComplaintDate >= DateSerial(Year(Date()), 1, 1)")

If you want an average per day over the entire YTD period, you would divide by...

DatePart("y", Date())

If you need to count distinct days for your average, then drop the time using the DateValue() function, and save this as query qDistinctDays....

SELECT DISTINCT DateValue(ComplaintDate) FROM ComplaintTable;

Then divide by...

DCount("*", "qDistinctDays")

hth

 

[The_Doc_Man]

I think it's because my date field also includes the time.

SELECT COUNT("1") AS NumDays FROM (SELECT [DateField] FROM yourTable GROUP BY Date([DateField]));

Use the DATE() function to strip the time from the date field.

 

[arnelgp]

Gotcha. However, step one isn't working for me.

you can use DateValue:

SELECT COUNT("1") AS NumDays FROM (SELECT DateValue([DateField]) As Expr1 FROM yourTable GROUP BY DateValue([DateField]));

 

[gemma-the-husky]

You probably want the date range for the report to evaluate the total day count.
I don't think you would want to just count the number of days with problems. But you may want to remove weekends and BH days.

eg If you have problems this week on Mon, Wed. Thu is that 3 days or 5 days (with Tue and Fri). You will know.
So the days count might be enddate +1 - startdate

 

[gojets1721]

You probably want the date range for the report to evaluate the total day count.
I don't think you would want to just count the number of days with problems. But you may want to remove weekends and BH days.

eg If you have problems this week on Mon, Wed. Thu is that 3 days or 5 days (with Tue and Fri). You will know.
So the days count might be enddate +1 - startdate

That is correct. The above solutions are only counting days where problems occur. I'm looking simply a total count of all days from January 1 to today.

 

[MarkK]

Read post #7 more closely.