Need serious help - subreports & queries

[Sandy]

Hello -

I posted regarding this before and thought I had the answer, but it turned
out it did not work.

I need to do a summary report. My main query contains date parameters. I
thought I could just attach my main query with the parameters to the report
and then do separate queries for the subreports to get Count for various
items and because the subreport was a part of the larger report, it would
automatically take into account the date parameters. I found out that is not
the case.

I have the following query (qryOne):
SELECT tblCalls.CallDate, tblCalls.LoanType, tblCalls.NumbApps,
tblCalls.Resolved, tblCallType.CallType, tblCalls.CallTypeID,
tblDeclaration.Declaration, tblCalls.DeclarationID
FROM tblDeclaration INNER JOIN (tblCallType INNER JOIN tblCalls ON
tblCallType.CallTypeID = tblCalls.CallTypeID) ON tblDeclaration.DeclarationID
= tblCalls.DeclarationID
WHERE (((tblCalls.CallDate) Between
[Forms]![frmSummaryAndDetail].[dtpFromDate] And
[Forms]![frmSummaryAndDetail].[dtpToDate]))
ORDER BY tblCalls.CallDate;

I need the report to show a count of LoanType, Resolved, CallType,
Declaration and the sum of NumbApps. In other words for the count, I need
the report to show, eg., each item in Declaration and how many of each item
there are beside it.

If I use a query for each subreport that would be something like Select
Declaration, Count(*) as DeclCount from qryOne Group by Declaration, then I
get prompts for the subreport for the dates.

If I use a separate query for each of the subreports that leaves out the
dates, it returns all records in the query regardless of dates I put in on
the form.

I am getting extremely frustrated! Any help will be greatly appreciated!

 

[Steve Schapel]

Sandy,

I haven't tried to understand the intricate details of the report, or
why you are using subreports for this purpose. However, I would expect
that (assuming the frmSummaryAndDetail form is open at the time), you
would not be prompted for the date criteria. You are basing your
subreport on a query which itself is based on a query that references
its criteria values from the form controls, so the criteria values from
the form controls should be carried through. So, not much help, I'm
afraid, except to reassure that the problem doesn't seem to be with your
basic methodology.

 

[OfficeDev18 via AccessMonster.com]

One little detail. Is your report bound to your main query?

Sam

Hello -

I posted regarding this before and thought I had the answer, but it turned
out it did not work.

I need to do a summary report. My main query contains date parameters. I
thought I could just attach my main query with the parameters to the report
and then do separate queries for the subreports to get Count for various
items and because the subreport was a part of the larger report, it would
automatically take into account the date parameters. I found out that is not
the case.

I have the following query (qryOne):
SELECT tblCalls.CallDate, tblCalls.LoanType, tblCalls.NumbApps,
tblCalls.Resolved, tblCallType.CallType, tblCalls.CallTypeID,
tblDeclaration.Declaration, tblCalls.DeclarationID
FROM tblDeclaration INNER JOIN (tblCallType INNER JOIN tblCalls ON
tblCallType.CallTypeID = tblCalls.CallTypeID) ON tblDeclaration.DeclarationID
= tblCalls.DeclarationID
WHERE (((tblCalls.CallDate) Between
[Forms]![frmSummaryAndDetail].[dtpFromDate] And
[Forms]![frmSummaryAndDetail].[dtpToDate]))
ORDER BY tblCalls.CallDate;

I need the report to show a count of LoanType, Resolved, CallType,
Declaration and the sum of NumbApps. In other words for the count, I need
the report to show, eg., each item in Declaration and how many of each item
there are beside it.

If I use a query for each subreport that would be something like Select
Declaration, Count(*) as DeclCount from qryOne Group by Declaration, then I
get prompts for the subreport for the dates.

If I use a separate query for each of the subreports that leaves out the
dates, it returns all records in the query regardless of dates I put in on
the form.

I am getting extremely frustrated! Any help will be greatly appreciated!