Top Records with multiple groupings

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark Senibaldi - 22 Feb 2005 20:19 GMT

Hi,
I am trying to return the top 10 records per grouping level. My table is
tblScores which contains NAME, DATE, SCORE. I am trying to return the
previous 10 scores per Name. Any idea on how I could do this.

Signature

MSS

Reply to this Message

John Spencer (MVP) - 23 Feb 2005 00:43 GMT

Use a coordinated sub-query to identify the records you want.

SELECT [Name],[Date],Score
FROM YourTable
WHERE YourTable.[Date] in
(SELECT Top 10 Temp.[Date]
FROM YourTable as Temp
WHERE Temp.[Name] = YourTable.[Name]
ORDER BY Temp.[Date] Desc)

This will give you more than 10 items IF there are ties in the date for the last position.

> Hi,
> I am trying to return the top 10 records per grouping level. My table is
> tblScores which contains NAME, DATE, SCORE. I am trying to return the
> previous 10 scores per Name. Any idea on how I could do this.
> --
> MSS

Reply to this Message

Mark Senibaldi - 23 Feb 2005 02:23 GMT

Thanks for the response, but my SQL statement doesn't recognize Temp as a
table. I'm not exactly sure what a coordinated sub-query is, so I'm guessing
that might solve my problem?

Thanks.

> Use a coordinated sub-query to identify the records you want.
>
[quoted text clipped - 14 lines]
> > --
> > MSS

Reply to this Message

Mark Senibaldi - 23 Feb 2005 03:17 GMT

Here's my SQL statement
I'm actually using a query and NAME is actually PLAYER.

SELECT Player,Date,Score
FROM [qSel-Handicaps]
WHERE [qSel-Handicaps].Date In (SELECT Top 10 Temp.[Date]
FROM [qsel-Handicaps] as Temp
WHERE Temp.[Player] = [qsel-Handicaps].player
ORDER BY Temp.[Date] Desc);

"I get the error: The Microsoft Jet Database engine does not recognize
[qsel-Handicaps].player as a valid field name or expression."
I think the error is referring to Line 5 "Where Temp.[Player] =
[qsel-Handicaps].Player.

> Use a coordinated sub-query to identify the records you want.
>
[quoted text clipped - 14 lines]
> > --
> > MSS

Reply to this Message

John Spencer (MVP) - 24 Feb 2005 02:00 GMT

I would guess that you have a spelling error some place. Perhaps an extra space
in the word player? From what I can see, your query should work.

> Here's my SQL statement
> I'm actually using a query and NAME is actually PLAYER.
[quoted text clipped - 29 lines]
> > > --
> > > MSS

Reply to this Message

Mark Senibaldi - 24 Feb 2005 19:29 GMT

I think I fixed it by taking out the table name and just keeping the field
name in there. The query returns records, but not what I was hoping for. I
wanted to see the last 10 SCORES for each PLAYER. The last 10 SCORES should
be determined by the most recent 10 DATES for each PLAYER. I put the field
names in Caps. The Query where I'm getting all my data from is
qSel-Handicaps. Does anybody have any thoughts?

> I would guess that you have a spelling error some place. Perhaps an extra space
> in the word player? From what I can see, your query should work.
[quoted text clipped - 32 lines]
> > > > --
> > > > MSS

Reply to this Message

John Spencer (MVP) - 24 Feb 2005 23:06 GMT

No, you didn't fix it by taking out the table name. That just forced the
subquery to match player to itself and therefore didn't filter down the record
set for the current player in the main query. Try putting [Qsel-Handicaps] back
in the subquery and make sure of your spelling of the table(query) name. I
should have been clearer and told you to check both the field and table name.

An alternative woudl be to alias the tablename in the main query also.
Something like the following. Using the short alias means you are less likely
to have a spelling error in the table name.

SELECT Player,[Date],Score
FROM [qSel-Handicaps] as A
WHERE [qSel-Handicaps].Date In
(SELECT Top 10 Temp.[Date]
FROM [qsel-Handicaps] as Temp
WHERE Temp.[Player] = A.Player
ORDER BY Temp.[Date] Desc);

> I think I fixed it by taking out the table name and just keeping the field
> name in there. The query returns records, but not what I was hoping for. I
[quoted text clipped - 39 lines]
> > > > > --
> > > > > MSS

Reply to this Message

Mark Senibaldi - 25 Feb 2005 14:27 GMT

Thanks.
I re-checked all my spelling of table names and field names and everything
looked fine. I took your suggestion of using an alias for the query and the
dbase still does not recognize A.Player as a valid field name. I even tried
copying your code from below and pasting it into my SQL statement, but I'm
still receiving that same error. Would it have anything to do with trying to
put a query into the Select Top 10 criteria?

> No, you didn't fix it by taking out the table name. That just forced the
> subquery to match player to itself and therefore didn't filter down the record
[quoted text clipped - 57 lines]
> > > > > > --
> > > > > > MSS

Reply to this Message

John Spencer (MVP) - 26 Feb 2005 01:15 GMT

Well, I'm confused. If the query works without the sub-query then I don't know
what the problem is. Sorry, but I'm unable to figure this one out.

> Thanks.
> I re-checked all my spelling of table names and field names and everything
[quoted text clipped - 65 lines]
> > > > > > > --
> > > > > > > MSS

Reply to this Message

Mark Senibaldi - 17 Mar 2005 18:15 GMT

I think this might be because I am basing this off of a Cross tab query.
I know I have to declare the criteria, but I'm having trouble on what syntax
to use.
Any help would be appreciated.

Thanks,

> Well, I'm confused. If the query works without the sub-query then I don't know
> what the problem is. Sorry, but I'm unable to figure this one out.
[quoted text clipped - 68 lines]
> > > > > > > > --
> > > > > > > > MSS

Reply to this Message

John Spencer (MVP) - 20 Mar 2005 17:21 GMT

TRY opening the cross tab query and declaring the parameters there.
CLICK in the grey area of the query grid
SELECT Parameters
ENTER the exact parameter name and specify the type.

You can also do this directly in the SQL text. Something like:

PARAMETERS [SomeParameterReference] DateTime;
SELECT ....
FROM ...

> I think this might be because I am basing this off of a Cross tab query.
> I know I have to declare the criteria, but I'm having trouble on what syntax
[quoted text clipped - 75 lines]
> > > > > > > > > --
> > > > > > > > > MSS

Reply to this Message

Mark Senibaldi - 21 Mar 2005 14:46 GMT

It's working now.
Thank you very much.
All your help is much appreciated.

> TRY opening the cross tab query and declaring the parameters there.
> CLICK in the grey area of the query grid
[quoted text clipped - 86 lines]
> > > > > > > > > > --
> > > > > > > > > > MSS

Reply to this Message