Finding the top categories in a query

[weazer via AccessMonster.com]

I have the following fields and I want to create a query that will give me
the top 5 in each category.


Product make
Product model
defects
date
manufacturer plant

What I want to do is enter a to and from date as a criteria. There are 4
manufacturing plants and I want to list the top five defects for each product
make and model that each manufacturing plant produces.

I would appreciate help because I keep getting the top 5 defects for all the
manufacturing plants combined.

Thanks.

 

[John Spencer]

Is defects a count of defects on a particular date? Or is defects the
name of a defect that occurred on specific date?

If you post the query that is giving you the top 5 for ALL plants, we
can suggest how to modify it to return the top 5 for each plant.

Hint: View: SQL (from the menu) then copy and paste


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[JamesDeckert]

So are you wanting 4 queries or are you wanting 1 query?

What you need to do is create a query which is sorted so that the top 5
records are what you want, then limit the query to the top 5.

If you are wanting 1 query to show the results of 4 queries, you'll probably
need to do 4 queries which result in a top 5, then create a UNION query which
will combine the results of more than one query to make it look like only one
query was done.

HTH,
James

 

[weazer via AccessMonster.com]

Thanks for the reply. This is what I did but in the union query it is giving
me the top 5. I want the top five for each plant for each make and model.I
will show the format below.

Plant Make Model defect Count
A 100 A crack 10
100 A weld 8
100 A paint 6
100 A chip 4
100 A split 2
200 A weld 7
200 A chip 6

make and model will show 3 more defects for make 200 and model A.The same
would be done for the other 3 plants for all the make and models they produce.
For each make and model the top 5 defect need to be listed.

I need to have this done in the 4 queries for each plant and for the union
query.

Thanks.

So are you wanting 4 queries or are you wanting 1 query?

What you need to do is create a query which is sorted so that the top 5
records are what you want, then limit the query to the top 5.

If you are wanting 1 query to show the results of 4 queries, you'll probably
need to do 4 queries which result in a top 5, then create a UNION query which
will combine the results of more than one query to make it look like only one
query was done.

HTH,
James

I have the following fields and I want to create a query that will give me
the top 5 in each category.

[quoted text clipped - 13 lines]

Thanks.

 

[weazer via AccessMonster.com]

Right now I do not have access to the SQL view because it is on a computer at
work. I had 5 queries. I had 1 query for each plant and the fifth query was a
union query. The problem is in each of the 4 queries. I want these queries to
show the top 5 defects for each make and model the plant produces. The date
will be put in via an input form and passed on to each of the 4 queries.

In my 4 queries - 1 for each plant I count on the defect field and get a
total for each defect by make and model, but when I select the the top 5 it
gives me the results for just the top 5 defects and not the top five defects
for each make and model combination.

I know without the SQL view it is more difficult, but I hope you can point me
in the right direction with this.

Thanks again for your help.

Is defects a count of defects on a particular date? Or is defects the
name of a defect that occurred on specific date?

If you post the query that is giving you the top 5 for ALL plants, we
can suggest how to modify it to return the top 5 for each plant.

Hint: View: SQL (from the menu) then copy and paste

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have the following fields and I want to create a query that will give me
the top 5 in each category.

[quoted text clipped - 13 lines]

Thanks.

 

[John Spencer]

I still don't know how you are determining the top 5 over the entire group.

A Generic sample

SELECT Field1, Field2, Field3
FROM YourTable
WHERE Field3 in
(SELECT Top 5 Temp.Field1
FROM YourTable as Temp
WHERE Temp.Field1 = YourTable.Field1
AND Temp.Field2 = YourTable.Field2
ORDER BY Temp.Field2 Desc, Temp.Field3 Desc)



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Right now I do not have access to the SQL view because it is on a computer at
work. I had 5 queries. I had 1 query for each plant and the fifth query was a
union query. The problem is in each of the 4 queries. I want these queries to
show the top 5 defects for each make and model the plant produces. The date
will be put in via an input form and passed on to each of the 4 queries.

In my 4 queries - 1 for each plant I count on the defect field and get a
total for each defect by make and model, but when I select the the top 5 it
gives me the results for just the top 5 defects and not the top five defects
for each make and model combination.

I know without the SQL view it is more difficult, but I hope you can point me
in the right direction with this.

Thanks again for your help.

Is defects a count of defects on a particular date? Or is defects the
name of a defect that occurred on specific date?

If you post the query that is giving you the top 5 for ALL plants, we
can suggest how to modify it to return the top 5 for each plant.

Hint: View: SQL (from the menu) then copy and paste

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have the following fields and I want to create a query that will give me
the top 5 in each category.

[quoted text clipped - 13 lines]

Thanks.

 

[weazer via AccessMonster.com]

When I count all the defects by make and model and group by the make and
model I come up with the total for each defect by make and model and I then
sort it in descending order. When I then take this entire population and try
to list just the top 5 defects for each make and model I only get the top 5
defects for the whole population or depending upon the sort the results will
be the data from the first five results from the query.

I know it is confusing - sorry. Thanks again.

I still don't know how you are determining the top 5 over the entire group.

A Generic sample

SELECT Field1, Field2, Field3
FROM YourTable
WHERE Field3 in
(SELECT Top 5 Temp.Field1
FROM YourTable as Temp
WHERE Temp.Field1 = YourTable.Field1
AND Temp.Field2 = YourTable.Field2
ORDER BY Temp.Field2 Desc, Temp.Field3 Desc)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Right now I do not have access to the SQL view because it is on a computer at
work. I had 5 queries. I had 1 query for each plant and the fifth query was a

[quoted text clipped - 31 lines]

[quoted text clipped - 13 lines]
Thanks.