"Vertical" Percent

[RichMorrison]

I am making an aging report. There is a table of transactions with a Submitted date and a Completed date. The difference between Submitted and Completed is expressed in days.
The transactions are also grouped by the Processing Department.

A standard summary query produces a result such as:
Dept AgeInDays # of transactions
A 2 20
A 3 30
A 4 50
B 2 20
B 3 40
etc.

I want the percent of the Department total for each AgeInDays group. Using the numbers above, for Department A the AgeInDays=2 is 20 percent.

What is the simplest way to calculate the percent and print it on a report ?

Thanks,
RichM

[This message has been edited by RichMorrison (edited 05-04-2002).]

 

[raskew]

Here's an example of a vertical percentage that you
might find helpful.

It uses Orders1, a copy of Northwind's
Orders table, as a test-bed: (done to allow anyway
to replicate the scenario using common data)

The intent is to determine the number of days between
DateOrdered and DateShipped, then show the percentage
of each date span.

Query410:
SELECT DateDiff("d",[orderdate],[shippeddate]) AS NumDays, Orders1.OrderDate, Orders1.ShippedDate
FROM Orders1
GROUP BY Orders1.OrderDate, Orders1.ShippedDate
HAVING ((Not (Orders1.ShippedDate) Is Null))
ORDER BY DateDiff("d",[orderdate],[shippeddate]);

Query510:
SELECT Query410.NumDays, Count(Query410.NumDays) AS CountOfNumDays, 100*(Count([NumDays])/(SELECT Count(OrderID) FROM Orders1)) AS [%OfEntries]
FROM Query410
GROUP BY Query410.NumDays;

 

[RichMorrison]

Thanks for the reply. It is clear and looks like it should work.

But.....
we want each departments percent of its own transactions. So if department A completes 50% of its transactions in one day and department B completes 40% of its transactions in one day, then department A is performing better.

You example seems to calculate the percent across all departments. That is not exactly what I need.

RichM

 

[raskew]

Rich-

I don't have a good example at hand, but I think that if you follow the concept, and group by department, then by NumDays, it'll bring you very close to what you're after.

Bob

 

[RichMorrison]

Bob,

This is what I wound up with.
1) make a query that produces:
Department,
AgeInDays,
NumRecords.

2) make a report with a group heading for Department. Put Department in the group heading. In the group heading, calculate the total DeptNumRecords using the DSum function with a criteria to use records for the current Department. I made the "DeptNumRecords" visible, it could also be invisible.

3) in the detail line, create a text box with a calculated data source
= NumRecords/DeptNumRecords.
Format as percent.

In preliminary testing, the results are corrent.

RichM