Results 1 to 5 of 5
  1. #1
    mnewton367 is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Jul 2015
    Posts
    14

    Counting Records with a certain word

    So I have a subform that is basically a query of a table. I have it programmed so that when you choose a name from the dropdown box it only displays records with that name in one of three columns. I want to put something that will also display at the bottom the number of jobs completed by that person. So if you filter someone's name and it lists all the jobs they've done, I want the bottom to have a box that inputs a number which basically says 'Job A = 43', 'Job B = 24', etc. My issue is that I need it to search for 'Job A' by a single word because the job might not always be typed in the same format. So basically if the job says 'Fixed a Computer' or 'Computer was fixed' in either of two columns, I need it to search for the records that have the word 'Computer' and tell me how many records have that word.



    I'm running with a dcount right now but it has to be word for word, and in the exact same phrase, to match.

  2. #2
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    use the like criteria

    e.g. Jobtxt like "*Computer*"

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why search two fields? Probably need OR operator in the DCount filter criteria.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    I have the same question as June7...why two fields? This strongly suggests that your db is not normalized.

    I'm running with a dcount right now but it has to be word for word, and in the exact same phrase, to match.

    The best way to insure that data is entered, using a specific format, is to provide all possible inputs via a Combobox. If there are sometimes situations which are unusual, you could have one Combobox selection that simply says 'Other,' then have a Textbox to enter the non-standard response.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    While there are some design challenges that have been pointed out, you can accomplish this fairly easily. Here's the construct:

    Code:
    SELECT count(*) FROM [tableName] WHERE [Field1] & [Field2] & [Field3] LIKE "*SearchString*"
    or

    Code:
    SELECT count(*) FROM [tableName]
    WHERE [field1] LIKE "*SearchString*"
     OR [Field2] LIKE "*SearchString*" OR [Field3] LIKE "*SearchString*"
    Last edited by InsuranceGuy; 12-23-2015 at 11:55 AM. Reason: Added Field 3 and alternate approach.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Counting records, that have child records?
    By lithium in forum Access
    Replies: 4
    Last Post: 10-28-2015, 09:07 AM
  2. Counting records
    By frustratedwithaccess in forum Queries
    Replies: 4
    Last Post: 03-25-2015, 06:18 PM
  3. counting records
    By Jrw76 in forum Access
    Replies: 5
    Last Post: 01-14-2014, 02:11 PM
  4. Replies: 1
    Last Post: 11-12-2013, 10:17 AM
  5. Counting Only Certain Records
    By jtphenom in forum Queries
    Replies: 9
    Last Post: 03-29-2011, 01:25 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums