Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Richard

  3. #3
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Richard

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
  •