Finding top 25 records per month

[Robert Gillard]

I have a data base that imports data from another system each month. (approx
1200 rows per month). Because of the way the system works it does not just
add the current month to the bottom of the table, but repopulates the table
from month1 (Aug 2003 - this is because certain fields may have changed as
volumes relate to adverse reactions that have arisen because of actions
taken on a certain date.)

Because of the volume of data in the table, I need to extract just the top
25 rows each month ie those with the highest score in column3. Whilst I can
get the top 25 rows for the current month, or I can get the top row (one row
only) for each month from Aug03 to date, I do not seem to be able to combine
the 2 queries and get the top 25 rows every month since Aug03.

Is it possible to get back the information as detailed above, or does anyone
have any thoughts on a best solution to this issue.

 

[PC Datasheet]

Open your query in design view. Set the sort for Col3 to descending. Right
click in the query window and click on Properties in the dialog that
appears. Set the Top Values property to 25. Be sure your Month field is
included in the query. The query will now give you the top 25 records for
each month.

 

[Robert Gillard]

I have tried your suggestion and I only get back either the top 25 entries
regardless of date or alternatively the top 25 from any one month.(depending
on what fields selected and whether ascending or descending)

I have attached the SQL in case that is of help to isolate what I am doing
wrong.(where "good" is the field on which I need to find the top 25 items
every month - not just the current month)

SELECT TOP 25 [12 Month].Good, [12 Month].Decode, [12 Month].[Application
Month]

FROM [12 Month]

ORDER BY [12 Month].Good DESC;

 

[Duane Hookom]

Do you have a primary key field in your table? Assuming one named ID you can
try this SQL:
SELECT [12 Month].Good, [12 Month].Decode, [12 Month].[Application Month]
FROM [12 Month] T
WHERE T.ID IN
(SELECT TOP 25 ID
FROM [12 Month] M
WHERE T.[Application Month] = M.[Application Month]
ORDER BY Good DESC)
ORDER BY [12 Month].Good DESC;

Duane Hookom
MS Access MVP

I have tried your suggestion and I only get back either the top 25 entries
regardless of date or alternatively the top 25 from any one
month.(depending
on what fields selected and whether ascending or descending)

I have attached the SQL in case that is of help to isolate what I am doing
wrong.(where "good" is the field on which I need to find the top 25 items
every month - not just the current month)

SELECT TOP 25 [12 Month].Good, [12 Month].Decode, [12 Month].[Application
Month]

FROM [12 Month]

ORDER BY [12 Month].Good DESC;

Open your query in design view. Set the sort for Col3 to descending.
Right
click in the query window and click on Properties in the dialog that
appears. Set the Top Values property to 25. Be sure your Month field is
included in the query. The query will now give you the top 25 records for
each month.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

 

[Robert Gillard]

As suggested I have tried the following code, but it keeps giving "Enter
Parameter Values" message relating to each part of the 1st line of the code.
I have tried to "fiddle" with it but without success. Could you perhaps give
some more guidance please
SELECT [12 Month].Good, [12 Month].Decode, [12 Month].[Application Month]

FROM [12 Month] T

WHERE T.ID IN

(SELECT TOP 25 ID

FROM [12 Month] M

WHERE T.[Application Month] = M.[Application Month]

ORDER BY Good DESC)

ORDER BY [12 Month].Good DESC;

Do you have a primary key field in your table? Assuming one named ID you can
try this SQL:
SELECT [12 Month].Good, [12 Month].Decode, [12 Month].[Application Month]
FROM [12 Month] T
WHERE T.ID IN
(SELECT TOP 25 ID
FROM [12 Month] M
WHERE T.[Application Month] = M.[Application Month]
ORDER BY Good DESC)
ORDER BY [12 Month].Good DESC;

Duane Hookom
MS Access MVP

I have tried your suggestion and I only get back either the top 25 entries
regardless of date or alternatively the top 25 from any one
month.(depending
on what fields selected and whether ascending or descending)

I have attached the SQL in case that is of help to isolate what I am doing
wrong.(where "good" is the field on which I need to find the top 25 items
every month - not just the current month)

SELECT TOP 25 [12 Month].Good, [12 Month].Decode, [12 Month].[Application
Month]

FROM [12 Month]

ORDER BY [12 Month].Good DESC;

Open your query in design view. Set the sort for Col3 to descending.
Right
click in the query window and click on Properties in the dialog that
appears. Set the Top Values property to 25. Be sure your Month field is
included in the query. The query will now give you the top 25 records for
each month.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


I have a data base that imports data from another system each month.
(approx
1200 rows per month). Because of the way the system works it does not just
add the current month to the bottom of the table, but repopulates the
table
from month1 (Aug 2003 - this is because certain fields may have

changed
as

volumes relate to adverse reactions that have arisen because of actions
taken on a certain date.)

Because of the volume of data in the table, I need to extract just

the
top

25 rows each month ie those with the highest score in column3. Whilst I
can
get the top 25 rows for the current month, or I can get the top row
(one

row
only) for each month from Aug03 to date, I do not seem to be able to
combine
the 2 queries and get the top 25 rows every month since Aug03.

Is it possible to get back the information as detailed above, or does
anyone
have any thoughts on a best solution to this issue.

 

[Duane Hookom]

Try:
SELECT Good, Decode, [Application Month]
FROM [12 Month] T
WHERE T.ID IN
(SELECT TOP 25 ID
FROM [12 Month] M
WHERE T.[Application Month] = M.[Application Month]
ORDER BY Good DESC)
ORDER BY Good DESC;

--
Duane Hookom
MS Access MVP

As suggested I have tried the following code, but it keeps giving "Enter
Parameter Values" message relating to each part of the 1st line of the
code.
I have tried to "fiddle" with it but without success. Could you perhaps
give
some more guidance please
SELECT [12 Month].Good, [12 Month].Decode, [12 Month].[Application Month]

FROM [12 Month] T

WHERE T.ID IN

(SELECT TOP 25 ID

FROM [12 Month] M

WHERE T.[Application Month] = M.[Application Month]

ORDER BY Good DESC)

ORDER BY [12 Month].Good DESC;

Do you have a primary key field in your table? Assuming one named ID you can
try this SQL:
SELECT [12 Month].Good, [12 Month].Decode, [12 Month].[Application Month]
FROM [12 Month] T
WHERE T.ID IN
(SELECT TOP 25 ID
FROM [12 Month] M
WHERE T.[Application Month] = M.[Application Month]
ORDER BY Good DESC)
ORDER BY [12 Month].Good DESC;

Duane Hookom
MS Access MVP

I have tried your suggestion and I only get back either the top 25 entries
regardless of date or alternatively the top 25 from any one
month.(depending
on what fields selected and whether ascending or descending)

I have attached the SQL in case that is of help to isolate what I am doing
wrong.(where "good" is the field on which I need to find the top 25 items
every month - not just the current month)

SELECT TOP 25 [12 Month].Good, [12 Month].Decode, [12 Month].[Application
Month]

FROM [12 Month]

ORDER BY [12 Month].Good DESC;





Open your query in design view. Set the sort for Col3 to descending.
Right
click in the query window and click on Properties in the dialog that
appears. Set the Top Values property to 25. Be sure your Month field
is
included in the query. The query will now give you the top 25 records for
each month.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


I have a data base that imports data from another system each month.
(approx
1200 rows per month). Because of the way the system works it does
not
just
add the current month to the bottom of the table, but repopulates
the
table
from month1 (Aug 2003 - this is because certain fields may have changed
as
volumes relate to adverse reactions that have arisen because of actions
taken on a certain date.)

Because of the volume of data in the table, I need to extract just the
top
25 rows each month ie those with the highest score in column3.
Whilst I
can
get the top 25 rows for the current month, or I can get the top row
(one

row
only) for each month from Aug03 to date, I do not seem to be able to
combine
the 2 queries and get the top 25 rows every month since Aug03.

Is it possible to get back the information as detailed above, or
does
anyone
have any thoughts on a best solution to this issue.

 

[Robert Gillard]

Duane,
Thank you for the time you have spent on my query, works as required
now.

Bob

Try:
SELECT Good, Decode, [Application Month]
FROM [12 Month] T
WHERE T.ID IN
(SELECT TOP 25 ID
FROM [12 Month] M
WHERE T.[Application Month] = M.[Application Month]
ORDER BY Good DESC)
ORDER BY Good DESC;

--
Duane Hookom
MS Access MVP

As suggested I have tried the following code, but it keeps giving "Enter
Parameter Values" message relating to each part of the 1st line of the
code.
I have tried to "fiddle" with it but without success. Could you perhaps
give
some more guidance please
SELECT [12 Month].Good, [12 Month].Decode, [12 Month].[Application Month]

FROM [12 Month] T

WHERE T.ID IN

(SELECT TOP 25 ID

FROM [12 Month] M

WHERE T.[Application Month] = M.[Application Month]

ORDER BY Good DESC)

ORDER BY [12 Month].Good DESC;

Do you have a primary key field in your table? Assuming one named ID

you
can

try this SQL:
SELECT [12 Month].Good, [12 Month].Decode, [12 Month].[Application Month]
FROM [12 Month] T
WHERE T.ID IN
(SELECT TOP 25 ID
FROM [12 Month] M
WHERE T.[Application Month] = M.[Application Month]
ORDER BY Good DESC)
ORDER BY [12 Month].Good DESC;

Duane Hookom
MS Access MVP


I have tried your suggestion and I only get back either the top 25 entries
regardless of date or alternatively the top 25 from any one
month.(depending
on what fields selected and whether ascending or descending)

I have attached the SQL in case that is of help to isolate what I am doing
wrong.(where "good" is the field on which I need to find the top 25 items
every month - not just the current month)

SELECT TOP 25 [12 Month].Good, [12 Month].Decode, [12 Month].[Application
Month]

FROM [12 Month]

ORDER BY [12 Month].Good DESC;





Open your query in design view. Set the sort for Col3 to descending.
Right
click in the query window and click on Properties in the dialog that
appears. Set the Top Values property to 25. Be sure your Month field
is
included in the query. The query will now give you the top 25

records
for

each month.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


I have a data base that imports data from another system each month.
(approx
1200 rows per month). Because of the way the system works it does
not
just
add the current month to the bottom of the table, but repopulates
the
table
from month1 (Aug 2003 - this is because certain fields may have changed
as
volumes relate to adverse reactions that have arisen because of actions
taken on a certain date.)

Because of the volume of data in the table, I need to extract just the
top
25 rows each month ie those with the highest score in column3.
Whilst I
can
get the top 25 rows for the current month, or I can get the top row
(one

row
only) for each month from Aug03 to date, I do not seem to be able to
combine
the 2 queries and get the top 25 rows every month since Aug03.

Is it possible to get back the information as detailed above, or
does
anyone
have any thoughts on a best solution to this issue.