John Spencer (MVP) said: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.
Mark said: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.
John Spencer (MVP) said: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.
Mark said: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.
Mark said: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.
John Spencer (MVP) said: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.
Mark said: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.
John Spencer (MVP) said: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.
Mark said: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.
John Spencer (MVP) said: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.
Mark Senibaldi wrote:
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.
Mark said: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?
John Spencer (MVP) said: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.
Mark said: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.
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.
Mark Senibaldi wrote:
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.
John Spencer (MVP) said: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);
Mark said: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?
John Spencer (MVP) said: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.
Mark Senibaldi wrote:
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.
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.
Mark Senibaldi wrote:
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.
Mark said: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?
John Spencer (MVP) said: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);
Mark said: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.
Mark Senibaldi wrote:
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.
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.
Mark Senibaldi wrote:
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.
John Spencer (MVP) said: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.
Mark said: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?
John Spencer (MVP) said: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);
Mark Senibaldi wrote:
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.
Mark Senibaldi wrote:
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.
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.
Mark Senibaldi wrote:
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.
Mark said: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,
John Spencer (MVP) said: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.
Mark said: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
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);
Mark Senibaldi wrote:
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.
Mark Senibaldi wrote:
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.
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.
Mark Senibaldi wrote:
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.
John Spencer (MVP) said: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 ...
Mark said: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,
John Spencer (MVP) said: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.
Mark Senibaldi wrote:
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
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);
Mark Senibaldi wrote:
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.
Mark Senibaldi wrote:
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.
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.
Mark Senibaldi wrote:
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.