Query top % of value possible??

[SueK]

Is there a way to retrieve 80% of records by the value, instead of record count? For instance, given a list of late orders, I want to review the ones that cause 80% of the total value of late orders.

In Excel, I'd sort descending, create a column with % of total value, then an additional column with a running total of the %'s and either manually cut off or filter to the top 80%.

Can this be done in Access at all?

 

[MarkK]

Yeah. You need to write a loop, and you could add records to a listbox...

1) calc the total
2) Open a recordset that sorts descending, 
3) calc running sum, and if current record qualifies (running sum < 0.8 * total (from 1)) 
   a) add to listbox
   b) move to next record
   c) goto 3)

...but you could do any number of things in that loop, like in 3a) you could flag the record rather than add it to a list, and then open a query of just the flagged records, and so on...
hth

 

[sneuberg]

I think you might be able to do it about the same way you do it in Excel. Start out by creating a query that selects the records that are late. Let's call that qryLate. Save that and use that in an aggregate query that just gets the total value. Let's call that qryTotal. Then put qryLate and qryTotal in a query without a join. Since qryTotal only has one record its field will just be repeated for every field in qryLate. Create an expression in this query that calculates the percent of total. Let's call this query qryPercentTotal. Save this query. Probably wouldn't hurt to close it and reopen it. Now add another expression to this query that uses DSum to create an running sum. Final you would add criteria to select the records with a running sum over 80%.

 

[SueK]

Now add another expression to this query that uses D Sum to create an running sum. Final you would add criteria to select the records with a running sum over 80%.

This actually what I've tried and I currently have a query that shows the data with a column for the overall total and column for % that divides the line by the overall total (QryLate). Oddly, I had to query that query in order to get it to descending by %, but I do have that (Qry%Sort).

I can't get DSum to work, though. I may be using it entirely wrong, I've never used this type of function before. What I'm trying looks like

DSUM([QryLate]![percentage], [Qrylate])

When I try to run, I get a pop up asking me to enter QryLate, as if it's a field and running from there results in error. Any idea where I'm going wrong? I tried using the builder and it enters [QryLate] in this manner as well, though I suspect that's where my problem is.

Thank you!

 

[SueK]

Yeah. You need to write a loop, and you could add records to a listbox...

1) calc the total
2) Open a recordset that sorts descending, 
3) calc running sum, and if current record qualifies (running sum < 0.8 * total (from 1)) 
   a) add to listbox
   b) move to next record
   c) goto 3)

...but you could do any number of things in that loop, like in 3a) you could flag the record rather than add it to a list, and then open a query of just the flagged records, and so on...
hth

Can I use a table instead of a listbox? If so, I have a query that selects my records, and adds fields for total value and % of total. I would need to start at record one, set the value, and move down tot he next and set value there. Am I understanding correctly?

If so, how do I write a macro (assuming this is the right tool?) that looks at a prior record to add to a current record to create the accumulating total? I can do a SetValue, but don't know how to refer to the prior record.

Thank you!

 

[sneuberg]

The fields need to be in quotes so if you put in an expression like:

Running Sum: DSUM("[percentage]", "[Qrylate]")

that should at least get you the sum. If you get that working then trying adding criteria to make it a running sum. You could start by trying

Running Sum: DSUM("[percentage]", "[Qrylate]", "[percentage] < " & [percentage] )

that assumes [percentage] will be look at as numeric by Access. If that's not the case and it's text then try it like:

Running Sum: DSUM("[percentage]", "[Qrylate]", "[percentage] < '" & [percentage] & "'" )

If you can't get that working if you upload your database I'll see what I can do.

 

[sneuberg]

By the way the DSum will slow the query down considerably but it may still be ok if there are not too many records or at least too many viewed at once. If it turns out this is too slow you may have to do this in a manner like MarkK suggested.

 

[sneuberg]

Can I use a table instead of a listbox? If so, I have a query that selects my records, and adds fields for total value and % of total. I would need to start at record one, set the value, and move down tot he next and set value there. Am I understanding correctly?

I think this method would be easier with a table too. I suspect MarkK suggested a listbox as that wouldn't cause bloat. With a table your database will increase in size by the size of the table you create each time you run this code. You can only get the space back by compact and repair but if this temp table is in the frontend you can just compact and repair on close to solve that problem.

I suggest that you continue to try the query method before going to this. Even if it turns out to be too slow you get the learning experience

 

[SueK]

I suggest that you continue to try the query method before going to this. Even if it turns out to be too slow you get the learning experience

I agree with you totally! I want learn both, as you never know what you'll need next time, right!?

Right now, the query method sort of seems like it might have worked. It was painfully slow to run, saying Access was not responding, but left to sit there I did get results! What I'm seeing is odd though, as the top record is 89.51 and it descends from there, where I would have expected it to ascend. I wanted to see if that was basically the total - the top items 5, but when I tried to scroll, it's locked up again. It's only 378 records, so I'm concerned it's having so much trouble, but I'm determined to see if it'll work and understand why.

 

[sneuberg]

I don't think 378 records should be causing this kind of slowness. I've done this kind of thing before with a lot more and have had acceptable results. I'd like to see this. Can you upload it?

 

[SueK]

View attachment BKUP - SOWS IH & LAX DB - Sue In Progress.zip

Embarrassed to share, I inherited this thing and they literally can't tell me why have the garbage is in there, so I've been reluctant to fully clean up! I'm new to the co.

You need a txt file as well, can't seem to upload, will try in separate post

 

[SueK]

Sorry, cant upload the .txt file you would need to run it. Maybe because I'm too new? I did zip it....

 

[SueK]

As I had to close without saving the new field, I can now see that % of the first item is 10.3. I think if I used your logic in "1-(DSum), it would give me what I'm after. Just need to get it to run!

 

[sneuberg]

Sorry, cant upload the .txt file you would need to run it. Maybe because I'm too new? I did zip it....

You are up to ten posts so I might work now. Please give it another try.

 

[SueK]

No can do :-( If I try .zip, it quickly says it failed and if I try the original .txt, it says there's a security token missing.

I wonder if needs time to register that I've hit 10 posts?

 

[SueK]

I thought be cute and ran the linked file through a make table, rezipped the db and tried to upload that, but it's failing on a security token again.

 

[sneuberg]

Are you following these steps . Your first upload appeared as a link. Usually they appear as a button below the post.

 

[MarkK]

Here's a database that does it. See if you can make it work for you. There's code in Module1 that flags the top 80% of rows in the table by value.

 

[SueK]

Are you following these steps . Your first upload appeared as a link. Usually they appear as a button below the post.

Yep! Once you do these steps, if you use the little down arrow on the paper clip, it puts the link in.

Just tried again - still getting the security code fail. Will have a look at what Mark sent and keep trying. At least I understand the query method now - beyond the slow running issue.

 

[sneuberg]

I looked at his solution. If you can add a boolean field to a table to flag the records as MarkK does then his solution looks like a good one. It should be fast and won't cause any bloat.