?
Solved

Need some help with an ASP / Access DB SQL scenerio

Posted on 2011-04-24
3
Medium Priority
?
232 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Matt Pinkston
  • 2
3 Comments
 
LVL 9

Expert Comment

by:lisfolks
ID: 35456865
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
 
LVL 6

Accepted Solution

by:
worthyking1 earned 2000 total points
ID: 35462015
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
 
LVL 6

Expert Comment

by:worthyking1
ID: 35487740
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I used to be SharePoint evangelist in our company, so my Outlook always full of questions about how to do this, or where I can find that. One day I found such an email with the following question: "how to attach 3-State workflow (one of the workflow…
SharePoint Designer 2010 has tools and commands to do everything that can be done with web parts in the browser, and then some – except uploading a web part straight into a page that is edited in SPD. So, can it be done? Scenario For a recent pr…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Loops Section Overview
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question