Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Posts
    23

    Unanswered: Couting the numbers of rows from a query and displaying in a report

    I have the following query:

    SELECT data.district, data.q1a
    FROM data
    WHERE (((data.district)="1") AND ((data.q1a)="At Home")) OR (((data.district)="4") AND ((data.q1a)="At Home")) OR (((data.district)="6") AND ((data.q1a)="At Home")) OR (((data.district)="7") AND ((data.q1a)="At Home")) OR (((data.district)="8") AND ((data.q1a)="At Home")) OR (((data.district)="9") AND ((data.q1a)="At Home"))
    ORDER BY data.district;

    When I run the query it returns the total # of results (In this case 18).
    I need to be able to use that # in a report.

    How can I count the total number of rows from a query and display it in a report?

  2. #2
    Join Date
    Sep 2003
    Posts
    29

    Re: Couting the numbers of rows from a query and displaying in a report

    I guess one solution would be to code the build event of the report. In this code you could set the query to be a recordset and do a recordcount that way:

    set RST = db.openrecordset("QueryName")
    aVar = RST.recordcount

    then you could use the variable "avar" in your report.

    Just a quick thought. I am sure there is a better way. For instance, you could query the query with a summation query counting the records.

    Hope this helps a bit.


    Originally posted by Bspahr75
    I have the following query:

    SELECT data.district, data.q1a
    FROM data
    WHERE (((data.district)="1") AND ((data.q1a)="At Home")) OR (((data.district)="4") AND ((data.q1a)="At Home")) OR (((data.district)="6") AND ((data.q1a)="At Home")) OR (((data.district)="7") AND ((data.q1a)="At Home")) OR (((data.district)="8") AND ((data.q1a)="At Home")) OR (((data.district)="9") AND ((data.q1a)="At Home"))
    ORDER BY data.district;

    When I run the query it returns the total # of results (In this case 18).
    I need to be able to use that # in a report.

    How can I count the total number of rows from a query and display it in a report?

  3. #3
    Join Date
    Jun 2002
    Posts
    23

    Re: Couting the numbers of rows from a query and displaying in a report

    Ok, I took your 2nd idea about querying a query, then did a count on the total # of rows and was able to return the # I was looking for. Thanks for the idea.

  4. #4
    Join Date
    Sep 2003
    Posts
    29

    Re: Couting the numbers of rows from a query and displaying in a report

    I have found that with Access, there is always more than one way to skin a goat, so to speak.

    Originally posted by Bspahr75
    Ok, I took your 2nd idea about querying a query, then did a count on the total # of rows and was able to return the # I was looking for. Thanks for the idea.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •