Top 20 of each group location

[Alanna]

Hi
TOP SELLERS Query.zip

In this query ( I exported as excel but it's a query in my DB) I want my form to print the top 20 best sellers by each location.


If I do return: 20 I only get the top 20 of DFW, I need top 20 of DFW, IAH, LAS, LAX, HRV, SFO. I'm NOT a coder and stumble through access. I would think I would be able to do this in design view, but I can't get it to work.

Anyone???

[ranman256]

in a form , put a listbox that has all the Locations.
the query looks at the listbox to pull the top 20:
select top 20 from table where [location]=forms!myForms!lstBox

then a button to loop thru the listbox exporting each query to a sheet:

sub btnPrintRpts_click()
dim vLoc
dim i as integer
dim vFile, vQry

vQry = "qsExportTop20of1Loc"
vFile = "c:\folder\Report.xls"

for i = 0 to lstBox.listcount -1
    vLoc = lstBox.itemdata(i)      'get next item in listbox
    lstBox = vLoc           'set the listbox to this item

        'export
    docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12, vQry, vFile,true,vLoc
next
end sub

[Alanna]

Sorry you really lost me.
On my query is each location and all the sales by item. I'm trying to create a pretty form that has each location with top 20 best sellers. One page per location.
Again code is lost to me. And I only did this in excel for purposes of you seeing the query. I have the query trying to get it to form.
So on my form, how do I tell it to give me the top 20 best sellers / location. Note, my query is sorted for net sales highest to lowest.

[ranman256]

Build the query with anything you like.
the code loops Thu the list (location)
produces top 20 for that location.

you cannot ask for top 20 for all locations.
you only get 20.

[Alanna]

So basically I would have to have a query for each location then for the report bring in the data from each individual location query.

[ssanfu]

You can ask for top 20 for all locations. But the query will need a sub query. Since don't have your dB, and don't know your table & field names, you get to do the heavy lifting.

See http://allenbrowne.com/subquery-01.html#TopN
(Also, read the whole page.)

Another site to read: https://stackoverflow.com/questions/...-sql-in-access


You can also search the internet on "TOP n records per group"

[orange]

You my get some ideas from this post in the code repository.

[Alanna]

Actually I supplied my query in excel format in my initial thread
I have googled and I got lost.... a lot.
I'll check out your links but I'm not a coder.

[Alanna]

I read it but don't understand it... sorry again not a coder. Just a simple Inventory Manager trying to do a coder job.

[orange]

Since we don't know your database and tables and the data, we really can't offer much more.

Is it possible to post a copy of the data base( we only need a few records)?
I recognize some airports ids, but we need more to offer more focused/detailed suggestions.

I copied your excel data to access and created table tblTOPSELLERS and let Access assign a primary Key (ID).
I designed and ran this query

SELECT t1.LOCATION,  t1.DEPT,t1.Item_no,t1.descr, t1.[SumofNet SALES], t1.id
FROM tblTOPSELLERS AS t1
WHERE (
        (
            (t1.id) IN (
                SELECT TOP 20 T2.Id
                FROM tblTOPSELLERS AS T2
                WHERE T2.LOCATION = T1.LOCATION
                ORDER BY T2.ID ASC
                )
            )
        )
GROUP BY t1.LOCATION,t1.ITEM_NO, t1.descr, t1.DEPT, t1.[SumofNet SALES], t1.id
ORDER BY t1.Location, t1.[SumofNet SALES]  DESC;

to get this result

LOCATION
DEPT
Item_no
descr
SumofNet SALES
id

DFW
60
A102551
S/S CROCHET SWEATER W/ FRINGE
$299.20
1

DFW
60
A103895
THE LOVE IT HOLDS L/S PLAID
$281.20
2

DFW
60
A103958
THE CAMO OVERALLS
$237.00
3

DFW
61
A103221
STORIES OF SHAWL
$209.60
4

DFW
60
A102183
KYOTO LEGGING
$204.80
5

DFW
60
A102547
PEEK-A-BOO SLEEVE SWEATER
$200.60
6

DFW
60
A100967
EMBELLISHED MILITARY SHIRT JKT
$168.00
7

DFW
60
A102411
SATURDAY MORNING CARDI
$148.00
8

DFW
60
A100254
S/S POCKET TEE
$144.00
9

DFW
60
A102844
PJ SHIRT
$138.00
10

DFW
60
A102886
PATCHWORK PRINT KAFTAN
$136.00
11

DFW
60
A101484
VNCK SWEATER BUCKING HORSE
$132.00
12

DFW
60
A103956
THE SLEEK JERSEY POCKET TEE
$132.00
13

DFW
60
A102032
WFFL KNIT SWTR "I LOVE DALLAS"
$116.00
14

DFW
60
A103026
JOGGER PANT W/ DRAWSTRING
$116.00
15

DFW
60
A103545
S/S V- NECK MAMA NEEDS WINE
$116.00
16

DFW
60
A103349
TAKE ME TEE
$104.40
17

DFW
60
A102415
BLOSSOM THERMAL
$98.00
18

DFW
60
A103843
MAMA NEEDS SHOTS L/S HOODIE
$98.00
19

DFW
60
A103885
SLIM JOGGER ANKLE PANTS
$96.00
20

HRH
61
A103221
STORIES OF SHAWL
$285.50
66

HRH
60
A103616
DOE- SLD SMCKD TANK MAXI DRESS
$224.00
67

HRH
60
A103617
AMIRA DRESS
$196.00
68

HRH
60
A103857
DALE'S TRACK LEGGING
$156.00
69

HRH
60
A101951
TOPAZ KIMONO
$145.00
70

HRH
60
A103219
SWEET ROSE SHIRT
$138.00
71

HRH
60
A102165
DEVON PULL ON SKINNY PANT
$128.00
72

HRH
60
A102594
HUNTER LEE DRAPED CARDIGAN
$118.00
73

HRH
60
A103900
PRNTD S/S BUTTON DOWN TIE FRNT
$116.00
74

HRH
60
A103934
HALTER SHORT DRESS
$98.00
75

HRH
61
A101903
QUILTED CHAINED CROSSBODY
$94.00
76

HRH
61
A103867
LUCY FLORAL WRITLET BAG
$91.20
77

HRH
60
A101836
501 CT JEANS
$89.50
78

HRH
60
A102548
V-NECK CROCHET TUNIC W/ FRINGE
$88.00
79

HRH
60
A103662
ALLMAN BROS TEE
$88.00
80

HRH
60
A103878
LOVE STREET TOP
$88.00
81

HRH
60
A103932
TIE FRNT BTTN DOWN L/S BLOUSE
$88.00
82

HRH
60
A103958
THE CAMO OVERALLS
$79.00
83

HRH
61
A100477
VALENTINO CAMERA SM BAG
$78.40
84

HRH
60
A102277
VIRGO LEGGING
$78.00
85

IAH
60
A103896
WE BREAK FREE FLORAL L/S
$400.20
134

IAH
60
A103899
WHEREEVER YOU GO SKIRT
$250.80
135

IAH
60
A103872
RAILROAD HENLEY
$218.40
136

IAH
60
A103933
S/S SHORT DRESS
$196.00
137

IAH
61
A103910
GLASS BEAD CTTN TASSL NCKLACE
$187.20
138

IAH
60
A100967
EMBELLISHED MILITARY SHIRT JKT
$168.00
139

IAH
60
A103093
THGHT I WAS DRMING MINI DRESS
$162.00
140

IAH
60
A103958
THE CAMO OVERALLS
$150.10
141

IAH
60
A103897
EACH & EVERY DAY L/S SHIRT
$138.00
142

IAH
60
A103611
BELIEVER SWEATSHIRT
$136.00
143

IAH
60
A102545
PRINTED HI LO BELL SLEEVE TOP
$129.20
144

IAH
61
A103926
"LOVE U 2 THE MOON" BAR NCKLC
$126.00
145

IAH
60
A103592
BLESSED L/S HILO TUNIC
$118.00
146

IAH
60
A102499
SIESTA S/S V NECK TEE W/ PANT
$115.20
147

IAH
60
A102555
T-SHIRT W/ CNTRST STRP ON SLV
$110.20
148

IAH
61
A103433
QUILTED BDY VGN LTHR TOTE
$108.00
149

IAH
60
A103855
BEAU SMOCKED PRINTED SLIP
$108.00
150

IAH
60
A103874
JUST MY STRIPE PULLOVER
$108.00
151

IAH
61
A103928
LETTER PENDANT NECKLACE
$108.00
152

IAH
60
A103082
LOVELY DAY BUTTONDOWN TOP
$102.60
153

LAS
60
A103766
LOVE KNIT SLEEPWEAR L/S HOODY
$196.00
240

LAS
60
A102894
STRIPED HOODED PULLOVR SWEATER
$156.00
241

LAS
60
A100254
S/S POCKET TEE
$108.00
242

LAS
61
A103432
QUILTED BACKPACK
$98.00
243

LAS
60
A103671
NIRVANA THERMAL W/THUMBHOLES
$98.00
244

LAS
60
A103839
CROPPED S/S TEE DAVID BOWIE
$91.20
245

LAS
60
A102174
BACK INTO IT JOGGER
$88.00
246

LAS
60
A103647
PRNTD DOT TIE FRONT KIMONO TOP
$88.00
247

LAS
61
A103863
QUILTED BACKPACK
$88.00
248

LAS
60
A101537
DREAM DRESS
$84.00
249

LAS
60
A102253
ROCKY ROAD SOLID LONG MSCL TNK
$84.00
250

LAS
60
A103758
PRINTED LACEUP TUNIC
$76.00
251

LAS
60
A103376
OTS 'PEACE' L/S TEE
$68.00
252

LAS
61
A100714
ARMY HAT
$65.00
253

LAS
60
A103584
EMBROIDERED GAUZE COVER UP
$64.00
254

LAS
61
A103884
PAYTON CROSSBODY BAG
$61.20
255

LAS
60
A103346
ISLAND GIRL HACCI
$58.00
256

LAS
60
A103869
GOLDEN GATE TEE
$58.00
257

LAS
60
A103973
SPAG STRAP PAISLEY SCRF DRSS
$58.00
258

LAS
60
A101593
JIMBARAN SHORT DRESS
$49.00
259

LAX
61
A103788
LG LETTER SLANTED PENDANT NCKL
$418.00
290

LAX
60
A104055
MALIBU CAP SLV CREW NECK TEE
$352.00
291

LAX
61
A100787
"LA" DODGERS TONAL BLLPRK HAT
$280.80
292

LAX
60
A103874
JUST MY STRIPE PULLOVER
$270.00
293

LAX
60
A103875
BEACH COMBER VNECK L/S
$270.00
294

LAX
60
A104058
L.A VNK TEEw/SNG BNDNG DTL
$261.60
295

LAX
60
A103288
L/S CLD SHLDR SMKDWAIST MINI D
$256.00
296

LAX
60
A104011
HELEN TROUSER SHORT
$250.80
297

LAX
60
A102889
L/S TERRY PULLOVER HOODIE
$240.00
298

LAX
60
A103869
GOLDEN GATE TEE
$232.00
299

LAX
61
A103446
CALI ICONIC HAT
$228.00
300

LAX
60
A103878
LOVE STREET TOP
$220.00
301

LAX
60
A103885
SLIM JOGGER ANKLE PANTS
$216.00
302

LAX
61
A103946
LG CRYSTAL PENDANT NECKLACE
$210.00
303

LAX
60
A103611
BELIEVER SWEATSHIRT
$204.00
304

LAX
60
A103278
SMOCKED OFF THE SHLDR L./S TOP
$196.00
305

LAX
61
A103989
MY DRINKING HAT
$190.00
306

LAX
60
A101314
DECO BRDR TASSEL OFF SHLDR TOP
$187.20
307

LAX
60
A103877
CHAMBRAY BTTRFLY DOT DRESS
$181.30
308

LAX
60
A102429
"I LOVE LA" FLEECE ACTIVE PANT
$170.00
309

SFO
60
A103877
CHAMBRAY BTTRFLY DOT DRESS
$284.20
536

SFO
60
A103869
GOLDEN GATE TEE
$232.00
537

SFO
60
A103934
HALTER SHORT DRESS
$196.00
538

SFO
60
A101538
DREAM PONCHO
$188.00
539

SFO
60
A101443
SNOWCONE HOODIE
$168.00
540

SFO
60
A103958
THE CAMO OVERALLS
$158.00
541

SFO
60
A103879
MOVE LIGHTLY CAMI PRINTED
$136.00
542

SFO
60
A103189
'SANTO' EMBROIDERED PANTS
$116.00
543

SFO
60
A103346
ISLAND GIRL HACCI
$116.00
544

SFO
60
A103391
VIVA MEXICO/SANTO PANTS
$116.00
545

SFO
60
A103169
NIGHTINGALE CARDI
$108.00
546

SFO
61
A103433
QUILTED BDY VGN LTHR TOTE
$108.00
547

SFO
60
A103874
JUST MY STRIPE PULLOVER
$108.00
548

SFO
61
A103928
LETTER PENDANT NECKLACE
$108.00
549

SFO
60
A103109
LIRA LEGGING
$98.00
550

SFO
60
A103205
NAMASTE OSAKA LEGGING
$98.00
551

SFO
60
A103278
SMOCKED OFF THE SHLDR L./S TOP
$98.00
552

SFO
60
A103475
DAKOTA SKIRT
$98.00
553

SFO
60
A103553
RETRO FEMME BLOUSE
$98.00
554

SFO
60
A103353
SWING IT COWL NCK TOP
$96.00
555

I hope it's useful to you.

Note : Using PK will eliminate ties.(as per Allen Browne)

[Alanna]

TOP SELLERS Query.zipTOP SELLERS Query.zip

From this I want for each Location the top 20 in net sales.
Each location would have a top 20 in net sales

LOCATION	DEPT	DEPT DESC	Total Qty Sold	Total Net Sales
DFW	60	Specialty Apparel	9	$299.20
DFW	60	Specialty Apparel	2	$281.20
DFW	60	Specialty Apparel	3	$237.00
DFW	61	Specialty Accessories	3	$209.60
DFW	60	Specialty Apparel	2	$204.80
DFW	60	Specialty Apparel	6	$200.60
DFW	60	Specialty Apparel	2	$168.00
DFW	60	Specialty Apparel	2	$148.00
DFW	60	Specialty Apparel	4	$144.00
DFW	60	Specialty Apparel	1	$138.00
DFW	60	Specialty Apparel	2	$136.00
DFW	60	Specialty Apparel	3	$132.00
DFW	60	Specialty Apparel	3	$132.00
DFW	60	Specialty Apparel	2	$116.00
DFW	60	Specialty Apparel	4	$116.00
DFW	60	Specialty Apparel	2	$116.00
DFW	60	Specialty Apparel	2	$104.40
DFW	60	Specialty Apparel	1	$98.00
DFW	60	Specialty Apparel	1	$98.00
DFW	60	Specialty Apparel	2	$96.00

[orange]

I was editing my post when you posted.
Take a look and see if this is close.

This sql should get the quantity also

SELECT t1.LOCATION,  t1.DEPT,t1.[DEPT DESC], t1.Item_no,t1.descr,t1.[SumOfQTY SOLD], t1.[SumofNet SALES], t1.id
FROM tblTOPSELLERS AS t1
WHERE (
        (
            (t1.id) IN (
                SELECT TOP 20 T2.Id
                FROM tblTOPSELLERS AS T2
                WHERE T2.LOCATION = T1.LOCATION
                ORDER BY T2.ID ASC
                )
            )
        )
GROUP BY t1.LOCATION,t1.ITEM_NO, t1.descr, t1.DEPT,t1.[DEPT DESC],t1.[SumOfQTY SOLD], t1.[SumofNet SALES], t1.id
ORDER BY t1.Location, t1.[SumofNet SALES]  DESC;

[Alanna]

SELECT t1.LOCATION, t1.DEPT,t1.[DEPT DESC], t1.Item_no,t1.descr,t1.[SumOfQTY SOLD], t1.[SumofNet SALES], t1.id
FROM tblTOPSELLERS AS t1

It's returning a syntax error in "from " clause, then highlights the "as"

[Alanna]

SELECT t1.LOCATION, t1.DEPT,t1.[DEPT DESC], t1.Item_no,t1.descr,t1.[SumOfQTY SOLD], t1.[SumofNet SALES], t1.id
FROM tblTOPSELLERS AS t1

It's returning a syntax error in "from " clause, then highlights the "as"

[Alanna]

got past the "as' issue not it is having an issue with the IN(select top 20.......