80% spend from a total

[Jul B.]

I am running a monthly report to summarize the inventory & commodity spend
for our department. The way the chick prior created the queries is rather
convoluted and I never want to do it that way again, yet I can't figure out
how to do everything in Access. I hope I can explain it properly.

A file is imported into the database to summarize actual dollars spent in
the month. One of the reports summarizes total spend by vendor then shows
vendors making up 80% of the total spend. The way it was done was to export
the query results for total spend, calculate what 80% represents, then put
that figure in as a criteria limit in the total query. I've created a query
to determine the 80% spend figure but can't seem to figure out how to
determine which vendor's spend makes the 80% spend list. The top 80% might
entail 40 vendors out of the total of 60 but I can't get much futher - can
anyone help? THANKS!

 

[Ken Snell MVP]

Need more information.....

How do you "order" the vendors in terms of figuring out which vendors are in
the "80%"? For example, if all vendors spent 20% of the amount, your list
would be four of the five vendors -- but which vendor is to be left out of
the list?

Also, please provide information about the structure of the table that holds
the data to be used for the query.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Jul B.]

Hi Ken - thanks for responding. The order is by descending spend by vendor.
I created a query that sums the total spend and determines the 80% value as a
number - for example, let's use $100,000 as the 80% figure (of the total
spend). I just need to know the actual details regarding which vendors make
up that total spend.
Here is my data:

Vendor $ Spend
C 20,408.18
D 20,042.85
A 18,739.05
E 16,299.94
B 16,190.07
F 14,027.85
R 13,802.01

So, for this example, all but Vendor R would be returned in the query/report
based on a variable "target" of 80% of the total spend.

I'm a bit green when it comes to technical jargon. I know how to create
queries and the like, but am not great with VB or SQL...but I'm a fast
learner. THANKS so much for your help!

 

[Ken Snell MVP]

This is a challenging one, but I think the easiest way to do essentially
what you seek is to let a report show you the desired information.

Let's use this query as the RecordSource of the report (I'm going to use
generic names for everything):

SELECT T.Vendor, T.SpendAmount,
(SELECT Sum(TT.SpendAmount) AS TTSA
FROM TableName AS TT) AS TotalAmount,
(SELECT Sum(TU.SpendAmount) * 0.8 AS TU80
FROM TableName AS TU) AS Pct80Amt,
T.SpendAmount / (SELECT
Sum(TV.SpendAmount) AS TVSA
FROM TableName AS TV) AS PctVendor
FROM TableName AS T;


Create a report and add put textboxes in the report for each of the 5 fields
in the above query. Put these textboxes in the Detail section to start.
Assign each textbox to a field (Control Source), be sure to have different
names for the textboxes than the fields.

Go to Sorting & Grouping and put SpendAmount field in the dialog box and
sort it by Descending.

Click on textbox bound to PctVendor. In Properties window, click on Data
tab, and change Running Sum to "Over All". This textbox now will show you
the cumulative percent for the vendors.

This report now will order the vendors in descending order for spending
amount, and will show the cumulative percentage. You will be able to see the
'breakpoint' visually based on the value in the txtPctVendor textbox.


To have the R vendor not show up at all will require some VBA code to
dynamically create the SQL statement for the report. The VBA code would have
to loop through the data to identify the appropriate vendors, and use that
information to build the query. More complicated to set up, but is that what
you need over all?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Jul B.]

Thank you!

This is a challenging one, but I think the easiest way to do essentially
what you seek is to let a report show you the desired information.

Let's use this query as the RecordSource of the report (I'm going to use
generic names for everything):

SELECT T.Vendor, T.SpendAmount,
(SELECT Sum(TT.SpendAmount) AS TTSA
FROM TableName AS TT) AS TotalAmount,
(SELECT Sum(TU.SpendAmount) * 0.8 AS TU80
FROM TableName AS TU) AS Pct80Amt,
T.SpendAmount / (SELECT
Sum(TV.SpendAmount) AS TVSA
FROM TableName AS TV) AS PctVendor
FROM TableName AS T;


Create a report and add put textboxes in the report for each of the 5 fields
in the above query. Put these textboxes in the Detail section to start.
Assign each textbox to a field (Control Source), be sure to have different
names for the textboxes than the fields.

Go to Sorting & Grouping and put SpendAmount field in the dialog box and
sort it by Descending.

Click on textbox bound to PctVendor. In Properties window, click on Data
tab, and change Running Sum to "Over All". This textbox now will show you
the cumulative percent for the vendors.

This report now will order the vendors in descending order for spending
amount, and will show the cumulative percentage. You will be able to see the
'breakpoint' visually based on the value in the txtPctVendor textbox.


To have the R vendor not show up at all will require some VBA code to
dynamically create the SQL statement for the report. The VBA code would have
to loop through the data to identify the appropriate vendors, and use that
information to build the query. More complicated to set up, but is that what
you need over all?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[John Spencer]

This query should get All vendor that are FULLY included in the 80%. Unless I
messed up on the inner join and got the directionality of the comparison
wrong. >= should be <=

SELECT A.Vendor, A.DollarValue, Sum(B.DollarValue) as RunningSum
FROM YourQuery as A INNER JOIN YourQuery As B
ON A.DollarValue >= B.DollarValue
GROUP BY A.Vendor, A.DollarValue
HAVING Sum(B.DollarValue) <=
(SELECT .8 * Sum(C.DollarValue)
FROM YourQuery)

This should identify vendors that are completely in the bottom 20 percent.
SELECT A.Vendor
FROM YourQuery as A INNER JOIN YourQuery As B
ON A.DollarValue <= B.DollarValue
GROUP BY A.Vendor
HAVING Sum(B.DollarValue) >
(SELECT .2 * Sum(C.DollarValue)
FROM YourQuery)

So you can use the query above
SELECT C.*
FROM YourQuery as C LEFT JOIN QIdBottomGroup As Q
ON C.Vendor = Q.Vendor
WHERE Q.Vendor is Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Ken Snell MVP]

Ah, very good, John. I thought it should be possible by queries alone, but
didn't think of this approach. Excellent.


Typo in the first query (alias in last subquery, and poster wanted to select
vendors based on descending order for spending amount):

SELECT A.Vendor, A.DollarValue, Sum(B.DollarValue) as RunningSum
FROM YourQuery as A INNER JOIN YourQuery As B
ON A.DollarValue <= B.DollarValue
GROUP BY A.Vendor, A.DollarValue
HAVING Sum(B.DollarValue) <=
(SELECT .8 * Sum(C.DollarValue)
FROM YourQuery As C)


Typo in the second query (alias in last subquery, and poster wanted to
select vendors based on descending order for spending amount):

SELECT A.Vendor
FROM YourQuery as A INNER JOIN YourQuery As B
ON A.DollarValue >= B.DollarValue
GROUP BY A.Vendor
HAVING Sum(B.DollarValue) <
(SELECT .2 * Sum(C.DollarValue)
FROM YourQuery As C)

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/