Results 1 to 7 of 7
  1. #1
    Jammer is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2014
    Posts
    4

    Grouped Options Query

    Hello all,
    I have a pc database, on a form I created an option group "Active, In Active, All", I now the output of the group is 1,2 or depending on what is selected.



    I have a query for a report assets by employees.
    I want the report to show only active or in active or all to the report. Is this possible

    Here is the sql view:
    SELECT DISTINCTROW Assets.*, [LastName] & ", " & [FirstName] AS [Employee Name]
    FROM Employees RIGHT JOIN Assets ON Employees.EmployeeID=Assets.EmployeeID;


    Thank you

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Did you want field [Active] sorted?

    In the report, in GROUPING / SORTING
    turn ON grouping for [active]
    (you can turn on the header too, if you want to see the break where [Active] changes.)

  3. #3
    Jammer is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2014
    Posts
    4
    I want it sorted by the selection made in the group option

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Exactly. The gouping will sort.

    Or do you mean FILTERED by a selection made by the user on a form?

  5. #5
    Jammer is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2014
    Posts
    4
    SELECT [StatusId], *
    FROM Assets
    WHERE (((Assets.StatusId) Like "*" & [Forms]![View Reports]![Frame10] & "*"))
    ORDER BY Assets.AssetNum, Assets.AssetNum;

    This works, except when user selects "all"

  6. #6
    Jammer is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2014
    Posts
    4
    Sorry, yes I need it to filter active, in active or all depending on the group option selection.

  7. #7
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Or you can make a table of Filters, tFilters
    [caption], [qry]
    All, qsAll
    Active, qsActive
    InActive, qsInactive


    when the user picks the caption from the combo box,
    the list applies the query

    Code:
    cboFlt_afterupdate()
    me.rowsource = cboFlt

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

Similar Threads

  1. Exclude IDs in grouped query
    By Ruegen in forum Queries
    Replies: 2
    Last Post: 09-02-2014, 09:27 PM
  2. Retun grouped results of a query
    By cbenisch in forum Access
    Replies: 3
    Last Post: 05-28-2013, 06:27 PM
  3. Finding the Median when in a grouped Query
    By jamesborne in forum Queries
    Replies: 4
    Last Post: 02-22-2012, 08:24 PM
  4. Replies: 1
    Last Post: 10-24-2010, 07:42 PM
  5. Query to Average on Grouped Fields
    By randolphoralph in forum Queries
    Replies: 1
    Last Post: 03-16-2010, 08:03 PM

Tags for this Thread

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