Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting records and top values (2000(SP3))

    I need to come up with a report for a social services agency that counts the prevailing language of service for each client every month. We have come up with a clear definition of what constitutes a prevailing language of service, but now I have to figure out how to implement it. <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    If a client comes in 3 times in a month and is served in English twice and Albanian the third time, then English is clearly prevailing. If they come in 4 times and English is used twice and Albanian twice then we have a tie and have to go through some select cases to decide which will be declared the prevailing language. The select case part is fine and I can deal with that. My problem is how to count each language and determine which one is used the most and whether there was a tie.

    I considered using top values for this but it is arbitary if there is a tie. So if I create a recordset in VBA and count the occurrence of each language, how do I determine if there is a clear winner or a tie? <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Counting records and top values (2000(SP3))

    Hi Peter,
    Check out this database and see if is any help.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting records and top values (2000(SP3))

    Thanks Pat. This is a start and has helped to re-focus my thinking. I want to be careful about the number of queries I use since I have three just to set up the data for analysis. This will give me some ideas about converting what you did into SQL statements though. My main concern is determining the ties in VBA. I suspect I can get some clues from your "q3dups".

Posting Permissions

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