Querying results for two recent dates

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
0
Rajtomar
11/20/2009 12:24:54 PM
access 16762 articles. 3 followers. Follow

3 Replies
771 Views

Similar Articles

[PageSpeed] 10

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 wrote:
> 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
0
John
11/20/2009 2:46:56 PM
On Nov 20, 7:46=A0pm, John Spencer <spen...@chpdm.edu> wrote:
> To get the last two tests per individual.
>
> SELECT [Name], ID, TestDate, Grade
> FROM Details as A
> WHERE TestDate in
> =A0 =A0(SELECT TOP 2 TestDate
> =A0 =A0 FROM Details as B
> =A0 =A0 WHERE B.[Name] =3D A.[Name])
>
> To get individuals that participated in the last two texts
>
> SELECT [Name], ID, TestDate, Grade
> FROM Details as A
> WHERE TestDate in
> =A0 =A0(SELECT TOP 2 TestDate
> =A0 =A0 FROM Details as B)
>
> John Spencer
> Access MVP 2002-2005, 2007-2009
> The Hilltop Institute
> University of Maryland Baltimore County
>
>
>
> Rajtomar wrote:
> > I have a table "DETAILS" in which fields are
>
> > Name =A0 =A0Id =A0 =A0 TestDate =A0 =A0 =A0Grade
> > (all fields can be duplicated)
>
> > Now I want a query which should return me
>
> > 'Name' =A0 =A0'Id' =A0 =A0' TestDate' =A0 =A0 =A0'Grade in 2nd Last Tes=
t' =A0 =A0 '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
0
Rajtomar
11/27/2009 1:08:18 PM
"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
http://I.Am/DougSteele
(no e-mails, please!)


"Rajtomar" <rajtomar219@gmail.com> wrote in message 
news:7c4ac346-a2dc-4a5e-85fd-efa31fc4a02e@u1g2000pre.googlegroups.com...
On Nov 20, 7:46 pm, John Spencer <spen...@chpdm.edu> wrote:
> 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 wrote:
> > 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 


0
Douglas
11/27/2009 2:15:22 PM
Reply:

Similar Artilces:

Tasks with no due date at the bottom?
Is there any way to convince Outlook 2000 to show tasks with no due date at the bottom of the task list -- when the sort is on by due date, ascending? I know if it's sorted by due date descending, the no-due-date tasks will be at the bottom, but that's just not it; what I need is a view sorted by due date, ascending, with no-due-date tasks at the bottom. I suppose it seems quite logical: the closes due dates on top, the farthest due dates lower down the list, the ones without due dates at the very bottom. I would love to do the same with Outlook 2003. I think the answer is that...

Thanks for all the help on Year-to-date query!
It's so great to know that there are folks out there that can take the time to help someone whose 78 year old brain is slowly turning to gorgonzola cheese! Again, thanX Mikie Francisco 9 @ ATT N E T ...

Query from Multiple Workbooks
Hello, I have 12 files in a directory with a sheet name Sales containing Sales data for each month having more than half a million rows in each workbook sheets The field names are all the same in all the files like date, item, itemgroup, branch, unit, salesman, amount etc I am looking for a way to get a query from all these workbook and get a summarised report based on criteria like sales by salesman, by month, by product, by product group, by date, by branch etc. Is this will be possible through code?. Itry to use MS query but not successful Using Excel 2007 and Win XP ...

Date in an Edit Control
Hi i would like to enter a Date value into an edit box To get very spcific... i have an edit box when selected ,the user must be able to just select the date he requires such as 11 02 04 ,.....only the numbers are required and this has to be stored into a fil See this link http://www.codeproject.com/editctrl/datetimedit.asp "NoviceJohn" <anonymous@discussions.microsoft.com> wrote in message news:602F22CD-C213-4F15-B007-39DA6A459BC9@microsoft.com... > Hi i would like to enter a Date value into an edit box. > > To get very spcific.... > i have an edit box when...

Dates II
Using Excel 2002. The data in my spreadsheet is categorized by year (YYYY), but the years that I'm using are not sequential. I would like to create a line graph with marker points at years 1990, 2000, 2004, 2005, 2006, and 2007. I'd also like there to be a lot of white space between the 1990 and 2000 labels, a little less between 2000-2004, and even less than that between the single years. I can get the markers on the line chart to appear at the proper intervals, but I can't get the associated labels--and only those labels--to appear. When I select time-scale, it forces...

Installed MS Office and Recent Documents Keep Disappearring
Having Windows 7 on new machine, installed MS Office 2007, but have a problem with program displaying recent documents. Set up shows 17 documents in Advanced settings. Have to open documents from Word/Excel directory, and it then shows in recent document list. Remains listed for the day. load up next day, and no documents listed. If anyone can advise what can be done to correct would be appreciated. Regards Phil B ...

match dates in two columns get value from third
I have a data set on daily basis and another data set on weekly basis. i want to put the weekly data in daily data set by putting a formula which picks values from col c for relevant dates in daily set. example - let us say this is the data set ColA Col B Col C jan1 jan1 8 jan2 jan7 7 jan3 jan4 jan5 jan6 jan7 I want the data from colC against dates in ColA as in ColD below. it should leave other cells blank (colA and ColB are dates format) ColA Col B Col C ColD jan1 jan1 ...

importing lot expiration dates through Integration Mgr.
Does anyone out there in Microsoft Land have any inkling as to why Integration Manager does not offer the ability to import in expiration dates for lot numbers? What you have to do, after you run the inventory transaction integration, is to run a script to update the expiration date with the date that you previously imported into the Lot Number Attribute window. Why does the tool just simply not let you map to the Expiration Date field in the Lot Number Attribute window? Would that not be a simple yet good idea? childofthe1980s If it was that simple, working with GP won't be a...

How to link Subtotal results?
How do i use subtotal function results from one worksheet in anothe work sheet. I am able to work out average price of raw materals brough but would liked it automatically linked to my profit forecast and actua sales worksheet. Or is there an easier way for me to have stock control and sales dat linked -- bugg ----------------------------------------------------------------------- buggu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3666 View this thread: http://www.excelforum.com/showthread.php?threadid=56412 If you want to link to a cell you can just co...

Query to Redistribute a Table
Hi I am struggling with how to query a table with the current structure: Field1 Field2 ...Field14 Jan Feb Mar Apr... x y ...z 10 15 13 16... etc, It represents sales per month to customers per delivery adress, product and so forth.The numbers under each month respresents the sales for that month. I need to be able to query so I can see all detalis (ie fileds 1-14) per row per month, ie redistribute that original table so I can use month as a field and still use all the original fields. My new table needs to be something link Month Field 1 Field2 ... Field14 New ...

merging two accounts
This seems like something that should be easy, but it's totally stumped me. I use my laptop at home and at work. At work, I log onto a domain, so that I actually have two accounts on my laptop: "username" and "username.domain". So far so good. My problem is that I use the same email accounts both at work and at home. To avoid having two independent Outlooks on my computer that check the same email accounts, I moved the data file (.pst) locations of both accounts to one place. This worked fine, and when I opened Outlook from both accounts, it shows the sa...

How to create report from Pivot Table View of Union Query?
Hi all, I have create a Union ALL Query. I want to generate a report from it. The Pivot Table View of this Union Query shows exactly what I want (having Row Heading, Column Heading and Detailed Data), so how can I make it as a Report? I have tried to create new report, but there is no options of making a pivot table (something like Crosstab Query and Pivot Table View of the Union Query). Also tried to export to Excel, but it shows only count of records. I want all detailed records. Thank you. -- Message posted via http://www.accessmonster.com Hi Duane, I tried to create a crosstab qu...

NESTED IF STATEMENT USING DATE
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C7B250.64254380 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello! Can some review the following IF statement. The error is stating that = there are too many arguments. =3DIF((TEXT((D2),"yyyy")=3D"2007"),"2007",if(TEXT(d2),"yyyy")=3D"2006","2= 006","2005 & Before") If the year of the date is 2007, state "2007", If the year of the date = is 2006, state "2006", otherw...

Epay dates
When I file an epay payment, I put in the due date. For some reason, the program puts in the current date. For example, if I pay a bill today that is due on July 15, I put in 7/15. When the payment is submitted, the program changes the date to 7/1 (today). The problem is, when the program tracks my balance, it looks like the 7/15 bill has already been paid. That makes my balance look lower--and often overdrawn. I want the date of an epay to stay the date the payment will be made. I cannot find any setting to make the program do this and the help is no help. --DS ...

Formatting a date field
Is it possible to format a date field in excel such that :- 1) it accepts day, month, year or month, year or just year or blank and 2) you are able to sort properly on the field Bob M Formatting has no effect on XL's input parser. You may find you can work with this: http://cpearson.com/excel/DateTimeEntry.htm In article <f1pb6t$bik$1@lust.ihug.co.nz>, "Bob Matthews" <matthews@es.co.nz> wrote: > Is it possible to format a date field in excel such that :- > 1) it accepts day, month, year or month, year or just year or blank and > 2) you are able...

A Template or a Macro with the current date
Hi, Can i add the current date to a new template of a new macro? That is, i want to have a template where the current date is already mentioned.(or a macro that added the current date together with other features) When i tried to replay a macro of the add current date shortcut (ctrl+;) it added the same date that was recorded already and not the current one. Thanks a lot, Iris. Iris One way: Range("A1") = Date HTH Otto "iris" <sassonir@bgumail.bgu.ac.il> wrote in message news:18fcd0e0.0309291307.8079802@posting.google.com... > Hi, > > Can i add the ...

Make payroll tax tables date sensitive
As I understand it, if the tax tables were date sensitive, we would be able to download the new year's tax tables and begin the new year's payroll before the year end wage file was created for the previous year. This would take a lot of the panic out of payroll year end. It would also enable our client who is still running 2007 payrolls in some companies after the first 2008 payroll in other companies to run without problems. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, ...

how to create item numbers column in a Query
Is there anyway to place a column (Item#) in a query, which will show the item numbers of the results. Say that, if query result has 13 items, Item# values will be sequentially 1,2,3...11,12,13. I have a feeling that it might be solved by usind DCOUNT function. Thanks ...

How to change date as general "200306" to date "06/2003"
I recieve data with a date formated as general that looks like "200306". I need to change it to a date format that looks like this "06/2003" How do I do this =DATE(LEFT(A2, 4),RIGHT(A2,2),1) This will actually return a date for Jun 1, 2003, which you can format for "06/2003" with Format - Cells - Number - Custom: mm/yyyy. Or if you don't want a for-real Excel date, you can just convert the text string: =RIGHT(A2,2) & "/" & LEFT(A2,4) -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------...

Calculate As of Date Check Book Opening Balance
Hi Is there any way to calculate as of opening and closing balance of a given check for Payments and Deposits Listing report Module = Banak Management Regards Irfan ...

FRx Report with Date Range
Guys, When im running a report in FRx 6.7, we want a report for a specified date range. It normally takes a defualt date range, where in i want a specified date range to be given. Eg: 15 Jan 2005 to 15 March 2005. Is this possible, i tried creating a T_ATTR in the column format but the look up window does not have anything. THanks in advance. You can specify specific dates in the Column Layout. "Ram" wrote: > Guys, > > When im running a report in FRx 6.7, we want a report for a specified date > range. > > It normally takes a defualt date range, where...

Using IF formula with dates
Hi there, Can anyone help! I am trying to create the following formula : A1 = 01.08.02 IF (A1="01.08.02","N","S") As A1 does = 01.08.02 it should have "N" in the cell which I am usin the formula in shouldn't it ? or is there something I'm missing. The dates are exactly the same format in the formula and in the A cell. Any help would be very much appreciated!! ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Availab...

Putting the date as a sheet name
Hi I've created some macros that create different sheets within a file. However, i'd like each sheet to display the date along with a pre defined name. Is it possible to incorporate this into the macro.....if so, how would i do it? Thanks Matt -- matthewwookie ------------------------------------------------------------------------ matthewwookie's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27503 View this thread: http://www.excelforum.com/showthread.php?threadid=470722 Hello Matt You don't say how you have created your sheets, so provided...

Form Results and scroll bars...
Can form results be displayed using scroll bars, rather than limiting number of lines? TIA David Use a scrollable <div> set to the required height and/or width: <div style="height: 500px; overflow: auto"> Form results go here </div> Not sure what you mean by "limiting number of lines" -- Ron Symonds Microsoft MVP (Expression Web) http://www.rxs-enterprises.org/fp Reply only to group - emails will be deleted unread. "116" <116@discussions.microsoft.com> wrote in message news:FE975DC3-C2A3-4599-A120-8324...

Dates and Math
I have two columns listing date and time in the MM/DD/YYYY TT:TT:TT format. I would like to subtract one from the other to get the time it takes to do X. Any ideas? Hi try =A2-A1 and format with the custom format [hh]:mm -- Regards Frank Kabel Frankfurt, Germany "jimlib" <j1ml1b@hotmail.com> schrieb im Newsbeitrag news:d5b18b0e.0411191423.1ee756dd@posting.google.com... > I have two columns listing date and time in the MM/DD/YYYY TT:TT:TT > format. I would like to subtract one from the other to get the time it > takes to do X. Any ideas? ...