Report Howto Needed

[dryjeans]

I'm desiging a report to show the age of orders. Part of the report is shown below:

Age of Order

<30 | 31-59 | 60-89|

Unit 3
------------------------------------------------------------------------------------
Unit 4



I need a hand in devising a method to "roll Up" by age bracket (i.e. <30, 31-59). In other words, I know I can calculate the age of each order using a date function. The question I have is how do I then count the orders that are <30 days old, the ones that are 31-59 days old, etc, and then put the count in the correct "bucket" of the report??????

[Stewart Ross]

Hi. In summary, to show data in banded groupings you create a separate table to hold the band limits, create a new query which has the relevant order data (with the order days old value), add the band table WITHOUT joining it on any field, then include a criterion in the query to select the age band where the order days old is between the minimum and maximum for that band.

The attached Access 2003 sample DB has two tables: Order Days Banded, which stores the day bandings, and a 'dummy' order details table which is standing in for what would normally be a multiple-join Order Details query that has your calculated Order Days field.

The two queries show how the banding is done (qryOrderBanded), and an example of other uses for the banded data (counting the number of orders falling within each band in this case).

Sample SQL for the qryOrderBanded table is:

SELECT [Order Details].[Order ID], ..., [Order Details].[Days Old], [Order Days Banded].[Band], [Order Days Banded].[Band Name]
FROM [Order Days Banded], [Order Details]
WHERE ((([Order Details].[Days Old]) Between [Band Min] And [Band Max]))
ORDER BY [Order Days Banded].[Band] DESC;

and for the count query is

SELECT qryOrderBanded.[Band], qryOrderBanded.[Band Name], Count(qryOrderBanded.[Band]) AS [No of Orders]
FROM qryOrderBanded
GROUP BY qryOrderBanded.[Band], qryOrderBanded.[Band Name]
ORDER BY qryOrderBanded.[Band] DESC;
 

-Stewart

[dryjeans]

Hi. In summary, to show data in banded groupings you create a separate table to hold the band limits, create a new query which has the relevant order data (with the order days old value), add the band table WITHOUT joining it on any field, then include a criterion in the query to select the age band where the order days old is between the minimum and maximum for that band.

The attached Access 2003 sample DB has two tables: Order Days Banded, which stores the day bandings, and a 'dummy' order details table which is standing in for what would normally be a multiple-join Order Details query that has your calculated Order Days field.

The two queries show how the banding is done (qryOrderBanded), and an example of other uses for the banded data (counting the number of orders falling within each band in this case).

Sample SQL for the qryOrderBanded table is:

SELECT [Order Details].[Order ID], ..., [Order Details].[Days Old], [Order Days Banded].[Band], [Order Days Banded].[Band Name]
FROM [Order Days Banded], [Order Details]
WHERE ((([Order Details].[Days Old]) Between [Band Min] And [Band Max]))
ORDER BY [Order Days Banded].[Band] DESC;

and for the count query is

SELECT qryOrderBanded.[Band], qryOrderBanded.[Band Name], Count(qryOrderBanded.[Band]) AS [No of Orders]
FROM qryOrderBanded
GROUP BY qryOrderBanded.[Band], qryOrderBanded.[Band Name]
ORDER BY qryOrderBanded.[Band] DESC;
 

-Stewart

Stewart,

Thanks for helping me to get the ball rolling...I'll study this example and try to encorporate your suggestion.

[Stewart Ross]

Hi. Sorry to hear that you found problems with the Zip file - I checked by downloading it again myself (to another drive - it has two tables, two queries and no forms at all, just as it was sent. Is it A2003 you have? I will attach another Zip in Access 97-converted format as another try...

-Stewart

[dryjeans]

Hi. Sorry to hear that you found problems with the Zip file - I checked by downloading it again myself (to another drive - it has two tables, two queries and no forms at all, just as it was sent. Is it A2003 you have? I will attach another Zip in Access 97-converted format as another try...

-Stewart

Stewart,

I think I got it....One last question...On the report, how would I reference the count of the number of orders in the <=30 bucket??

[dryjeans]

Stewart,

I think I got it....One last question...On the report, how would I reference the count of the number of orders in the <=30 bucket??

Stewart,

I added one additional criteria...a UNIT ......So in my final table I have band, band name, number of orders, and unit...obviously, unit name (i.e. Unit 3) will appear multiple times in the table. In the report, how would I reference Unit 3's number of orders that are <=30, the number that are 31-59, etc, etc???

[Stewart Ross]

Stewart,

I added one additional criteria...a UNIT ......So in my final table I have band, band name, number of orders, and unit...obviously, unit name (i.e. Unit 3) will appear multiple times in the table. In the report, how would I reference Unit 3's number of orders that are <=30, the number that are 31-59, etc, etc???

Hi. To include the Units in the count query grouping, just add the Unit to the count query as a group-by field (the default). In your report, if you want to refer to the count of any one unit you join the count query back to the report's recordsource query. To do this without affecting the queries you already have create a new query and base it on your existing report recordsource. Add to that the order count query and join it to the report query on the unique field or fields - the unit number or unit number and order ID I guess in your case. Change your report to refer to the new query instead of its original recordsource and you can then include the count for each unit as a repeated value on each row referring to that unit in your report.

Alternatively you can use an unbound field in your report and do a DLookup to find the value from the count query, but joining the count query back for report purposes is what I would do (and is the approach I use when doing similar work, in my case for age banding of student groups rather than day banding of orders).

An advantage of using the separate day band table is that you can always add new bandings or change the min/max thresholds and names of the existing bands without altering your count query and report in any way.

-Stewart

[dryjeans]

Hi. To include the Units in the count query grouping, just add the Unit to the count query as a group-by field (the default). In your report, if you want to refer to the count of any one unit you join the count query back to the report's recordsource query. To do this without affecting the queries you already have create a new query and base it on your existing report recordsource. Add to that the order count query and join it to the report query on the unique field or fields - the unit number or unit number and order ID I guess in your case. Change your report to refer to the new query instead of its original recordsource and you can then include the count for each unit as a repeated value on each row referring to that unit in your report.

Alternatively you can use an unbound field in your report and do a DLookup to find the value from the count query, but joining the count query back for report purposes is what I would do (and is the approach I use when doing similar work, in my case for age banding of student groups rather than day banding of orders).

An advantage of using the separate day band table is that you can always add new bandings or change the min/max thresholds and names of the existing bands without altering your count query and report in any way.

-Stewart

Thanks, Stewart. I think this is EXACTLY what I needed!!