Averages

[noservice]

Hello again,

I have an interesting situation. I basically inherited an older
database that is used to enter dates, usage and charges for accounts.
The situation is that we are attempting to do averages filtered by
account, and averaged against the number of days that data has been
entered. So, for example, we had 7 months of data entered, our query
should total the number of days in the seven months (x), then average
that with the total usage number (y). This data currently resides in
the same table labeled "data" in the form of from and to dates
(6/1/1999 to 7/1/1999) and usage (320). How can I write a query that
searches the data filtered by account, checks the number of days, and
then averages it against usage?

I have tried running the query wizard, but the results are less than
spectacular. Any help with this would be greatly appreciated as I have
spent the better part of today banging my head against this "brick
wall"


Sincerely,

M. Carrizales

 

[noservice]

Alternately, if number of days is too complicated, we could settle for
doing it based on number of months.

 

[Arvin Meyer [MVP]]

I'm not sure if usage is the number of entries, or the data in a record.

For the number of days, you first need to find the first date. Do that with
a Totals query using the Min function for the first date:

SELECT AccountID, Min(DateField) AS FirstDateField
FROM tblMyData
GROUP BY AccountID;

Save that query and use it to calculate the number of days, using the Date()
function for today:

SELECT AccountID, Date()-[FirstDateField] AS DayCount
FROM Query1;

You can do the same thing for the number of days by using the Count function
in the first query.

 

[Andy Hull]

Hi

datediff("d", from_date, to_date) will give the number of days between 2 dates

so datediff("d", #1/1/2007#, #1,3,2007#) = 2

If you want to be inclusive and count the above as 3 days then just use...

datediff("d", from_date, to_date) + 1

Guessing you might want average usage per day so your query might look
something like...

select from_date, to_date, usage, usage / (datediff("d", from_date, to_date)
+ 1) as avg_usage
from MyTable

hth

Andy Hull

 

[noservice]

Thank you very much for your help. I will try this out, and let you
all know. This group is so awesome in its willingness to help those of
us, struggling to make things work in Access. Again, thank you for
your assistance.


Sincerely,

M. Carrizales