Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122

    Question Unanswered: Counting with SQL

    I need to use just SQL to access an MS Access DB and count the numberr of ocurences of a string in a field as part of a larger UNION query.

    Can this be done and how?

    I've tried playing with Like *word* but that just returns the records with that string somewhere but I need to count them.

    ?
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  2. #2
    Join Date
    Mar 2004
    Location
    Brighton, UK
    Posts
    25
    Well I’m not sure this is exactly what you’re after but it may help a bit. You could set one of the columns in your query to being:

    MyCount: IIf(instr([FieldName], strFindWhat),1,0)

    Which would tell you if there is a match in that field or not, but it sounds as if you've already got something that does that. You could elaborate on the above if there are 2 or more different things to find in each record:

    MyCount: IIf(instr([FieldName], strFindWhat1),1,0) + IIf(instr([FieldName], strFindWhat2),1,0) + IIf(instr([FieldName], strFindWhat3),1,0) etc etc

    But this doesn’t tell you the number of matches in each field, only if there is or not. You could write a function which counted the number of matches given a string to search and string to search for, eg:

    MyCount: CountMatches([FieldName], strFindWhat)

    But I'm not sure you could do this if you aren't using Access to run the SQL itself. If none of this helps let me know as I'm sure there must be a way of doing it, maybe putting the search criteria into a temporary table and doing some weird IIf statements using IN ...

    Hope this is of some help.

  3. #3
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122

    Things that make you go: hmmmm

    hmmm

    Some food for thought but you are right about the IIf I am useing

    "Where [field] like *STRING*"

    which gives only the records with at least one example of the search text in the field. ideally I'd like to count at least the first five (in which case if more than four count = lots (5) ). It's not VITAL just very important.

    Thanks for the help so far.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    depending on your version of access, you might have the REPLACE function available

    it's not available in access 97, which is all i have

    you might also check this out, and see if you can use it -- http://www.techonthenet.com/access/f...ng/replace.htm

    anyhow, the technique is to replace each occurrence of the search string with an empty string, i.e. remove it

    now what you need are the length of the original string, the length of the resulting string, and the length of the search string

    example:

    original string: "peter piper picked a peck of pickled peppers"

    length: 44

    search string: "pe"

    length: 2

    resulting string length: 34

    therefore the original string had (44-34)/2 = 5 occurrences of "pe"

    so the sql would be something like

    PHP Code:
    select (
           
    len(origstr
         - 
    len(replace(origstr,searchstr,''))
           )
         / 
    len(searchstr)  as occurrences
      from 
    ... 
    Last edited by r937; 04-05-04 at 07:18.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122

    Unhappy

    Brillient...?

    wouldn't that result in loss of data?
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, dude, you're not updating, you're just selecting

    SELECT ANYFUNCTION(SOMETHING) can produce absolutely anything, without changing what's in the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    ok then

    NICE!

    I'll have a play with this later, thankyou.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

Posting Permissions

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