Is this possible?

[Sylvialn]

Here's what I have:
(1) qryOne which contains:
Name, ID, Event, date, days
(2) qryTwo which contains:
Name, ID, Event, date, days

Each pulls up different set of data, but with the same information. I need to put both of these on a report (I know I can do this with subreports). But here's where the technical difficulties come in: I need to have a total days on the bottom (of both reports), and thenI need to total days for each different event. Here's my trouble. How can I accomplish this? I basically have to go through each record on the big report(both subreports), and total up each different event. I'm unsure how to accomplish these 2 tasks. Can anyone offer a solution? "Try everything at least once."

 

[scking]

Try creating a module level variable to store the count and, in the OnFormat event perform your counts, storing the value in the module level variable.

I've never done this but it seems like it would work.

Steve King Growth follows a healthy professional curiosity

 

[snoopy75]

For manipulations like this, I've always used domain aggregate functions as the source for an unbound text box on my main report. For example, to total up the days for just one query, you would use something like:

=DSum("[Days]", "[qryOne]", [optional where clause])

And to add up the totals for both queries, you simply use:

=DSum("[Days]", "[qryOne]", [optional where clause]) + DSum("[Days]", "[qryTwo]", [optional where clause])

I hope I've understood your problem, and been able to help in some small way.

 

[Sylvialn]

Does that code go on the main report?? And what about counting each individual event - I guess it would be the same thing, huh? Well..I can try it..thanks so much to the both of you! "Try everything at least once."

 

[mikehoot]

Have you tried Grouping and Sorting the report, by the event.

Go to VIEW, SORTING & GROUPING. Make GROUP FOOTER = True.
Then..

1 Open the report in Design view.
2 Add a calculated text box to the detail section.

How?

3 To display the property sheet, make sure the text box is selected, and then click Properties on the toolbar.
4 Set the following properties.

Property Setting
Name RecordCount
ControlSource =1
RunningSum Over Group
Visible No
5 Add a calculated text box to the group footer.
6 To display the property sheet, make sure the text box is selected, and then click Properties on the toolbar.
7 Set the ControlSource property to the name of the control in the detail section that's tracking the running total; for example, =[RecordCount].

Then add a text box to the report footer using SUM and your footer control.

 

[Jn88]

You don’t have to use subreports you could make a UNION query and base the report on that query. Hope this helps if not let me know

Jn88
Jn88@att.net