Creating a report based on multiple queries

[dotolee]

I've created 4 queries - each of which just returns a total count based on certain criteria
Now I need to create a report that shows these totals based on a start / end date that the user provides.

I've tried to create a report using the wizard but it's not happy because I've included multiple queries in it. How do I do this? I'm probably doing something blantantly wrong ... i apologize in advance.
Also I need to know how to change these queries to optionally, accept a start /end date parm. I don't want to force them to have to select a date range

 

[boblarson]

Use SubReports


Bob Larson
A2K,A2K3,A2K7,SQL Server 2000/2005,Crystal Reports 10/XI,VB6, WinXP, and Vista
Free Quick Tutorials and Samples:

http://www.btabdevelopment.com

 

[dotolee]

I might be able to get away with calc totals on thereport itself.
Is there any way to get a count of all records that have a value in a column on my report?

eg)if i had the following report

DateABC Name LName XYZ
2/15/2008 John Doe asdfasdfasdf
Jane Doe asdfasdf
2/14/2008 Bob Barker oiuoiu

Could I create a summary section at the bottom with totals?
Eg) I know i can do use the control source for a text box to do something like:
= "total records" & Count(*)

which will give me a count of all records... in this case 3.
But it is possible to use the same mechanism to get a count of only those records with a value in DateABC or in field XYZ??

 

[boblarson]

You might be able to use a DCount aggregate function.


Bob Larson
A2K,A2K3,A2K7,SQL Server 2000/2005,Crystal Reports 10/XI,VB6, WinXP, and Vista
Free Quick Tutorials and Samples:

http://www.btabdevelopment.com

 

[dotolee]

I got the answer to the first question.
I'm just going to use
=Sum(IIf(Isdate(DateABC),1,0))

Now I just need to know how to pass dates to this report and have it filter the records by date.

 

[Pampers]

You can set the date criteria in your underlying report query by refering to the form where it is getting its dates from. In your query use the wizard if you build the criteria... In a criterium field you get something like this (you can also just handtype it of course):

Forms![frmYourForm]![DateStart]

Pampers
Keeping it simple can be complicated

 

[dotolee]

THe only problem with that is if I put it as a part of the sql query, i always have to pass dates.
I'd like to make the dates optional.
I guess i could build in logic into the form so that if they leave everything blank, i pass in values that I know will return all records.
Is there any other way around this?
thanks.