Subreport headers on multible pages

[brudderman]

I have a report with 6 embedded subreports. A couple of the subreports will sometimes flow to a second page. When this happens, I lose the header information on the subreport print on that second page. That's not critical in itself, but it would be nice to fix.

What is critical is that the first record on the second page does not show field borders at the top. I have the fields to print adjacent to each other, each with a border, to simulate grids. So when the top border is missing, the result looks very sloppy.

How can I get the field borders to print when a subreport flows to an additional page? Or if I need to do something else, I'd sure be willing to try.

Thanks for any help!

James

 

[dhookom]

You can create a new primary sorting level in the subreport based on a constant expression like:
=1
Set it to show a group header. Open the properties of the =1 Group Header and set its Repeat Section to Yes. You can then use this section as a pseudo page heading for your subreport.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[brudderman]

Thanks, Duane!

I tried this and get an error: "Multilevel GROUP BY clause is not allowed in a subquery." Then clicking on the help button for this dialog gives this: "An SQL subquery cannot list more than one field in a GROUP BY clause."

This subreport is based on a subquery in order to calculate the Year To Date total for each record.

Any other thoughts on this in the case where a subquery is involved?

James

 

[dhookom]

Could you provide the SQL of the subreport record source?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[brudderman]

Thanks, Duane.

Here is the SQL view:

SELECT b2.donor_ID, b2.TransactionDate, b2.amount, (SELECT SUM(Amount) FROM tblContributions as B1 WHERE (b1.TransactionDate<=b2.TransactionDate and b1.donor_id=b2.donor_id and b1.election=b2.election and b1.election_year=b2.election_year ) and b1.candidate=b2.candidate) AS AmtYTD, b2.election_year, Trim(tblDonors!FirstName & " " & tblDonors!LastName) & Chr(13) & Chr(10) & tblDonors!Address & Chr(13) & Chr(10) & [CSZ] AS FullName, b2.election, tblDonors.Employer, tblDonors.Occupation, tblDonors.PlaceOfBusiness, tblDonors!City & " " & tblDonors!State & " " & tblDonors!Zip AS CSZ, [PlaceOfBusiness] & Chr(13) & Chr(10) & [Employer] & Chr(13) & Chr(10) & [Occupation] AS FullData, b2.TRN_method
FROM tblDonors INNER JOIN tblContributions AS b2 ON tblDonors.ID = b2.donor_ID
WHERE (((b2.TransactionDate) Between #5/19/2004# And #6/30/2004#) AND ((b2.election_year)=2004) AND ((b2.TRN_method)<>"In-kind") AND ((b2.fk_CAM_ID)=4))
ORDER BY b2.TransactionDate;

I've specified certain things (like dates) that I'll eventually pick up from input forms once I get it all working.

I appreciate your help.

James

 

[dhookom]

What happens if you try remove the subquery and make it into a saved totals query. Then just join the totals query into the main query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[brudderman]

Thanks, Duane. I had once considered that but have the thing so tangled now that I'm not sure, with my limited understanding, that I can unravel the "inner" subquery from the "outer" query. But it seems like it might work.

If you could mark in my code, with "**" perhaps, where the subquery starts and ends, I'll give it a try.

And the how would I do the join with so many equalities in the criteria?

James

 

[dhookom]

I would create a total query:
== qtotElectionAmt ==============
SELECT TransactionDate, donor_id , election, election_year , candidate, SUM(Amount) AS AmtYTD
FROM tblContributions
GROUP BY TransactionDate, donor_id , election, election_year , candidate;
==========================

Then another query based on this and your two original tables (not sure on the syntax):
SELECT b2.donor_ID, b2.TransactionDate, b2.amount, q.AmtYTD, b2.election_year, Trim(tblDonors!FirstName & " " & tblDonors!LastName) & Chr(13) & Chr(10) & tblDonors!Address & Chr(13) & Chr(10) & [CSZ] AS FullName, b2.election, tblDonors.Employer, tblDonors.Occupation, tblDonors.PlaceOfBusiness, tblDonors!City & " " & tblDonors!State & " " & tblDonors!Zip AS CSZ, [PlaceOfBusiness] & Chr(13) & Chr(10) & [Employer] & Chr(13) & Chr(10) & [Occupation] AS FullData, b2.TRN_method
FROM tblDonors INNER JOIN tblContributions AS b2 ON tblDonors.ID = b2.donor_ID INNER JOIN qtotElectionAmt q On q.TransactionDate=b2.TransactionDate and q.Donor_ID=b2.Donor_ID and q.Election = b2.Election AND q.Election_Year = b2.Election_Year AND q.Candidate = b2.Candidate
WHERE (((b2.TransactionDate) Between #5/19/2004# And #6/30/2004#) AND ((b2.election_year)=2004) AND ((b2.TRN_method)<>"In-kind") AND ((b2.fk_CAM_ID)=4))
ORDER BY b2.TransactionDate;


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[brudderman]

I can easily understand the totals query part but am not sure about the other one. But I'll give it a try and see what happens.

James

 

[brudderman]

I'm not smart enough to get there, Duane. Here's what I have for the simplified query based on what you set out:
*****
qryAmtYTD
*****
SELECT B1.TransactionDate, B1.donor_ID, B1.election, B1.election_year, B1.fk_CAM_ID, B1.amount, Sum(B1.amount) AS AmtYTD
FROM tblContributions AS B1
GROUP BY B1.TransactionDate, B1.donor_ID, B1.election, B1.election_year, B1.fk_CAM_ID, B1.amount
HAVING (((B1.fk_CAM_ID)=4));
*****

Now what I want, I think, is to put this query (qryAmtYTD) on the design grid for a new query along with an occurence of tblContributions. I can do that, and I can select the fields I want to show (same as in qryAmtYTD). But somehow I need to join them so that as the dates increase the AmtYTD accumulates when donor_ID, election, election_Year and fk_CAM_ID are all equal.

How can I specify all of this? It will be a lot easier for me to see on the grid if that can be done.

I hate to keep troubling you with what should be simple, I'm sure.

James

 

[dhookom]

I feel like I am shooting at a moving target. Where did the fkCAM_ID field come from? Why does your query include B1.Amount? Shouldn't this just be the Sum(Amount)? Also the Having... should be a Where fk_CAM_ID = 4.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[brudderman]

>>I feel like I am shooting at a moving target.

Yes, you are--and so am I, unfortunately.

I apologize for all the confusion in trying to sort this out. Probably what I ought to do at this point is to try to construct a very simple example for myself and see if I can get that to work from what you have already told me. If I can, then surely I can expand it to the more complex problem I'm actually trying to solve. If not, I should at least have something easier to explain. I'll post again if necessary.

Thanks for all the help so far!

James