Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need a report using counts from 2 or more Queries (2000)

    Is there a way to use counts from more than a single Query in calculations for a Report? ie. I have a Query which has all my records, and another Query which shows records with missing data in certain fields. I can get counts from each but need to use them both to figure percentages. Have also thought that there might be a way to store this info in another table, then do my Report based on the numbers in that table? Just unsure how to go about it. SOLUTION or IDEAS?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need a report using counts from 2 or more Queries (2000)

    One way is to use statistical domain functions such as DCount and DSum. The general form is DSum("fieldname", "tablename", "where-condition") in which the where-condition argument can be omitted.
    So you could put a text box txtCountAll in the report with control source
    =DCount("*", "qryAllRecords")
    and a text box txtCountSome with control source
    =DCount("*", "qrySomeRecords")
    A third text box txtPercentage formatted as a percentage calculates the fraction:
    =[txtCountSome]/[txtCountAll]

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need a report using counts from 2 or more Queries (2000)

    Thanks, Hans. Tried it... didn't work... came up with ERROR... I used the format =DSum([Fieldname],<tablename>) in a Text box (tried DCount, too).
    Is there another way or maybe like in the second part of my original message, a way to store the "Counts" from various reports in another table which can then be used to make a final report?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need a report using counts from 2 or more Queries (2000)

    The format =DSum([fieldname],) is invalid:
    1. The arguments of DSum, DCount etc. are strings like "LastName".
    2. You must supply the name of a table or query as second argument.

    Valid examples are
    =DSum("Amount", "tblData")
    =DCount("LastName", "qryMembers")
    assuming that Amount is a numeric field in the table tblData, and that LastName is a field (any type) in the query qryMembers.

    An alternative would be to design a Totals query that returns the counts for all records, and another Totals query that returns the counts for the filtered records. Then create a third query that combines the first two.

  5. #5
    New Lounger
    Join Date
    Jun 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need a report using counts from 2 or more Queries (2000)

    Hans, sorry I had a problem setting it up the first time. Works now. However, I have a related... deeper problem. When I work a % Incomplete formula using what you have shown me... it works fine in the Report Footer, but I cannot get it to work for each Worker in the Worker Footer section. I am attaching a demo/test that shows this. If you click on "Incomplete Sites Report" you will see the errors in the report... but the % incomplete total at the bottom is fine. Can you help with this?
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need a report using counts from 2 or more Queries (2000)

    The complete form of DCount etc. is DSum("fieldname", "tablename", "where-condition") in which "where-condition" is in SQL syntax - it is the WHERE clause of a query (without the word WHERE). Examples of valid where-conditions:<pre>"CustomerID = 37" (compare to constant numeric value)
    "CustomerID = " & [CustomerID] (compare to numeric value from form or report)
    "LastName = 'Jones'" (compare to constant text value)
    "LastName = '" & [LastName] & "'" (compare to text value from form/report)
    "LastName Like 'J*'" (comparison with wildcard)
    "OrderDate Between #1/1/2003# And #7/1/2003" (comparison with dates)</pre>

    In your case, you didn't use a valid where-condition in the percentage text box. The control source should be:<pre>=Count([Reference])/DCount("Reference","Workers","Worker='" & [Worker] & "'")</pre>

    This compares the Worker field to the Worker text box.

  7. #7
    New Lounger
    Join Date
    Jun 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need a report using counts from 2 or more Queries (2000)

    Worked GREAT, Hans !!! Thanks !!

Posting Permissions

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