Report from two queries

  • Thread starter Thread starter TY
  • Start date Start date
T

TY

I need to count the number of tickets within a date
range. And the count of tickets where field1>3600 also in
the same date range. I have no problem creating these
queries, but how do I report these two counts on the same
report?
 
This question looks familiar.
Generally, reports don't count "tickets", they count records. If you have
two queries defined that return the values, you can use DLookup()
=DLookup("CountField","queryFirst")
and
=DLookup("CountField","querySecond")

It may be more efficient to grab the values from your report's record source
but we don't have any idea if your report is bound to any records.
 
I tried grabbing the values from the report's record
source, but the problem is the I need values from two
different queries. Can I specify two record sources for
the same report?
 
Within a report, you can use different record sets as
Row Sources of combo or list boxes
Row Sources of graph controls
Record Sources of subreports
Domains in domain aggregate functions such as DLookup() or DSum() or
DCount()
Code that creates recordsets to place values in unbound controls
Results from user-defined functions
... (there may be more)
Are you looking to add only two single values, one from each query?
 
I need to calculate the percentage of records where
field1>3600 within the date range. Say the table has 500
records. 69 of them fall within the date range, and only
2 of these 69 have field1>3600. So the percentage I'm
trying to get is 2.9%
 
Your percentages are totals. Do you need your report to only include the
totals or do you need to see details.
To get your percentage, add a text box with a control source of:
=DCount("*","tblA","DateField Between #1/1/2003# and 12/31/2003# and
Field1>3600") / DCount("*","tblA","DateField Between #1/1/2003# and
12/31/2003#")
 
Is there going to be another question ;-)
You can use references to controls on a form.
DCount("*","tblA","DateField Between #" & Forms!frmA!txtStart & "# and #" &
Forms!frmA!txtEnd & "#")
 
Back
Top