I have a vb app that accesses a data base of participants who are in the db in primarily two tables, a "Roster" table and a "grades" table. This is designed to be able to track their grades from year-to-year. The "Roster" table has their first name, last name, and gender and the "Grade" table has a grade for them for each year that they have been in the database. Here is the problem that I have having:If I have two different people with the same name and gender on the same team and I call them up to list them in a list box it shows the correct name(s) and grades (even if their grades are different) but shows the same unique id for both of them. It basically shows the id that is associated with the first of the two participants.For instance, this code:sql = "SELECT r.RosterID, r.FirstName, r.LastName, r.Gender, g.Grade" & sGradeYear & " FROM Roster r INNER JOIN Grades g "sql = sql & "ON r.RosterID = g.RosterID WHERE r.TeamsID = " & lTeamID & " AND r.Archive = 'n' ORDER BY r.LastName, r.FirstName"Set rs = conn.Execute(sql)Do While Not rs.EOF lstRoster.AddItem rs(0).Value & "-" & Replace(rs(2).Value, "''", "'") & ", " & rs(1).Value & " (" & rs(3).Value & ", " & rs(4).Value & ")" rs.MoveNext Loop Set rs = Nothingcould show the following:2441-John Doe (10)2441-John Doe (11)if there were two John Doe's on this team where one was in grade 11 and the other in grade 10. I assume there is something wrong with my join but it puzzles me that it lists both of them with the correct ages but only the first RosterID.I appreciate any help you can give me!Thanks~
Getting Wrong ID on Query
Posted - 2006-09-02 : 22:22:50
