# Thread: counting data (access 2k)

1. ## counting data (access 2k)

Hello again, Here is my problem and I am sure that there is an easy fix I just don't know how to do it.

I have a database that tracks students and dates from when they started a program. I also have a field in my query that calculates how many days have past from the time they were referred to the time that they started. I am trying to do a crosstab query that groups the number of days. For example. John Doe was referred on August 17, 2004 and started the program on September 23, 2004. 38 days later. I want to see the total number of students who started the program within the first 30 days, then the total number of students with in 60 days, etc.

can you help?

2. ## Re: counting data (access 2k)

I think you can do this without the cross tab.
A two field query.
first field would be the DateDiff(d, referDate, startDate)
second field would be studentID.

Make the query a Totals query and group on the first field and count the second field.
This will give the number of students that started on each number of referal days.

Hope this helps.

3. ## Re: counting data (access 2k)

OK, so then this groups one way now how about totaling all of the students within a selected number of days. For instance, My query came out like this: 0 days , 5 students; 1 day, 2 students and so on, How do I find out how many students are within the first 30 days?

4. ## Re: counting data (access 2k)

A second query based on the first.

first field IIF( (numberOfDays > 0 and numberOfDays <=30), 30, ( IIF ( numberOfDays > 30 and numberOfDays <=60 ), 60, 90 ) )
second field studentID

Same as before group on the first field and count the second field.
You can continue the IIF statement for as many different groups as you would like.

Good Luck

5. ## Re: counting data (access 2k)

Another possibility is to use the little known Partition function:

Partition([numberofdays], 0, 1000, 30)

and group on that. This will create groups 0-29, 30-59, 60-89 etc. If you want groups 1-30, 31-60 etc., replace the start value 0 in the function with 1. Type Partition somewhere in the Visual Basic Editor and press F1 to get more information about it.

6. ## Re: counting data (access 2k)

Thank you both for your time to help me with this. I did try both options and the partition option was the easiest. Again, thank you for your time!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•