Complex query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everybody,

I have a table storing the quiz data of the students like this:

Date | Time | Name | Grade

I can show the data of specific student by the query below:

SELECT *
FROM tbl_quiz
WHERE name ='xxx'

Suppose I have 10 records for each student, and I wanna display the
data like this:

Previous quiz date | Previous quiz time | Date | Time | Name | Grade

How can I make the "Previous quiz date" and "Previous quiz time"? How
can I query it? Please advice

FRANK
 
ONE METHOD: I would combine the Date and Time into one item. You can do the
calculation separately, but it would be more complex to do so.

SELECT C.*
, (SELECT Max([Date] + [Time])
FROM [tbl_quiz] as P
WHERE P.[Name] = C.[Name]
AND P.[Date] + P.[Time] < C.[Date] + C.[Time]) as PreviousDateTime
FROM [tbl_quiz] as C
WHERE [Name] = 'xxx'

IF you want Date and time separate
SELECT C.*
, (SELECT DateValue(Max([Date] + [Time]) )
FROM [tbl_quiz] as P
WHERE P.[Name] = C.[Name]
AND P.[Date] + P.[Time] < C.[Date] + C.[Time]) as PreviousDate
, (SELECT TimeValue(Max([Date] + [Time]) )
FROM [tbl_quiz] as P
WHERE P.[Name] = C.[Name]
AND P.[Date] + P.[Time] < C.[Date] + C.[Time]) as PreviousTime
FROM [tbl_quiz] as C
WHERE [Name] = 'xxx'
 
Hi John, thanks for your posting. I may not clearly describe my
situation. I want to show the previous quiz result with the current
quiz detail. If I show the 1st quiz result, there will be no previous
quiz date and time; If I show the 2nd quiz result, I can show 1st quiz
date and 1st quiz time as well, and so on. Is your solution still works

in this situation?

It will be much better if u can give me some idea if I would like to do
this:

Previous quiz date | Previous quiz grade | Date | Time | Name | Grade |
Next quiz date | Next quiz grade

That is, if I show the 4th quiz detail, it will display the 3rd quiz
date and grade and 5th quiz date and grade as well. Is that impossible?

Please let me know if u want more information. Many thanks.
 
Did you try the query I gave you in the earlier post? Did it work? Did it
fail? If it failed, what error message did you get?

You've now significantly changed what you are asking for. Now you want the date
for the previous test and the grade plus the next test and grade. The following
UNTESTED AIR CODE SQL may work for you. It should at least give you a start.

SELECT *

FROM (Tbl_Quiz as C LEFT JOIN

(SELECT P.*
FROM Tbl_Quiz as P
WHERE P.Date =
(SELECT Min(Date)
FROM tbl_quiz as P1
WHERE P1.Name = P.Name
AND P1.Date + P1.Time < P.[Date + P.Time) as Prior

ON C.Name = Prior.Name)

LEFT JOIN

(SELECT N.Name, N.Date, N.Grade
FROM Tbl_Quiz as N
WHERE N.Date =
(SELECT Min(Date)
FROM tbl_quiz as N1
WHERE N1.Name = N.Name
AND N1.Date + N1.Time > N.Date + N.Time) as Next

LEFT JOIN ON C.Name = Next.Name
 
Back
Top