Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002

    Question Unanswered: Counting similar values by city

    I'v got a table with a "City" field and a "Application" field. The Application field has values like "Excel 97", "Excel 2000", "Office", "Office 97", "Office 2000", ect. One city may have 2 or 3 versions of the same application. I need to know the total number of applications per city for Excel, Office, Word, ect. regardless of version. My queries keep grouping the applications by version. I've tried filtering using a "Where tblApps.Application Like "Excel*"" statement but that doesn't work. Same thing with "Having ...". Can someone help me figure this out?



  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    start by recognizing that you have to strip the version number off the end of the application name

    it's tricky, because Word, Excel, Office, and Dreamweaver are all different lengths

    the way to do this is to take each name, scan from the left, find the first blank, and discard the rest

    now all you have to do is GROUP BY city and this truncated application name
    select city
         , mid(application,1,instr(application+' ',' ')-1)
         , count(*)
      from tblApps
        by city
         , mid(application,1,instr(application+' ',' ')-1)
    note that the INSTR function examines the application name with a space concatenated to the end of it -- this is for those cases where the application does not contain a blank, and INSTR on the field itself would then return 0 (search string was not found)

    having found a blank, even if it's the one concatenated to the end, subtract 1 from this position, and use that as the length parameter in the MID function to pull out the substring

    then GROUP BY and bob's your uncle

    (yes, i tested this)


  3. #3
    Join Date
    Dec 2002
    Thanks very much. That is exactly what I was trying to do. You saved me a lot of time and trouble.


Posting Permissions

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