Access SQL / ASP - Value not ID

[XtreamXTC]

I apologise in advance at the simplicity of my question, but I'm stumped if I can figue it out! (-Newb-).

My database has 2 tables and a query:

tblPlayers (contains a rsPlayerID and rsPlayerName)
tblResults (contains rsPlayerName lookup to tblPlayers, rsPlayed and rsWon)

qryPlayers - Looks at tblResults and sums the played and won for each player.

Now I execute the query as thats what I want to display but when I run the SQL, I get back the PlayerID as opposed to the value in rsPlayerName.

Example

set rslea = server.createobject("ADODB.recordset")
sqllea = "EXEC qryPlayersTable"
sqllea2 = "SELECT * FROM qryPlayersTable ORDER BY WinPer DESC, SumOfWon DESC, PlayerName ASC"

I've trawled the net to try and find the answer, I simply want to convert the Primary key (Player_ID) back to the PlayerName contained in tblPlayers.

Thanks in advance!

 

[ChrisHirst]

assuming your Access SP (query) returns the recordset

set objRS = objConn.execute("exec qryPlayersTable")

rename your objects to suit.

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[Tarwn]

rsPlayername as a field in your Results table, was this supposed to be rsPlayerID?

If so, then (guessing) your query probably should look something like:
SELECT tblPlayers.rsPlayerName, tblPlayers.rsPlayerID, SUM(tblResults.rsPlayed) as TotalPlayed, SUM(tblResults.rsWon) as TotalWon
FROM tblResults INNER JOIN tblPlayers ON tblResults.rsPlayerID = tblPlayers.rsPlayerID
GROUP BY tblResults.rsPlayerID, tblPlayers.rsPlayerName, tblPlayers.rsPlayerID

The Join between the two tables on the ID field is what defines how the records from one table are linked to the other, allowing us to get the player name from the Players table and link it to the correct SUM from the Results table. I have made this an INNER JOIN in the example above, meaning that in order for a record to be returned in the final resultset there must be data on both sides of the join to build that record (ie, if there is a player with no results record there is nothing to JOIN, so they will not be included).

If I have made any incorrect assumptions, please correct them so we can offer more accurate advice,

-T

 

[XtreamXTC]

Chris & Tarwn - Thanks for the replies

Tarwn, That code works brilliant! Many thanks for that.

I guess you've probably answered my question, in that its best to have the SQL coded into the ASP rather than use the access queries and then try and pull them in?