Query doesn't work after conversion (1 Viewer)

[mugman17]

I recently converted a website that had an Access backend to SQL server. Had some queries that didn't work anymore (Mostly IIF to CASE changes). This one does not work at all anymore. Something to do with have 3 of the same table opened at once I would think.


strSQL = "SELECT tblCoaches.CoachID, tblCoaches.CoachName, tblTeamSeasons.Season, tblTeamSeasons.Wins, tblTeamSeasons.Losses, tblTeamSeasons.Ties, tblTeamSeasons.TeamID, tblCoaches_1.CoachID, tblCoaches_1.CoachName, tblCoaches_2.CoachID, tblCoaches_2.CoachName FROM tblCoaches AS tblCoaches_2 INNER JOIN (tblCoaches AS tblCoaches_1 INNER JOIN (tblTeamSeasons INNER JOIN tblCoaches ON tblTeamSeasons.Coach = tblCoaches.CoachName) ON tblCoaches_1.CoachName = tblTeamSeasons.OffensiveCoord) ON tblCoaches_2.CoachName = tblTeamSeasons.DefensiveCoord WHERE (((tblTeamSeasons.Season)=" & Request.QueryString("Season") & ") AND ((tblTeamSeasons.TeamID)= " & Request.QueryString("id") & "));"

 

[mugman17]

Basically the query works but its giving me an error when I trying to access the coach names. If I try this I get an error message:

<%=rsCoach.Fields.Item("tblCoaches_1.CoachName").value %>