Hi all, new here and to SQL so do go easy!
Wondered if anyone could help...
We have 4 weekly quizzes each month that every user accessing our site needs to complete. These all have weeknumbers from 1 to 4 depending on which week of the month it is.
We have two tables in our database, 'Questions' and 'Results'. The 'Questions' contains records for each quiz and its questions for each week of a particular month. When these records are called, they are manipulated by .asp into a form of multiple choice questions, that when completed should submit the users name, answers, and Quiz 'WeekNumber' into the 'Results' table.
What we would like our SQL to do is analyse the quizzes that the user has completed by checking for their entries in the 'Results' table and then populate our recordset with the quizzes from the 'Questions' table that are NOT shown in the 'Results' table, meaning they need to be completed. It should also not show any quizzes that are more than 28 days old.
What our SQL is actually doing, is populating our recordset with the quizzes that HAVE been completed (i.e. there are entries in the results table) twice, but giving each set of results the dates that are less than 28 days old (Currently two of these are less than 28 days old and the other two more than 28 days old)
Our Statement:
SELECT DISTINCT Questions.WeekDate, Questions.Weeknumber, Results.Weeknumber, Results.AgentName
FROM Results, Questions
WHERE Results.AgentName = 'MMColParam' AND Questions.WeekDate >= NOW() -28 AND 'Results.WeekNumber' <> 'Questions.WeekNumber'
ORDER BY Questions.WeekDate
'MMColParam' is a value for the users name pulled from a cookie. Is there anything glaringly wrong with the above statement? It's driving me nuts!
Thanks
Dave
Wondered if anyone could help...
We have 4 weekly quizzes each month that every user accessing our site needs to complete. These all have weeknumbers from 1 to 4 depending on which week of the month it is.
We have two tables in our database, 'Questions' and 'Results'. The 'Questions' contains records for each quiz and its questions for each week of a particular month. When these records are called, they are manipulated by .asp into a form of multiple choice questions, that when completed should submit the users name, answers, and Quiz 'WeekNumber' into the 'Results' table.
What we would like our SQL to do is analyse the quizzes that the user has completed by checking for their entries in the 'Results' table and then populate our recordset with the quizzes from the 'Questions' table that are NOT shown in the 'Results' table, meaning they need to be completed. It should also not show any quizzes that are more than 28 days old.
What our SQL is actually doing, is populating our recordset with the quizzes that HAVE been completed (i.e. there are entries in the results table) twice, but giving each set of results the dates that are less than 28 days old (Currently two of these are less than 28 days old and the other two more than 28 days old)
Our Statement:
SELECT DISTINCT Questions.WeekDate, Questions.Weeknumber, Results.Weeknumber, Results.AgentName
FROM Results, Questions
WHERE Results.AgentName = 'MMColParam' AND Questions.WeekDate >= NOW() -28 AND 'Results.WeekNumber' <> 'Questions.WeekNumber'
ORDER BY Questions.WeekDate
'MMColParam' is a value for the users name pulled from a cookie. Is there anything glaringly wrong with the above statement? It's driving me nuts!
Thanks
Dave
