Need some math help for a report.

[odin1701]

I'm not sure if this is possible or not. I don't think that it is.

I need to find the average number of days that an account has been entered. All I have is the date it was entered and of course the current date.

Now here's the interesting part. I need average days in the system for accounts that are 0-15 days only, 16-21 days only, etc.

There isn't an operation that I can do that would come up with that that I can think of. If the days in the system were stored in a field though it would be simple, so I'm probably just going to create a field in one of my tables to store that information. But even then I'm not sure that will work. Can a field store an expression like =(Date() - DateEntered) and calculate that?

 

[steve711]

Are you using a query to generate your report? You can do a count entries within the query and then average from there.

 

[odin1701]

Are you using a query to generate your report? You can do a count entries within the query and then average from there.

Yes I am doing that. I could give an average for the entire set of data or an average of the number for a particular date range. I.E. an average of how many accounts are 0-15 days old, etc. But I need the average number of days, not the average number of accounts.

 

[tim419762]

If you are open to having a single query for each time frame, turn on totals and then add the date field twice. You should end up with two fields with the record source being today()-date_created or something like that. In the totals row, set one to Avg. In the other, set it to Where. In the criteria for the field where the total is Where, put >0 and <15.

This should give you a query that shows the average days aging for accounts that are less than 15 days old.

You'd have to build multiple queries, but I'm guessing that these periods are relatively set. You could then use subreports or subforms to display all the results in a single location.

 

[odin1701]

If you are open to having a single query for each time frame, turn on totals and then add the date field twice. You should end up with two fields with the record source being today()-date_created or something like that. In the totals row, set one to Avg. In the other, set it to Where. In the criteria for the field where the total is Where, put >0 and <15.

This should give you a query that shows the average days aging for accounts that are less than 15 days old.

You'd have to build multiple queries, but I'm guessing that these periods are relatively set. You could then use subreports or subforms to display all the results in a single location.

That should work, but the problem is that I can't really have 15 subforms on a single line...They would each need a different control source and then have to be linked to another query....because this is for an employee summary. It has to filter for an employee, date range, determine the number of accounts in a certain status and the aging of each account. It's a pretty complex report that already has two subreports and uses 3 queries already. It's kind of a pain to work with.