Results 1 to 6 of 6
  1. #1
    SilverSN95 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2009
    Posts
    11

    Count distinct records in parameterized query

    Hello,


    My database includes these tables:
    Persons_Completed with an ID field (name + date completed primary key).


    Data table with all entries linked by ID. Persons_Completed(one) ->Data(many).

    My problem basically boils down to this:

    I have a report that is using a parameterized query that is based on a join of the above two tables that is not totaled/grouped by in any way. I want a count in the report of grouped by ID as this will signify the number distinct records returned from the parameters. I cant get this directly because I need the data ungrouped for the report.

    I realize this is a fairly common request (I’ve looked at SELECT DISTINCT), however I can’t figure out how to have this "total records" field use the same parameters that are entered when the form is opened without having to enter them in multiple times over.

    Do I need to do something in vba to forward the parameters, a sub report, or can this simply be done with a correct query?

    Thanks again for the help.

    Side note: I posted a thread related to this db a couple of weeks back, but I think it would be more work to make all of the information relevant to this question apparent so I hope starting a new thread isn’t an issue.

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    as you are obliged not to group in the report; then I would group & count by using a new & separate regular aggregate query on the same record set that is the record source for the report. Every Access textbook has examples of the aggregate query (sum, count, max, etc). Use this new aggregate query as the source for a subreport that can be inserted into your main report.

  3. #3
    SilverSN95 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2009
    Posts
    11
    This count needs correspond to the parameters of the query that the original report is being run on. Will that query run on the same set of data the original report is using?

    For example, the parameters for the report query are a date and level range that the user inputs. The count would then be the distinct records (by ID) in this specified range. I think I had tried something close to what you are describing, but I as prompted for the same parameters many times.

  4. #4
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    a query(2) that uses another query(1) as its record source, will report only on the data within that query(1).

    a parameter query(1) will trigger the parameter prompt when you run query(2) - I think - - I actually rarely use parameter queries. Try it and see. If it does - and you don't want it to - - then change approach away from a parameter query to instead putting the necessary parameter/criteria of query(1) into a form's textboxs and having query(1) call the criteria: i.e. Forms!FormName.TextBoxName

  5. #5
    SilverSN95 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2009
    Posts
    11
    NTC,

    I created a query (lets call it B) that looks like this

    SELECT Count(GroupedbyID.ID) AS CountOfID
    FROM GroupedbyID;

    and GroupedbyID is just a groupedby query of my original parameter query A. When I added B as a subreport into my report based on A, I was promted several times for the same parameters.

    You mentioned having the parameters feed from a textbox. Can I do this kind of thing from a report? I do have fields on the report that capture the input parameters, but I don't know if/how I can use these for the subquery.

    Thanks again.

  6. #6
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    one must separate apart your issues to be clear really what is occurring.

    1. any report that opens will throw a parameter prompt for a control that is on the report - but doesn't exist in the record source.

    2. a query that opens will throw a parameter prompt if it has a parameter in the query design.

    these 2 things are very similar; you must be sure which is occuring. make sure there is no parameter prompt in any query of your record source temporarily - and open your report....if it continues to throw parameter prompts then you have controls on there that need to be deleted (and this can include in the sorting/grouping set up or any VBA code that is firing on open). resolve this.

    a query can not call a variable from a report. to avoid having a parameter prompt as part of a query - instead prompt for the variable in a form...then call the data with the query from that form.

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

Similar Threads

  1. Query for distinct member number
    By Lauri in forum Queries
    Replies: 1
    Last Post: 08-23-2013, 03:22 AM
  2. Replies: 7
    Last Post: 07-22-2010, 01:14 PM
  3. Count of records
    By Bruce in forum Forms
    Replies: 3
    Last Post: 03-22-2010, 01:30 PM
  4. Replies: 2
    Last Post: 11-18-2009, 06:49 PM
  5. Replies: 0
    Last Post: 08-08-2008, 08:34 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