Create a list until a total is reached

[Miked13]

Hey Gang!

Been :banghead: a while trying to figure out how to do this and definitely need help.

Have a list of SKUs along with their usage percentage. Need to create a way to have Access(or Excel, depending on which is better equipped) list the SKUs that make up 80% of my usage.

let me know what else you will need.

Sorry if this has been answered previously, wasn't able to locate thread.

 

[theDBguy]

Hi. Not sure I understand the question without seeing the data involved; but in Access, it might look something like this?

SELECT SKU FROM TableName WHERE Usage>=.8

 

[Uncle Gizmo]

I'm not 100% sure about what you're looking for myself, however my guess is you want to generate a certain number of Records automatically. I covered it on my blog here:- http://www.niftyaccess.com/create-many-records/

 

[Miked13]

for my own sanity I hope this isn't as simple. I will try and let you know.

 

[Gasman]

My interpretation was

Select records whose percentage would total 80% ?

So you could sort by percentage descending and select until you get past 80% and then remove the last one. What happens though if you are at let's say 76% and the next record is 10%, but the record after that is 4% ?

Was I even close?

 

[Uncle Gizmo]

Select records whose percentage would total 80% ?

I reckon you are right!

 

[Miked13]

Originally Posted by Gasman View Post
Select records whose percentage would total 80% ?

You're correct Gasman, need to select the records that total to around 80%. Luckily the percentages are 0.06% so the jumps wont be as drastic.

"Select SKU where Sum of Percent =80%" i think something like this???

 

[CJ_London]

I have to say I don't understand the requirement

list the SKUs that make up 80% of my usage.

which 80%

lets say you have 10 records, each with 10%. So which 8 records do you want? the first 8, the last 8?

the question to the answer

SELECT SKU FROM TableName WHERE Usage>=.8

would be something like

list the SKUs that that have 80% or more usage

 

[Gasman]

Originally Posted by Gasman View Post
Select records whose percentage would total 80% ?

You're correct Gasman, need to select the records that total to around 80%. Luckily the percentages are 0.06% so the jumps wont be as drastic.

"Select SKU where Sum of Percent =80%" i think something like this???

Well as CJ_London pointed out, what would be the criteria?

I was thinking that you would want the top percentage SKUs ?, so sort descending by SKU% and process until it goes over 80%, and either remove the last processed or accept it.