Querying results for two recent dates

[Rajtomar]

I have a table "DETAILS" in which fields are

Name Id TestDate Grade
(all fields can be duplicated)

Now I want a query which should return me

'Name' 'Id' ' TestDate' 'Grade in 2nd Last Test' 'Grade
in Latest Test'

It should be noted that every person appears the test many times and i
just want the recent two results.
The query should return names of only those people who have appeared
in either or both of the last two tests

I was trying to do this by running a query on a query but the results
were repeated again and again. Actually the query result should not
have one persons ID twice..

I am ready to hear that my table design is wrong i should make two
related tables but i'll be really thankful if someone can help me like
this..

Thanks a lot

 

[John Spencer]

To get the last two tests per individual.

SELECT [Name], ID, TestDate, Grade
FROM Details as A
WHERE TestDate in
(SELECT TOP 2 TestDate
FROM Details as B
WHERE B.[Name] = A.[Name])

To get individuals that participated in the last two texts

SELECT [Name], ID, TestDate, Grade
FROM Details as A
WHERE TestDate in
(SELECT TOP 2 TestDate
FROM Details as B)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Rajtomar]

To get the last two tests per individual.

SELECT [Name], ID, TestDate, Grade
FROM Details as A
WHERE TestDate in
   (SELECT TOP 2 TestDate
    FROM Details as B
    WHERE B.[Name] = A.[Name])

To get individuals that participated in the last two texts

SELECT [Name], ID, TestDate, Grade
FROM Details as A
WHERE TestDate in
   (SELECT TOP 2 TestDate
    FROM Details as B)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a table "DETAILS" in which fields are

Name    Id     TestDate      Grade
(all fields can be duplicated)

Now I want a query which should return me

'Name'    'Id'    ' TestDate'      'Grade in 2nd Last Test'     'Grade
in Latest Test'

It should be noted that every person appears the test many times and i
just want the recent two results.
The query should return names of only those people who have appeared
in either or both of the last two tests

I was trying to do this by running a query on a query but the results
were repeated again and again. Actually the query result should not
have one persons ID twice..

I am ready to hear that my table design is wrong i should make two
related tables but i'll be really thankful if someone can help me like
this..

Thanks a lot- Hide quoted text -

- Show quoted text -

This is not doing the trick

 

[Douglas J. Steele]

"is not doing the trick" doesn't really tell anyone anything.

What's the problem? Do you get an error? If so, what's the error? If you
don't get an error, what do you get, versus what you hoped to get?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

To get the last two tests per individual.

SELECT [Name], ID, TestDate, Grade
FROM Details as A
WHERE TestDate in
(SELECT TOP 2 TestDate
FROM Details as B
WHERE B.[Name] = A.[Name])

To get individuals that participated in the last two texts

SELECT [Name], ID, TestDate, Grade
FROM Details as A
WHERE TestDate in
(SELECT TOP 2 TestDate
FROM Details as B)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a table "DETAILS" in which fields are

Name Id TestDate Grade
(all fields can be duplicated)

Now I want a query which should return me

'Name' 'Id' ' TestDate' 'Grade in 2nd Last Test' 'Grade
in Latest Test'

It should be noted that every person appears the test many times and i
just want the recent two results.
The query should return names of only those people who have appeared
in either or both of the last two tests

I was trying to do this by running a query on a query but the results
were repeated again and again. Actually the query result should not
have one persons ID twice..

I am ready to hear that my table design is wrong i should make two
related tables but i'll be really thankful if someone can help me like
this..

Thanks a lot- Hide quoted text -

- Show quoted text -

This is not doing the trick