Results 1 to 4 of 4
  1. #1
    dsajones is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2012
    Posts
    19

    Expression Builder - Equivalent of Select Distinct

    Hi. I've got a report that feeds off a query. In that query I generate rows from a child table *similar* to order items from an order. So each row will have an ID that would link it back to the parent table e,g, order number if this was orders and order items. In the report I'm producing grand totals based on the total of all records returned in the query. But I also want to get a unique count e.g. how many *actual orders*. Below is a simple example. My summary report totals would be for each Type, multiply days by cost and days by saving. That's all working fine. But in this example I've got 2 records for ID 1 and 1 each for IDs 2 and 3. So the total count is for unique IDs i.e. 3 in this case. How do I build an expression based on =Count(ID) but for unique values?

    ID Type Days Cost Saving
    1 A 45 67.89 3.56
    1 B 32 8.90 0.00
    2 A 56 23.00 2.00
    3 A 37 10.00 .98


  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you use a separate query

    SELECT Count(*) FROM (SELECT DISTINCT ID FROM myTable)

    to display on your form or report, consider using a listbox set to one row high with the sql as the rowsource

  3. #3
    dsajones is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2012
    Posts
    19
    Thanks for getting back to me. I've tried your suggestion and the actual text looks like this in Expression Builder:

    = SELECT Count(*) FROM (SELECT DISTINCT [FrameWork_ID] FROM [qry_Updates])

    But when I click OK on the expression builder I get a syntax error saying "Check the sub-query's syntax and enclose the sub-query in parentheses". But the sub-query is already enclosed in parentheses. So I added opening and closing parentheses to the whole statement making it look like this:

    = (SELECT Count(*) FROM (SELECT DISTINCT [FrameWork_ID] FROM [qry_Updates]))

    The error message disappeared when I clicked OK so I then ran the report. In the total field where the result of this query should be displayed I'm now getting #Name? Any idea what might be going wrong?

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    no - you use a separate query, you cannot use query sql syntax in a controlsource.

    As suggested change your textbox to a listbox and put your sql in the rowsource

    alternatively create a separate query called say qryUpdateCount - note you'll need to modify to include an alias for the count value

    SELECT Count(*) AS UpdateCount FROM (SELECT DISTINCT [FrameWork_ID] FROM [qry_Updates])


    and in your textbox controlsource put

    =dlookup("UpdateCount","qryUpdateCount")

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

Similar Threads

  1. Replies: 6
    Last Post: 05-05-2020, 11:21 PM
  2. Replies: 4
    Last Post: 05-03-2016, 01:51 PM
  3. Replies: 2
    Last Post: 11-20-2012, 03:21 AM
  4. SELECT DISTINCT or equivalent
    By kpo in forum Programming
    Replies: 13
    Last Post: 05-20-2012, 01:45 PM
  5. Replies: 8
    Last Post: 04-21-2011, 05:29 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