how big is the pie? what percent of the whole are the slices?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I can get Access to tell me how big the whole pie is, but how do I get it to
tell me what percent of the whole thing each piece represents?
My table has the details of all the individual open invoices from our
vendors. My query summarizes the total amount owed to each vendor. I can
arrange these in decending order to see who we owe the most to, but I want to
add a calculation that shows me what percentage of the total pie that vendor
represents, either within this query or in a second one that uses this first
one as a starting point. To make things even more convoluted, assuming I can
do this percentage, I'd like to see it a second way. I'd like to see them
accumulate, too.
For example, we owe Vendor A $250, Vendor B $150 and Vendor C $100. That's
$500 total. I'd like a query to tell me that 50% of our accounts payable is
owed to A, 30% is owed to B, and 20% is owed to C. Further, I'd like it to
also show me that while just A is 50% of our liability, A&B together
represents 80%, and A,B&C are 100% of our open invoices.
One further limitation -- I don't use VBA very well. I prefer to put
everything into the expression builder. Thank you!
 
Run the Report Wizard and in the summary options choose the percentages. It
will show you how to do this in a report. (Which is the final destination
for the data.).
 
Thank you, Steve. The summary options in the report wizard are an
interesting feature I had never known about. But this doesn't quite fulfill
my needs in this case. However, inspiration struck and I answered half of my
own question. Let me tell what I did and then refine my focus.

Table1 has details on vendors and open amounts.

Query1 groups by vendor and summarizes open amounts from Table1.

Query2 restates vendor and open amounts from Query1, and calculates the
absolute value of open amount.

Query3 simply does a count of vendors and a sum of absolute value from Query2.

Query4 pulls in vendor and open amount from Query2, then does a calc. It
divides the absolute value of each record in Query2 by the one "sum of
absolute value" record from Query3. (There is no join between Query2 and
Query3 in the design of Query4.)

BAM! I can now see what percent of the whole pie each slice represents. I
can even sort on the percent field in descending order. But my remaining
question is:

How can I write an expression that shows the cumulative percent of each
record in the order I've sorted them. In Excel, if the raw percents were
sorted in descending order in column A, and the cumulative were in column B,
the formulas in column B would read:
B1: =A1
B2: =A1+A2
B3: =A2+A3
etc.
How can I get Access to do this same sort of simple thing that Excel does?
 
Back
Top