Continuous subreports calculating percentages

[Saners]

My main report prints one record per page. In the detail section there are 2 continuous subreports. Subreport 1 shows how many items for sizes 1, 2 and 3 were available. Subreport 2 shows how many items for sizes 1, 2 and 3 were ordered. In subreport 2 the 3 sizes % ordered should be 100%. It is for Size 1 (4/4 * 100) but it is calculated incorrectly for size 2 (650 i.e. 26/4 * 100) and for size 3 (175 i.e. 7/4 * 100). It is using the total for size 1 to calculate all percentages. I want to use the correct total on sub report 1 to calculate the % by size.

The Total fields on both subreports are called txtTotal. The % ordered field on subreport 2 is a text box with the following control source:

=IIf(Reports!rptMonthlyOrders!rptsubTotals.Report!txtTotal>0,
(Round((Reports!rptMonthlyOrders!rptsubOrdered.Report!txtTotal/Reports!rptMonthlyOrders!rptsubTotals.Report!txtTotal)*100)),Null)


1. The main report is rptMonthlyOrders

2. Subreport 1 is rptsubTotals and prints as:

Size Total

1 4
2 26
3 7


3. Subreport 2 is rptsubOrdered and prints as:

Size Total %ordered

1 4 100%
2 26 650%
3 7 175%

The subreports are linked to the main report by an ID field. Can anyone please help me calculate the % correctly for each size (i.e. each record on subreport 2).

All suggestions appreciated.

Thank you!

 

[dhookom]

I would completely ignore subreport 1 from subreport 2.

Figure out how to get the number of available by size into the record source of subreport 2. You haven't provided any information about your tables and fields so we can't help you.

Duane
Hook'D on Access
MS Access MVP

 

[Saners]

Thanks for the reply Duane.

I have tried to add the total available by size to the record source of subreport 2 without any luck. Subreport 2 is built using the following SQL Query:

SELECT Size, CompanyID, Count(CompanyID) AS [Ordered Total]
FROM tblMonthlyOrders
WHERE Ordered=1
GROUP BY Size, CompanyID;

The SQL query to get the total available by size would be:

SELECT Size, CompanyID, Count(CompanyID) AS [Total Available]
FROM tblMonthlyOrders
GROUP BY Size, CompanyID;

The linked field between subreport2 and the main report is the CompanyID.

I know I may need a ‘UNION’ to get the total available into the recordset by size but I don’t know how. Any suggestions?

The fields on table tblMonthlyOrders are:

RefNo Autonumber
CompanyID Number
Size Number
Ordered Yes/No

 

[dhookom]

It looks like you could join the queries since they are both grouped on CompanyID and Size.

Duane
Hook'D on Access
MS Access MVP

 

[Saners]

I know a union will get all the ordered records followed by the total available.

I would really appreciate it if you could show me how to query the data to get it in this format:

Size
No Ordered
Total Available

So I can then work out the %.

Apologies if I am missing the obvious and many thanks for your help.

 

[dhookom]

Doesn't this get what you want?

SELECT CompanyID, Size, Sum(Abs(Ordered)) as OrdTotal, 
Count(*) as AvailTotal, Sum(Abs(Ordered))/Count(*) As OrdPct
FROM tblMonthlyOrders
GROUP BY CompanyID, Size;

Duane
Hook'D on Access
MS Access MVP

 

[Saners]

It worked perfectly. Thank you so much.