Showing only summaries in subforms?

[NZArchie]

Hi guys, just to confirm, I'd like an opinion on my methods. Each advisor has a number of payments made to him, and there are a number queries on these limiting to date, payment type etc, for that advisor.

I'm trying to make a single report which presents summaries of each smaller query (total ex tax, tax, total incl tax).

Is this best achieved by grouping the main form by advisor, then placing subforms in the advisor header??

If I put them in the detail section, they turn up hundreds of times, so that's impractical.

Thanks in advance,

Arch

 

[PNGBill]

If you have the data in the report body you can have a Calculated Control in the Report Footer that displays the sum of the records fields.
This means you do not need to calculate the sum else where.

If the figure required is a little more complicated, you could Use a Public Function and have this in the Report Footer to again, just display the final result.

Study the Report Grouping and Sorting properties which are quite flexible and allow Statement Like results.
You can Monthly totals through a report or quarterly (examples) as you wish.
Final overall Totals at the end.

 

[PNGBill]

I'm trying to make a single report which presents summaries of each smaller query (total ex tax, tax, total incl tax).

Is this best achieved by grouping the main form by advisor, then placing subforms in the advisor header??

Are you talking about Forms or Reports ? some differences in how method may be required.

 

[NZArchie]

Hey, cheers for replying. Sorry it is definitely a report, and I don't want to report on any specific records in the body, only the sum amounts of each sub-query.

I currently have the report grouping on Advisor so I have the Advisor Header to put subreports in (there will only ever be one Advisor in the main forms control source). Is the report Footer a better place to put this?

 

[NZArchie]

Further to this, how can I use fields from these subreports in a calculation in the main report/another subreport? I currently have the control source as:

=Sum([SelectAdvisorNBForPeriod]![AmountIncGST])*[SelectAdvisorNBForPeriod]![NBLevy%]

Which just prompts the user for data, like it can't find the [SelectAdvisorNBForPeriod] query

 

[vbaInet]

What are the subreports for?

Can you create a mock up in MS Word for us to see what you are trying to do?

 

[PNGBill]

Is the report Footer a better place to put this?

You can have calculated controls to Sum field values placed either in the footer or header.
If you have Grouping then you can Sum in these as well.
eg I have a report on Loans for the year and it has grouping on Month so we have totals for each month and for the whole year.

On some reports eg Loans Yet to pay that were issued in a given year, the total is at the end of the report but becuse the report can be a few pages long I put a calculated control on the Report Header and this shows the totals on the top of the first page as well.

The totals do not have to be for data displayed on the report body, so long as it is in the Data Source.

None of my Reports use Sub Reports. Just Grouping and Sorting and in the odd case some Functions to supply some One Off Data.
99.9% of the what is on the report is either in the Data Source SQL or a Calculated Control.

 

[vbaInet]

You can have calculated controls to Sum field values placed either in the footer or header.
If you have Grouping then you can Sum in these as well.
eg I have a report on Loans for the year and it has grouping on Month so we have totals for each month and for the whole year.

Just to clarify:

1. you can't perform a sum or count on calculated values - the calculation has to be in the record source for the sum to work, i.e. put the calculation in the query bound to the report.
2. you can't perform a sum or count in the page footer or header - this can only be done in code.

An alternative will be DSum()

 

[PNGBill]

The totals do not have to be for data displayed on the report body, so long as it is in the Data Source.

This is where I am saying to Calculate the data must be in the Data Source. It could be in a form Control that is not visible.
Normally, in a Statement style view, you would assume a Total would be for values that were visible.

You can also have Calculated Controls in each record of a continous form.
eg =[fieldname1]+[fieldname2] will give you a 3rd value being the row total. This can be quite a long "string" if required with +'s and -'s.

They can not be sumed in the header and footer but you can repeat the process
eg you have calculated controls in the form footer
Data =Sum([fieldname1]) and Name "SumFieldName1" without""
&
Data =Sum([fieldname2]) with Name "SumFieldName2"

Next to them you have a control
Data =[SumFieldName1]+[SumFieldName2]

This means in your Form body you have two values from your query plus a value of the two added together.
In the footer you have the two values Summed and next to them you have the total of the two values.

You can have Calculated Controls in your Group Headers and Footers and Calculated Controls in the Form Header or Footer that are an overall Sum. This isn't a Sum of the earlier calculated controls but a sum of the fields themselves ie a field value can be Summed more then once.
In the Group Footer and again in the Form footer.

This gives you a Sub Total and an overall total.

 

[NZArchie]

Thanks for this

But I can't reference a different query in a text box(not the report source)??

 

[PNGBill]

Just following on... You can also do Conditional Sums.

=Sum(IIf([APLBank]="ANZ",[Principal],0))

=Sum(IIf([APLBank]="ANZ",[RefinanceAmount],0))

This then subtracts one from the other

=[ANZBankTrsfGross]-[ANZBankTrsfRefinance]

The above is reapted for a 2nd bank
and then the two banks are added together

=[ANZBankTrsfGross]+[BSPBankTrsfGross]

We have a "grid" of 9 controls that are all either =Sum() of the field or =[]+ or -[] which uses the control name.

This is in a form header and only sum's [Principal] where the bank is ANZ.

In this situation. The Form Header has a grid of controls 3 high by 3 wide that sum conditions and then adds the columns and the rows

 

[PNGBill]

Thanks for this

But I can't reference a different query in a text box(not the report source)??

A Report or Form can only have One Data Source (query/sql or table)
All controls then have to be Bound to that data, or a Calculated Control or some other unbound control which could be a function.

With a function, you can bring in related values that are not easily collected by the above data source.
This may slow your processing time although most of us may not notice it.
Try and get as much done in your query first. This can do calculations as well.

If you do need to add some data, usually in the Header or Footer where it is only needed once, then use a function.

Access has functions DLookup, DSum etc and you can create your own Public Functions.

We use fncTeamMember() which allows us to have the Operator's Name included in the footer of the report.
Only called once in the report so doesn't make sence to have it in the sql where it will need to be on every record, say 10,000 records yet is not needed in the body of the report.

Public Functions are very handy and there is a lot of support on the forum when you need it.
I regret I delayed too long before creating my first Public Function. We now have too many to poke a stick at

 

[NZArchie]

On a related note, I've just been looking into crosstab queries, which seem like they would be the ideal answer to this, apart from the fact that I can't use one as a base for a report.

Does anyone know how to refer to crosstab fields in a report?? It would be great if I could say [row heading].[column heading] as the record source for a textbox.

 

[PNGBill]

On a related note, I've just been looking into crosstab queries, which seem like they would be the ideal answer to this, apart from the fact that I can't use one as a base for a report.

You can use a Crosstab Query as a Report Data Record Source.

 

[vbaInet]

Does anyone know how to refer to crosstab fields in a report?? It would be great if I could say [row heading].[column heading] as the record source for a textbox.

In order for you to be able to use a Crosstab Query as the Record Source of your report, i.e. in order to make the fields visible, you will need to create static Column Headers using the PIVOT part of the sql. If you had a Date field, an example would be something like:

PIVOT Format([DateField],"mmm") In ("Jan","Feb","Mar");

... which would show Jan | Feb | Mar as static Column Headers.

 

[PNGBill]

We were able to replace numerous reports being exported to excel with this crosstab query which uses a Union Query as it's data source.

One multipage report, using the crosstab query, is able to produce years of financial data all sorted, grouped and displayed along with calculated controls at the click of one button.

TRANSFORM Sum(QryMonthlyReportUnionQuery.TranAmount) AS SumOfTranAmount
SELECT QryMonthlyReportUnionQuery.FinancialYear, QryMonthlyReportUnionQuery.FinancialMonth
FROM QryMonthlyReportUnionQuery
GROUP BY QryMonthlyReportUnionQuery.FinancialYear, QryMonthlyReportUnionQuery.FinancialMonth
PIVOT QryMonthlyReportUnionQuery.TranType;

In the report, Group on FinancialYear, Sort on FinancialMonth.

Here is the Union Query

SELECT TBLTRANS.TRNACTDTE AS TranDate, TBLTRANS.TRNTYP AS TranType, TBLTRANS.TRNDR AS TranAmount, Format([TranDate],"yyyy") AS FinancialYear, Format([TranDate],"mm") AS FinancialMonth
FROM TBLTRANS
WHERE (((TBLTRANS.TRNACTDTE) Is Not Null) AND ((TBLTRANS.TRNDR)<>0))
UNION ALL
SELECT TBLTRANS.TRNACTDTE AS TranDate, "CapitalDue" AS TranType, TBLTRANS.TRNPR AS TranAmount, Format([TranDate],"yyyy") AS FinancialYear, Format([TranDate],"mm") AS FinancialMonth
FROM TBLTRANS
WHERE (((TBLTRANS.TRNPR)<>0))
UNION ALL SELECT tblBankStatements.StatementDate AS TranDate, "Repayment" AS TranType, tblMemberRepayments.PaymentAmt AS TranAmount, Format([TranDate],"yyyy") AS FinancialYear, Format([TranDate],"mm") AS FinancialMonth
FROM tblBankStatements INNER JOIN tblMemberRepayments ON tblBankStatements.StatementID = tblMemberRepayments.StatementID
WHERE (((tblMemberRepayments.PaymentAmt)<>0));

By formating Finacial Year and Month as four and two digit numbers the sort order made sence.
Reports starts a new page for each year and we no longer have to worry about updating excel each January.