Results 1 to 7 of 7
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Need Help to Remove Requirement for "Helper" (Input) Query

    Experts:

    I need some assistance with streamlining a query. Attached DB contains the following:



    - Table "tbl_JobTitleSkills" with 2 fields (50 sample records).
    - Field [JobTitle] contains 5 distinct job titles.
    - Query "qry01_Input" is a group-by query counting the number of job titles (e.g., 7, 13, 9, 15, 6).
    - Query "qry02_Output" then utilizes the 1st query as a "helper" query in order to place the 5 counts into parentheses. For example, "Business Analyst (7)".

    My question(s):
    1. Do you know of a method/way that would allow me to eliminate the need for the helper query "qry01_Input" while still producing the results in the 2nd query?
    2. If so, what does the SQL look like (for current query "qry02_Output") so that the job titles count will be placed into the parentheses "( )" based on counting directly from the table?

    Thank you,
    EEH
    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tom,

    You could combine them into 1 query, but why?

    Code:
    SELECT [JobTitle] & " (" & [Count] & ")" AS [Job Title (Cnt)]
    FROM (SELECT tbl_JobTitleSkills.JobTitle, Count(tbl_JobTitleSkills.JobTitle) AS [Count]
    FROM tbl_JobTitleSkills
    GROUP BY tbl_JobTitleSkills.JobTitle
    ORDER BY tbl_JobTitleSkills.JobTitle)

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Orange -- that's PERFECT!!! Thank you for your help on this topic.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Orange -- based on the query that you helped me with, I integrated a new function into a search form. Unfortunately, I have run into a VBA error.

    Given the new post is related to the previous post, I certainly would welcome any suggestions/recommendations you may have. The new post can be found at:
    Listbox (lstSearch.ItemsSelected.Count) results in VBA error (accessforums.net)

    Thank you,
    Tom

  6. #6
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Orange -- issue has been solved... thank you for the previous help.

    Cheers

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

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

Similar Threads

  1. Try to mitigate need of a "helper" query
    By skydivetom in forum Queries
    Replies: 3
    Last Post: 02-15-2020, 07:03 PM
  2. Replies: 5
    Last Post: 06-26-2019, 12:18 PM
  3. Replies: 6
    Last Post: 06-24-2016, 06:07 AM
  4. Replies: 2
    Last Post: 02-27-2015, 10:03 PM
  5. Replies: 4
    Last Post: 07-12-2014, 02:02 PM

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