Querying results for two recent dates


R

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
 
Ad

Advertisements

J

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
 
R

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
 
Ad

Advertisements

D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top