displaying database info via asp

[RFH Reseller: UKFlyer]

I have an access database which I can display then contents of. What I have done is create a relationship with one of the fields and another table within that database. I manage the local football clubs web site and the problem I had a while back - which I resolved - was there were two variables id 1 - ("location") Away id 2 -("location") Home. In the actual database itself when viewed you could select and view home and away, however when using asp instead of reading the associated field it was reading the id number. This was resolved by using this:

<%If objRS("location")="1" Then
Response.Write("Away")
else
Response.Write("Home")
End If %>

Basically if it wasn't 1 it was the other.

I have now created a new table in relation to the team. There are seventeen players. I want to be able to select the players for each match played, however same problem instead of getting the player name - as displayed in the access database via the lookup function, I get the player id. Being 11 (starting - not including reserves) and not just two I can't use the above. I could use this...
<%
If objRS("player1")="1" Then
Response.Write("Jones")
End If
If objRS("player1")="2" Then
Response.Write("White")
End If
If objRS("player1")="3" Then
Response.Write("Cooper")
End If %>

Running up to total number of players we currently have, however there is sixteen sections and everytime a new player signs - we had forty last season, I'd have to manually add them to that asp page as well as adding them to the database. I am using table players with player_id and player_name. The page is rendering the player_id number, is there a more efficient way of converting that to the players name?

Thanks in advance...

Neil

[RFH Reseller: Rappie]

Why can't pull/write/select the players name from the database? You have the players name together with the player number in a table right?

[RFH Reseller: UKFlyer]

Perhaps I didn't explain it that well. The table I'm working with is fixtures. Within that table I have listed sixteen slots to show the players that were available to play that day - eleven plus reserves. Rather that type into that table maually each of the names I have associated the table 'players'. Working directly in access I can get the player names in 'fixtures' by utilising the lookup wizard, so within the actual database itself the names are displayed. However when I create the page and ask the script to look at that record within 'fixtures' instead of giving me the player name, it gives me the id instead.

Like I said I can overcome it but I'm sure there is an easier way.....

[Staff: Warren Ashcroft]

How are you pulling the data? Via SQL Query?

[RFH Reseller: UKFlyer]

I'm using the following code:

<%
DIM mySQL, objRS
mySQL = "SELECT * FROM fixtures WHERE team =1 AND date < (now()-1)ORDER BY date Asc "
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorType = 1
objRS.Open mySQL, objConn
DIM intPageRecords, intRecords, intRecordCount, intCurrentPage
DIM intNumberOfPages, intDisplayPage
intPageRecords = Request.Querystring("page")
IF intPageRecords = "" THEN intPageRecords = 1 : intRecords = 1
intRecords = intPageRecords
intPageRecords = ((intPageRecords - 1) * 30) +1
intRecordCount = 0
IF NOT objRS.EOF THEN
objRS.Move (intPageRecords - 1)
DO WHILE intRecordCount < 30 and NOT objRS.EOF
%>

[Staff: Warren Ashcroft]

Change the SQL Query from:

SELECT * FROM fixtures WHERE team =1 AND date < (now()-1)ORDER BY date Asc

To something like (adjust the parts in bold):

SELECT fixtures.*, players.name AS playername FROM fixtures LEFT JOIN players ON fixtures.playerid = players.playerid WHERE team =1 AND date < (now()-1) ORDER BY date ASC

players = name of players table
name = name of player name column in players table.
playerid = name of player ID column in both fixtures and players tables (the link).

...you will now have a new column named "playername" for each fixture row.

[RFH Reseller: UKFlyer]

OK will give that a go ... won't be until this evening now as I have to go work ... I'll let you know how I get on.

[RFH Reseller: UKFlyer]

I get the error:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression '*players*.*player_name*'.

Within players the field names are: player_id and player_name
Within fixtures the field names are: id then player1 player 2 player 3 etc

[Staff: Warren Ashcroft]

I don't understand, what is the value of the "player1" column?

[RFH Reseller: UKFlyer]

Here's a screen shot of the actual access database. Each of the rows are a match. You have up to sixteen players listed available to play in that match. Rather than having to type out each player I can do it as a drop down where the info is drawn from the players table, however when I try to display the info in a web page it is the player_id number that is displayed and not the player name.