report counting option buttons

  • Thread starter Thread starter kathy
  • Start date Start date
K

kathy

I can create queries that will give the count of various
option boxes. How can I put these multiple queries into a
single report? The wizard says I have chosen fields it
cannot connect.

The queries each have a start time and date, and sum of
field. The second query for each does a count of the sum
field from first query.

They work, but there must be a better way so they can all
be included in a single report.

Thanks.
 
Do you have some sample data and desired output in the report that you could
share with us?
 
Sure. I have 3 checkboxes, Track 1, Track 2, Track 3. I
would like a count of how many of each within a time
frame, daily, weekly, monthly (which will probably be user
input at prompt).

I can get 3 separate reports, using 2 queries for each.
The first queries each have a start time and date, and sum
of field. The second query for each does a count of the
sum field from first query.

I would like a report that gives all 3 checkboxes and
counts:

Track 1 Track 2 Track 3

16 57 28

Any help would be appreciated!
 
Can you try a report record source of
SELECT Abs(Sum(Track1)) As Trk1, Abs(Sum(Track2)) As Trk2, Abs(Sum(Track3))
As Trk3
FROM tblWithCheckBoxes;
 
Yes! This works for the entire database. How can the
date select be entered into a report for the user to
select the dates/times?

Kathy
 
Create a form "frmDates" with two text boxes "txtStart" and "txtEnd". Then
in your query criteria, add the expression:
Between Forms!frmDates!txtStart AND Forms!frmDates!txtEnd
 
Then how do I get this query into the report?

To get the 3 fields in the report I used as the
recordsource -

SELECT Abs(Sum([Track1])) AS Expr1, Abs(Sum([Track2])) AS
Expr2, Abs(Sum([Track3])) AS Expr3 FROM Activity;


Thanks again.
 
SELECT Abs(Sum(Track1)) As Trk1, Abs(Sum(Track2)) As Trk2, Abs(Sum(Track3))
As Trk3
FROM Activity
WHERE [YourDateField] Between Forms!frmDates!txtStart AND
Forms!frmDates!txtEnd;

--
Duane Hookom
MS Access MVP


kathy said:
Then how do I get this query into the report?

To get the 3 fields in the report I used as the
recordsource -

SELECT Abs(Sum([Track1])) AS Expr1, Abs(Sum([Track2])) AS
Expr2, Abs(Sum([Track3])) AS Expr3 FROM Activity;


Thanks again.
-----Original Message-----
Create a form "frmDates" with two text boxes "txtStart" and "txtEnd". Then
in your query criteria, add the expression:
Between Forms!frmDates!txtStart AND Forms!frmDates!txtEnd

--
Duane Hookom
MS Access MVP
--




.
Click to expand...
Click to expand...
 
Back
Top