Trouble working with formulas and dates

[laragon]

I have several people on call, and they get paid by the case and I am trying to get them a report of an average of call cases per call day each one has. The problem is that when I cannot make a formula to count the days people are on call, every time it counts the total numbers of cases.
I have tried the =count(dateofservice), I have tried grouping the records in days and adding a field with a =1 with a running sum for the group and then trying a forumula with the =count(*)/max([field]) and it hasn't worked.
The way of getting the average would be the total number of cases for an individual divided by the number of days for that same individual. I can get the total number of cases for each person, but I cannot make it get the days.
For example when I have tried it, for example: a person has a total of 6 cases in the month and on 04/18 had 2 cases and 3 on 04/20 and 1 on 04/29. Instead of getting 3 as the days the individual was on call I get the total number of cases which are 6.
Any suggestions? ideas? anything ! please..
I hope somebody can understand my problem... I am desperate....

 

[kathryn]

I'm not sure where you are trying to do this, but you could create a query that grouped on name and date and then get a count from the query.

For example, If you have Table1 with three fields, Field1, Field2, and Field3:

Joe 4/18 Case 1
Joe 4/18 Case 2
Joe 4/20 Case 3
Joe 4/20 Case 4
Joe 4/20 Case 5
Joe 4/29 Case 6

Using this SQL:

SELECT Table1.Field1, Table1.Field2, Count(*) AS Count
FROM Table1
GROUP BY Table1.Field1, Table1.Field2;

You get:

Joe 04/18/2000 2
Joe 04/20/2000 3
Joe 04/29/2000 1

The NUMBER of rows is your count of days. The sum of the Count column is your number of cases.

Let me know if that helps.

 

[laragon]

Great !!
It worked...
Thanks a lot !