Top Values based on 2 Fields

S

SMac

I have a query that returns the following information:

Plant Part Number Cost
----------------------------------------
P1 08327 $82,000
P1 08122 $50,000
P3 06222 $20,000
P3 05876 $15,000
P5 03666 $5,000
P5 09128 $4,500
etc. about 100 lines more.

What I want is the Top 5 for each Plant, is there a way to do this or write
it in SQL?

Thanks!!
 
K

Ken Snell [MVP]

Top 5 based on what ? Cost? Part Number?

What is the primary key for the table that has the data?

Assuming that you want the Top 5 based on cost and assuming that you have a
primary key field that is not shown here, something like this should get you
started:

SELECT [Plant], [Part Number], [Cost]
FROM QueryName
WHERE [NameofPrimaryKeyField] IN
( SELECT TOP 5 A.[NameofPrimaryKeyField]
FROM QueryName AS A
WHERE A.[Plant]=QueryName.[Plant]
ORDER BY A.[Cost] DESC );
 
S

SMac

Yes the Top 5 are based on the Cost.

This table doesn't have a primary key, its basically an information table
because it is a download out of a non-Windows system to be able to sum up the
cost of each part.

Could I do make a table query based on the results of the below select query
and have the part number as the primary key and then do a Top 5 query? If
that would work would I use the same SQL statement as you have stated below?

Thanks for your time and help!

Ken Snell said:
Top 5 based on what ? Cost? Part Number?

What is the primary key for the table that has the data?

Assuming that you want the Top 5 based on cost and assuming that you have a
primary key field that is not shown here, something like this should get you
started:

SELECT [Plant], [Part Number], [Cost]
FROM QueryName
WHERE [NameofPrimaryKeyField] IN
( SELECT TOP 5 A.[NameofPrimaryKeyField]
FROM QueryName AS A
WHERE A.[Plant]=QueryName.[Plant]
ORDER BY A.[Cost] DESC );

--

Ken Snell
<MS ACCESS MVP>



SMac said:
I have a query that returns the following information:

Plant Part Number Cost
----------------------------------------
P1 08327 $82,000
P1 08122 $50,000
P3 06222 $20,000
P3 05876 $15,000
P5 03666 $5,000
P5 09128 $4,500
etc. about 100 lines more.

What I want is the Top 5 for each Plant, is there a way to do this or write
it in SQL?

Thanks!!
Click to expand...
Click to expand...
 
S

SMac

I did the make table query and then based the Top 5 query off of that table
with the Part Number as Primary Key.

The problem is I get the following message when I run the Top 5 query:
"Circular reference caused by 'qry_FTT_Cost'" (qry_FTT_Cost is the name of
my query).

Any suggestions?

Thanks again!

Ken Snell said:
Top 5 based on what ? Cost? Part Number?

What is the primary key for the table that has the data?

Assuming that you want the Top 5 based on cost and assuming that you have a
primary key field that is not shown here, something like this should get you
started:

SELECT [Plant], [Part Number], [Cost]
FROM QueryName
WHERE [NameofPrimaryKeyField] IN
( SELECT TOP 5 A.[NameofPrimaryKeyField]
FROM QueryName AS A
WHERE A.[Plant]=QueryName.[Plant]
ORDER BY A.[Cost] DESC );

--

Ken Snell
<MS ACCESS MVP>



SMac said:
I have a query that returns the following information:

Plant Part Number Cost
----------------------------------------
P1 08327 $82,000
P1 08122 $50,000
P3 06222 $20,000
P3 05876 $15,000
P5 03666 $5,000
P5 09128 $4,500
etc. about 100 lines more.

What I want is the Top 5 for each Plant, is there a way to do this or write
it in SQL?

Thanks!!
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

No, using Part Number as a primary key would not be appropriate, as I assume
that Part Number is not unique among the records.

Why not add an autonumber field to your table and make it the primary key?
That will then give you what you need for this query to work. Note that a
primary key is required if you want to use a query such as this. And also
note that you'll need to include the table's primary key in your first query
(the one being used by my query) for it to work.
--

Ken Snell
<MS ACCESS MVP>



SMac said:
Yes the Top 5 are based on the Cost.

This table doesn't have a primary key, its basically an information table
because it is a download out of a non-Windows system to be able to sum up the
cost of each part.

Could I do make a table query based on the results of the below select query
and have the part number as the primary key and then do a Top 5 query? If
that would work would I use the same SQL statement as you have stated below?

Thanks for your time and help!

Ken Snell said:
Top 5 based on what ? Cost? Part Number?

What is the primary key for the table that has the data?

Assuming that you want the Top 5 based on cost and assuming that you have a
primary key field that is not shown here, something like this should get you
started:

SELECT [Plant], [Part Number], [Cost]
FROM QueryName
WHERE [NameofPrimaryKeyField] IN
( SELECT TOP 5 A.[NameofPrimaryKeyField]
FROM QueryName AS A
WHERE A.[Plant]=QueryName.[Plant]
ORDER BY A.[Cost] DESC );

--

Ken Snell
<MS ACCESS MVP>



SMac said:
I have a query that returns the following information:

Plant Part Number Cost
----------------------------------------
P1 08327 $82,000
P1 08122 $50,000
P3 06222 $20,000
P3 05876 $15,000
P5 03666 $5,000
P5 09128 $4,500
etc. about 100 lines more.

What I want is the Top 5 for each Plant, is there a way to do this or write
it in SQL?

Thanks!!
Click to expand...
Click to expand...
Click to expand...
 
Top