Selecting top records based on multiple fields

K

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?
 
M

MGFoster

-----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-----
 
K

Kat5813

That seems to have worked.

Thank you so much!!

MGFoster said:
-----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?
Click to expand...
Click to expand...
 
K

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)));

MGFoster said:
-----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?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating a Chart on form from selected data in query. 0
Obtain Top Value from Table 9
Comparing two fields in two different records 6
Page numbering with Merged Document and IF statements 1
Selecting Fields From a Record 4
Include additional fields in or from a totals qry 12
Multiple look up criteria 2
return without gosub error 0

Top