Report Receipt Output - 3 Monthly Totals

[grubnz]

Hi,
I was wondering if anybody can assist me with this.

I have a report I have created for Income as per the attached .xls.

At the moment it is showing values for the whole year.

What I will be doing is creating a parameter form similar to the one attached(.xls file) to select the dates as I only want on one page 6 months’ worth of data which has now lead me to a couple of issues I am not sure about.

Firstly I would like to create Interest, Comp Fees, Raffle, RSL Grant Balance etc. similar to the Balance at December 31.

Balance at March 31, Balance at June 30, Balance at September 30, Balance at December 31

The query that I have created so far to run this report on is below

SELECT DISTINCT Tbl_Main.MainDate, Tbl_Main.ReceiptCheque, [ReceiptCheque] & " " & [ReceiptChequeNumber] AS DescExpr, Sum(IIf([Description]="interest",[Amount],0)) AS INTEREST, Sum(IIf([Description]="Comp Fees",[Amount],0)) AS [COMP FEES], Sum(IIf([Description]="Raffle",[Amount],0)) AS RAFFLE, Sum(IIf([Description]="RSL Grant",[Amount],0)) AS [RSL GRANT], Sum(IIf([Description]="Membership Fees",[Amount],0)) AS [MEMBERSHIP FEES], Sum(IIf([Description]="Functions",[Amount],0)) AS FUNCTIONS, Sum(IIf([Description]="Other",[Amount],0)) AS OTHER, [INTEREST]+[COMP FEES]+[RAFFLE]+[RSL GRANT]+[MEMBERSHIP FEES]+[FUNCTIONS]+[OTHER] AS TOTAL, Tbl_Main.Reconciled, Tbl_Main.Reconciled1, Qry_TotalReceiptAndExpenditureAmounts.TotalIncome, Qry_TotalReceiptAndExpenditureAmounts.TotalExpenditure, Qry_TotalReceiptAndExpenditureAmounts.Balance
FROM Qry_TotalReceiptAndExpenditureAmounts, Tbl_Main INNER JOIN Tbl_Receipt ON Tbl_Main.MainID = Tbl_Receipt.MainID
GROUP BY Tbl_Main.MainDate, Tbl_Main.ReceiptCheque, [ReceiptCheque] & " " & [ReceiptChequeNumber], Tbl_Main.Reconciled, Tbl_Main.Reconciled1, Qry_TotalReceiptAndExpenditureAmounts.TotalIncome, Qry_TotalReceiptAndExpenditureAmounts.TotalExpenditure, Qry_TotalReceiptAndExpenditureAmounts.Balance
HAVING (((Tbl_Main.ReceiptCheque)="CARRY OVER BALANCE" Or (Tbl_Main.ReceiptCheque)="CREDIT" Or (Tbl_Main.ReceiptCheque)="NOT PLAYED" Or (Tbl_Main.ReceiptCheque)="RECEIPT"));

Also I have on the bottom of the page Fields Yearly Expenditure and Carry Over Balance as per B/S but I only want to see this on the page that contains July through to December.



Thanking anyone who can assist me with this.

 

[grubnz]

Re: Report Receipt Output - 3 Monthly Totals - Help Pls

Hi,

I was wondering if anyone can assist with this.

I have finally created a report with two subreports to accomplish my first task.

I now have an issue with the total the first three months is fine as they are totalling up as expected.

The second three months are the issue because I would like the Total to equal the first 3 months total + the second three months total.

The report is created with two queries
Rpt_ReceiptOutput1
Rpt_ReceiptOutput2

I have tried a few things that have been in this forum and many other forums but I am obviously missing something that maybe someone with experience might pick up.

I have attached an .xls file to show the report so far and also the code.

Thanking you very much for your assistance as I am at a loss.

 

[JHB]

Just a thought.
Why do not you make a grouping in your query, so for example, January, February, March get a 1 as number, April, May, June gets a 2 as number, and then build your report with group band instead of a subreport per 3 months.

 

[grubnz]

Hi JHB,

Thank you for replying, I needed someone other than myself for a different perspective.

I had created two queries this was the orginal one I was working with, is this what you are meaning?

Qry_ReceiptOutputRptQ1 made "q" , 1 and then copied this and made Qry_ReceiptOutputRptQ2 and made "q", 2. Maybe I am doing this wrong?

But what happened was it was ok for Q1 because it total together the first three months. But when I needed to put the second 3 months on the page I needed it to add the first 3months values to the second 3months values.

To make the report more dynamic for each year I will make parameter form so I can select a year.

I hope this makes sense.

Thank you once again for your assistance.

 

[JHB]

I had created two queries this was the orginal one I was working with, is this what you are meaning?

No, not quite, it should be in the same query.
I've made a small example for you with few data, then it is to hard to explain how it is done. Not hard to do, but to explain.
Open the report "QuaterQueryReport" maybe it can give you some ideas, how it can be done.
Why are you not using a cross tab query to retrieve the data, instead of Sum(IIf([....]="...",[Amount],0)) AS .... ?
An example is made in "TheTest_CrossTabQuery"

 

[grubnz]

Hi JHB,

Thank you for responding. Sorry I haven't got back to you sooner. Thank you for the example.

The reason I have used this method rather than the crosstab query was that it was giving me duplicate values when inputting two cheques under the one date. Also not holding the value headings if there was nothing under that value. Then it was suggest as per the below link to create the query manually and it fixed all my issues I had.

http://www.access-programmers.co.uk/forums/showthread.php?t=239375

I have finally got my report to work, but I don't think it was the best way to accomplish this. I am sure there are better and more efficient ways to acheive this result.

I have attached a copy of what the report looks like for the 3rd and 4th Quarter.

And the queries I created to achieve this.

Once again thank you and I hope you have a wonderful day.