Criteria for report built with sub reports

[kentwoodjean]

I have a report that is built from 3 sub reports. I tried a Union query but due to the fields being used, was unable to do this. The one common field for all 3 reports is the date field, or "Date1". I would like this complete report to inquire once as to the [Begin Date] and [End Date]. I don't think I would use this parameter on each of the 3 sub reports so I am not sure how to set this up.

Any ideas?

 

[Trendsetter]

The main report will hold the overall information and each of the different sets of data will have their own subreports - each one will be linked by the common field "Date1" which will be used in the Parent Link Field and the Child link field for each sub report.
The start date and end date can be picked from 2 textboxes on the form used to select the report.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[glalsop]

The easiest solution might be to have the user enter the dates on a form and then reference the form fields in all of your queries.

-Gary

 

[dhookom]

Parameter queries are "so yesterday". This is especially true with subreports. Take Gary's advice. If you have questions, check

http://www.fontstuff.com/access/acctut08.htm.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[kentwoodjean]

I agree that Garys solution would probably be best. This is not a method I am used to using. I did go to the link that was referenced and found 2 things: (1) it is a great site with loads of info and I have bookmarked this site for future reference and (2) i there is a considerable amount of information on queries and parameters that I have read. I have been trying to follow the instructions and fit them for my situaton but I have a few questions which will probably make me appear a bit foolish, but here goes.

My report name is SumPendComb and it has no control source or relationship to query/table. I took a blank report in design view, and added 3 sub reports: SumRecvd, Closed and Pending. The first 2 sub reports contain the field Date1 while I have made the Pending sub report real time so date is not an issue.

I made a form per the instructions, and can easily relate the form to the query in the parameter field but I am hung up on what goes in the "Unbound" box. The directions say to build a query as normal using all fields I would like. I already have 2 queries built that feed the sub reports, both of which have the Date1 field. Do I just select 1 of the 2 as the query related to the form, and use the Date1 as the control source for the "Unbound" box and then relate the form to the 2nd query?

This is probably simple to all of you, but new to me.

 

[dhookom]

Come back with the SQL view of at least one of your subreport's record source, the name of the date field that you want to set your criteria, and the names of your form and controls.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[kentwoodjean]

OK, here is the info. Date field name is "Date1", form name is 'DateParamFrm" that contains 2 text box's for Begin Date and End Date entries but are presently unbound. There are also 2 buttons that I set per instructions, "OK" and "Cancel".
Here is the SQL of one of the 2 sub reports:

SELECT Count(tblLastUpdated.Date1) AS CountOfDate1, tblLastUpdated.Date1, RespCdeLst.RespID, RespCdeLst.RespMain, tblLastUpdated.[Resp Cd], RespCdeLst.RespCat
FROM RespCdeLst LEFT JOIN tblLastUpdated ON RespCdeLst.RespCde = tblLastUpdated.[Resp Cd]
GROUP BY tblLastUpdated.Date1, RespCdeLst.RespID, RespCdeLst.RespMain, tblLastUpdated.[Resp Cd], RespCdeLst.RespCat;

 

[dhookom]

Assuming you want to limit the records in the subreport based on the dates entered into the form controls:

SELECT Count(tblLastUpdated.Date1) AS CountOfDate1, tblLastUpdated.Date1, RespCdeLst.RespID, RespCdeLst.RespMain, tblLastUpdated.[Resp Cd], RespCdeLst.RespCat
FROM RespCdeLst LEFT JOIN tblLastUpdated ON RespCdeLst.RespCde = tblLastUpdated.[Resp Cd]
WHERE Date1 BETWEEN Forms!DateParamFrm![Begin Date] AND Forms!DateParamFrm![End Date]
GROUP BY tblLastUpdated.Date1, RespCdeLst.RespID, RespCdeLst.RespMain, tblLastUpdated.[Resp Cd], RespCdeLst.RespCat;

The form must be open when your run your report and you should have your dates keyed into the two text boxes.


Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[kentwoodjean]

Duane,

This worked great! I was also able to bring the 2nd query of 3 into the mix using the same SQL info you provided. Very close to my original but I do understand what you did.

Thanks again for your help. I am practicing now trying to better understand the power of SQL statements/Union Queriers and I will post my questions on a separate post. Thanks again.