Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sort on calculated field (97)

    Hi all. I have a report which is first is sorted by year. the next level sort I would like to perform is based on a caculated field (the control source in the text box reads: =Count([Type of study]) ). [Type of study] is a field in the underlying query upon which the report is based. I've been trying to accomplish this for some time now without success. Any help would be greatly appreciated.
    thanks for the insight, Van

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

    Re: sort on calculated field (97)

    But over what would you like to count [Type of study] ?

  3. #3
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort on calculated field (97)

    over the calandar year. is that what you mean? Let me try to expain a bit better. Each doctor requests various type of tests (studies). i would like to sort the doctors by the number of tests they order (by year).

    thanks,
    Van

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

    Re: sort on calculated field (97)

    Year is the first sorting level in your report. So you'll get the records for (say) 2000, then those for 2001, etc. The count of [Type of study] will be a fixed number within each year, so there is nothing to sort on. I think that what you want is to group by [Type of study], but sort on the number of records within each type instead of on [Type of study] itself.

    I don't think you can do that in one step. Create a totals query that calculates these counts by year and type of study, and use that as record source for a report. Create a subreport based on the individual records, put it in the main report and link by year and study type.

  5. #5
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort on calculated field (97)

    thanks Hans. I'm trying to nut it out, but no luck. I want to group by year, then by doctor, but have the doctor's sorted (from most to least) based on the number of studies they have ordered in that year. Van

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

    Re: sort on calculated field (97)

    Ah! with doctors, you introduce a new variable into the equation that you haven't mentioned before in this thread! That was what I meant in my question "But over what would you like to count [Type of study] ?" Without complete and relevant information, it is impossible to give useful answers... Maybe the following is nearer what you want.

    Create as totals query that groups on year and doctor, and calculates the number of studies. Base the main report on this query. In the Sorting and Grouping window, add year (ascending), number of studies (descending) and doctor (ascending), in this order. Specify a group header for year if you like.

    If you only want to display the counts, this should do. If you want to report the individual studies, create a subreport for them and put this in the detail section of the main report. Link it to the main report by year and doctor.

  7. #7
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort on calculated field (97)

    thanks Hans. sorry i wasn't all that clear. it's all working well now.

    cheers,
    Van

Posting Permissions

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