Creating Samples listings from Categories in Jet SQL: Clevernessfailure

  • Thread starter Thread starter Ion Chalmers Freeman
  • Start date Start date
I

Ion Chalmers Freeman

OK, I have something that would be a little trivial in a SQL Server
Stored Procedure that's a little hard with an Access query.
What I want to do is get sample lists corresponding to sample. I forget
the cursor syntax, but I'd open one with
SELECT DISTINCT @Strat = STRATEGY FROM PLAYER
and inside a loop another with
SELECT TOP 10 @Player = PlayerID FROm PLAYER WHERE STRATEGY = @Strategy
and in the inner loop
SELECT Player.Strategy, A & B & C & D,
Distance FROM (FLIGHTS INNER JOIN PLAYER ON FLIGHTS.PlayerID =
Player.PlayerID) Inner join GAME ON Flights.GameID = Game.GameID
WHERE GAme.TrialCount = 3 AND Strategy = @Strat WHERE FLights.PlayerID =
@PLAYER

There may be a cleverer way, but I'm not in a clever space. That
cleverer way, of course, is what I need to do it in Jet-SQL. Can I do
this in Access, or do I need to call in a little VB?
ion
 
Hi,


SELECT Player.Strategy, A & B & C & D, Distance
FROM ((flights INNER JOIN player ON flights.PlayerID=Player.PlayerID)
INNER JOIN game ON flights.GameID=game.GameID
WHERE game.TrialCount=3

is fine, but then, you add

AND Strategy = @Strat and @Strat is from table Player. From what table
comes Strategy? from Player too? then you are asking:
Player.Strategy=Player.Strategy ? it should return true, unless you use
Nulls. The condition is probably useless.

You continue with a second WHERE clause??? probably a typo, just an AND:

AND flights.PlayerID = @Player
but @player is part of SELECT TOP 10 PlayerID FROM player. We already
have a join between flights and player. If you just want to consider the top
10 player, use a virtual table, here aliased as TopPlayers, instead of
Player:

SELECT TopPlayers.Strategy, A & B & C & D, Distance
FROM ((flights INNER JOIN
( SELECT TOP 10 * FROM Player ORDER BY ??? ) As TopPlayers
ON flights.PlayerID=TopPlayers.PlayerID)
INNER JOIN game ON flights.GameID=game.GameID
WHERE game.TrialCount=3



Note that when you use a TOP n, you also need an ORDER BY clause (absent
from your initial post).



In general, it is greatly preferable to say WHAT we want to get rather than
HOW we want to get it, in SQL, and in a newsgroup. Failing to do that
produce huge typos and syntax errors, which increase the "guessing" part,
rather than help to understand, your message :-)


Hoping it may help,
Vanderghast, Access MPV
 
Back
Top