Selecting top records based on multiple fields

[Kat5813]

I have a query shown below:

SELECT data.[Account-Sub account], data.[Account Unit], data.[Account Unit
Description], data.Account, data.[2007 Actual], data.[2008 Actual],
data.[2009 Actual], data.[2009 Budget], data.Description, data.[Description
Order], data.[LEVEL 1], data.[L1 Order], data.[LEVEL 2], data.[L2 Order],
data.[LEVEL 3], data.[L3 Order], data.[LEVEL 4], data.L4Order, data.[LEVEL
5], data.[LEVEL 6]
FROM data;


Based on this, is there any way to select the top 10 for the 2009 actual in
each of the different descriptions (data.description). So, the description
filed has several different values. What I want is for each of those values,
give me a list of the top 10 based on the 2009 actual field.

So for example, a record might have 'Total Salaries' in the description
field and $100,000 in the 2009 Actual field. Another record might have
$5,000 in the 2009 Actual field. If I only wanted the 1 top record, I would
need a way to select the first one while being able to do the rest for each
of the other values in the description field.

Is this even possible?

 

[Kat5813]

That seems to have worked.

Thank you so much!!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps this (untested):

SELECT [Account-Sub account], [Account Unit],
[Account Unit Description], Account,
[2007 Actual], [2008 Actual], [2009 Actual], [2009 Budget],
Description, [Description Order],
[LEVEL 1], [L1 Order], [LEVEL 2], [L2 Order], [LEVEL 3], [L3 Order],
[LEVEL 4], L4Order, [LEVEL 5], [LEVEL 6]
FROM data AS D
WHERE [2009 Actual] In
(SELECT TOP 10 [2009 Actual]
FROM data
WHERE Description = D.Description
ORDER BY [2009 Actual] DESC)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBST7g44echKqOuFEgEQJamACfWeGXb61U68GZD5JY7uz+Wc8BjpcAniRX
lX1PBCPhcmwBo0C0q8BxD4Kz
=QtuR
-----END PGP SIGNATURE-----

I have a query shown below:

SELECT data.[Account-Sub account], data.[Account Unit], data.[Account Unit
Description], data.Account, data.[2007 Actual], data.[2008 Actual],
data.[2009 Actual], data.[2009 Budget], data.Description, data.[Description
Order], data.[LEVEL 1], data.[L1 Order], data.[LEVEL 2], data.[L2 Order],
data.[LEVEL 3], data.[L3 Order], data.[LEVEL 4], data.L4Order, data.[LEVEL
5], data.[LEVEL 6]
FROM data;


Based on this, is there any way to select the top 10 for the 2009 actual in
each of the different descriptions (data.description). So, the description
filed has several different values. What I want is for each of those values,
give me a list of the top 10 based on the 2009 actual field.

So for example, a record might have 'Total Salaries' in the description
field and $100,000 in the 2009 Actual field. Another record might have
$5,000 in the 2009 Actual field. If I only wanted the 1 top record, I would
need a way to select the first one while being able to do the rest for each
of the other values in the description field.

Is this even possible?

 

[Kat5813]

This was working at first, but I changed the table to include another field
and renamed it and now I keep getting a box that says query.description when
I try to run the query (in other words it is asking for that value).

Here is the new SQL with the changes I made. Any thoughts?

SELECT D.[Account-Sub account], D.[Account Unit], D.[Account Unit
Description], D.Account, D.[Fiscal Month Name], D.[2007 Actual], D.[2008
Actual], D.[2009 Actual], D.[2009 Budget], D.Description, D.[Description
Order], D.[LEVEL 1], D.[L1 Order], D.[LEVEL 2], D.[L2 Order], D.[LEVEL 3],
D.[L3 Order], D.[LEVEL 4], D.L4Order, D.[LEVEL 5], D.[LEVEL 6]
FROM [Data 2008] AS D
WHERE (((D.[2009 Actual]) In (SELECT TOP 10 [2009 Actual] FROM [data 2008]
WHERE Description = D.Description ORDER BY [2009 Actual] DESC)));

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps this (untested):

SELECT [Account-Sub account], [Account Unit],
[Account Unit Description], Account,
[2007 Actual], [2008 Actual], [2009 Actual], [2009 Budget],
Description, [Description Order],
[LEVEL 1], [L1 Order], [LEVEL 2], [L2 Order], [LEVEL 3], [L3 Order],
[LEVEL 4], L4Order, [LEVEL 5], [LEVEL 6]
FROM data AS D
WHERE [2009 Actual] In
(SELECT TOP 10 [2009 Actual]
FROM data
WHERE Description = D.Description
ORDER BY [2009 Actual] DESC)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBST7g44echKqOuFEgEQJamACfWeGXb61U68GZD5JY7uz+Wc8BjpcAniRX
lX1PBCPhcmwBo0C0q8BxD4Kz
=QtuR
-----END PGP SIGNATURE-----

I have a query shown below:

SELECT data.[Account-Sub account], data.[Account Unit], data.[Account Unit
Description], data.Account, data.[2007 Actual], data.[2008 Actual],
data.[2009 Actual], data.[2009 Budget], data.Description, data.[Description
Order], data.[LEVEL 1], data.[L1 Order], data.[LEVEL 2], data.[L2 Order],
data.[LEVEL 3], data.[L3 Order], data.[LEVEL 4], data.L4Order, data.[LEVEL
5], data.[LEVEL 6]
FROM data;


Based on this, is there any way to select the top 10 for the 2009 actual in
each of the different descriptions (data.description). So, the description
filed has several different values. What I want is for each of those values,
give me a list of the top 10 based on the 2009 actual field.

So for example, a record might have 'Total Salaries' in the description
field and $100,000 in the 2009 Actual field. Another record might have
$5,000 in the 2009 Actual field. If I only wanted the 1 top record, I would
need a way to select the first one while being able to do the rest for each
of the other values in the description field.

Is this even possible?