Top 10 by Category

[was1]

Hello,

Is there a way to create a query that lists the top 10 by each category without and sql or vb code? I'm not familiar with coding.

Ex:
Soda
Coke 105
Pespi 100
Sunkist 80

Chips
Ruffles 87
Lays 75
Doritos 60

etc...

 

[dhookom]

Do you really want this without SQL? Do you have any table and field names you could share with us?

Duane
Hook'D on Access
MS Access MVP

 

[was1]

I dont know SQL so I would need step by step instructions...

Table: Food
Field1: Snacks
Field2: Brand
Field3: Volume

thanks!

 

[dhookom]

You would go to the SQL view of a query and enter something like:

SELECT Snacks, Brand, Volume 
FROM FOOD
WHERE Brand IN 
(SELECT TOP 10 F.Brand
 FROM FOOD F
 WHERE F.Snacks = FOOD.Snacks
 ORDER BY F.Volume DESC)

Duane
Hook'D on Access
MS Access MVP

 

[was1]

I went into the SQL view of the query and entered the info but when I tried to run it, I received an message box "Select Data Source". I'm not sure what to enter.

 

[dhookom]

The error message sounds like you went to "SQL Specific" in the query rather than:
1) click new query
2) click OK for Design View
3) click Close without selecting any tables
4) Select View->SQL View
5) Replace the "SELECT" with the SQL statement I suggested previously
6) Select View->Datasheet View

If you have an issue, please reply with the SQL you tried as well as any error message.


Duane
Hook'D on Access
MS Access MVP

 

[was1]

It works! I'll have to keep that for future reference.

I thought it was going to be much more complicated. Thanks for you help.