Problem with Query?

[Zachary Hauri]

I'm having a problem with the following query using VB.

"SELECT games.* FROM Games, Log ORDER BY Log.logID DESC
LIMIT 1,10"

If I run the VB, it says "Syntax error in ORDER BY
clause." (runtime error 3138), but I don't see any obvious
errors in my syntax. Should there be something in
between "ORDER BY Log.logID DESC" and "LIMIT 1,10"?

 

[Nick HK]

Zachary,
Considering that you are not selecting anything from the Log table, there's
no way to ORDER BY Log.logID. If there is a foreign key in Games to the Log
table then ORDER BY Games.logID should give you what you want. And FROM Log
is not needed.

NickHK


| I'm having a problem with the following query using VB.
|
| "SELECT games.* FROM Games, Log ORDER BY Log.logID DESC
| LIMIT 1,10"
|
| If I run the VB, it says "Syntax error in ORDER BY
| clause." (runtime error 3138), but I don't see any obvious
| errors in my syntax. Should there be something in
| between "ORDER BY Log.logID DESC" and "LIMIT 1,10"?

 

[Zachary Hauri]

What I'm actually trying to do is get the last 10 records
in the Log table, where logID is an AutoNumber, gameID is
linked to Games.gameID, and gameTime is an integer (how
long the game was run).

So, if I only want to get the last 10 games ran (which I
assume would be possible by selecting the last 10 records
in Log that have unique gameIDs), how would I form my SQL
statement?

 

[John Spencer (MVP)]

I'm confused as to your data structure, but you need to use TOP in your query.
GUESSING at the SQL

SELECT Games.*
FROM Games
WHERE Games.GamesID IN (
SELECT TOP 10 Log.GamesID
FROM LOGID
ORDER BY Log.LogID Desc)