Need some help with an ASP / Access DB SQL scenerio

I have a DB with a table called registrations
 
People register to tryout for our teams and I need to do a search on players from the previous year who have not registered for this year.  The issue is the team could change.

I triied this but its not working because the "OR" messes up the serahc of the previous ANDS

HELP!!!!!


set conn = server.createobject("ADODB.Connection")
myDSN = "FILEDSN="
myDSN = myDSN & server.mappath("/fpdb/registrations.dsn")
dim strSQL
dim blnk
blnk="  "
strSQL = "SELECT DISTINCT email, bday, program, regdate, lname, fname from registrations WHERE season = '2010' AND program = 'Virus' ORDER BY email"
conn.open myDSN
set rs = conn.execute(strSQL)

while not rs.eof
xdate = rs("bday")
xdate1 = DatePart("m", Now()) & "/" & DatePart("d", Now()) & "/" & DatePart("YYYY", Now())

xage = DateDiff("yyyy", xdate, xdate1)

  If rs("regdate") > #09/30/2008# Then
  If xemail <> rs("email") Then
     If rs("email") <> "" Then 
        strSQL1 = "SELECT DISTINCT email, bday, program, regdate, lname, fname from registrations WHERE email='" & rs("Email") & "' AND season = '2011' AND program = 'Virus' ORDER BY email"
        set rs1 = conn.execute(strSQL1)
        If rs1.EOF Then
           'Record does not exist
           strDistList = strDistList & rs("Email") & "; "    
           response.write rs("lname") & ", " & rs("fname") & " - " & rs("Email") & "<br>"    
           sentto = sentto + 1     
        Else
           Response.Write "Record exists" & "<br>"
        End If
     End If   
  End If  
  End If  
  xemail = rs("email")
  rs.movenext
wend

rs.close
conn.close

Open in new window

Matt PinkstonAsked:
Who is Participating?
 
worthyking1Commented:
A much easier way to do it is simply pull a single recordset of all of those people who registered last seaon that have not registered this season (for any teams at all):

"SELECT DISTINCT email, bday, program, regdate, lname, fname from registrations WHERE season = '2010' AND program = 'Virus' AND email NOT IN (SELECT DISTINCT email from registrations WHERE season = '2011' AND program = 'Virus') ORDER BY email"

Or if you need those people who registered last season for a specific team that have not registered yet this year for that same team then:

"SELECT DISTINCT email, bday, program, regdate, lname, fname from registrations WHERE season = '2010' AND email NOT IN (SELECT DISTINCT email from registrations WHERE season = '2011') ORDER BY email"
0
 
lisfolksCommented:
What 'OR' are you talking about?  I see two SQL statements in your code:


SELECT DISTINCT email, bday, program, regdate, lname, fname from registrations
 WHERE season = '2010' AND program = 'Virus'
 ORDER BY email

- and -


SELECT DISTINCT email, bday, program, regdate, lname, fname from registrations
 WHERE email='" & rs("Email") & "' AND season = '2011' AND program = 'Virus'
 ORDER BY email

Both of these look okay.

Can you give us more detail? What error message(s) do you get? Do you get results successfully, but they're just not the results you're expecting? And, what about that elusive 'OR' - where is it?

0
 
worthyking1Commented:
Just noticed that my two queries above were reversed (the top one should have been below and vice versa), but I guess you already figured that out :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.