Solved

Last record on a query

Posted on 2009-04-13
12
472 Views
Last Modified: 2012-06-27
Hi Guys

I am trying to get the last record on a query, i have been using Totals, by i am getting wrong data, maybe i am aplying it wrong

Patient      Start Date      End Date      Phase      Treatment      Evaluation      Evaluation Date      TTP
2      27-Aug-02      27-Aug-03      1      Treat - Medication Gleevec @ 400-mg      Eval - Scan Prog = -1      15-Feb-04      537
2      15-Feb-04      14-Apr-06      1      Treat - Medication Gleevec @ 400-mg      Eval - Scan Prog = -1      15-Feb-04      0
2      02-Mar-06      02-Mar-06      3      Treat - Surgery      Eval - Scan Prog = -1      15-Sep-07      562
2      15-Apr-06      14-Jun-06      3      Treat - Medication Gleevec @ 800-mg      Eval - Scan Prog = -1      15-Sep-07      518
2      15-Jun-06      19-Oct-07      3      Treat - Medication Gleevec @ 600-mg      Eval - Scan Prog = -1      15-Sep-07      457
2      15-Oct-07      15-Oct-07      4      Treat - Surgery      Eval - Scan Prog = -1      01-Apr-08      169
2      20-Oct-07      12-May-08      4      Treat - Medication Gleevec @ 400-mg      Eval - Scan Prog = -1      01-Apr-08      164
4      15-Jan-00      15-Jan-00      2      Treat - Surgery      Eval - Scan Prog = -1      15-May-01      486
4      07-Feb-00            2      Treat - Medication  @ -      Eval - Scan Prog = -1      15-May-01      463
4      01-Jun-01      14-Jul-01      3      Treat - Medication Gleevec @ 400-mg      Eval - Scan Prog = -1      15-Jul-07      2235
4      15-Jul-01      07-Apr-02      3      Treat - Medication Gleevec @ 600-mg      Eval - Scan Prog = -1      15-Jul-07      2191
4      08-Apr-02      14-Oct-06      3      Treat - Medication Gleevec @ 300-mg      Eval - Scan Prog = -1      15-Jul-07      1924
4      15-Oct-06      15-Jan-07      3      Treat - Medication Gleevec @ 600-mg      Eval - Scan Prog = -1      15-Jul-07      273
4      15-Jan-07      15-Jan-07      3      Treat - Medication Sutent @ 0-mg      Eval - Scan Prog = -1      15-Jul-07      181
4      15-Aug-07      15-Aug-07      4      Treat - No Treatment      Eval - Scan Prog = -1      23-Apr-08      252
4      15-Sep-07            4      Treat - Medication  @ -      Eval - Scan Prog = -1      23-Apr-08      221
7      04-Apr-06      14-Apr-06      1      Treat - Medication Gleevec @ 400-mg      Eval - Scan Prog = -1      13-Jul-06      100
7      15-Apr-06      14-May-06      1      Treat - Medication Gleevec @ 0-      Eval - Scan Prog = -1      13-Jul-06      89
7      15-Apr-06      14-May-06      1      Treat - Medication RAD001 @ 0-      Eval - Scan Prog = -1      13-Jul-06      89
7      15-May-06            1      Treat - Medication Sutent @ 0-      Eval - Scan Prog = -1      13-Jul-06      59
11      19-Apr-99      19-Apr-99      1      Treat - Surgery      Eval - Scan Prog = -1      15-Jun-01      788
11      09-Aug-01      01-May-07      3      Treat - Medication Gleevec @ 400-mg      Eval - Scan Prog = -1      15-Apr-07      2075
13      15-May-02      21-Nov-02      2      Treat - Medication Gleevec @ 400-mg      Eval - Scan Prog = -1      01-Oct-03      504
13      22-Nov-02      14-May-06      2      Treat - Medication Gleevec @ 600-mg      Eval - Scan Prog = -1      01-Oct-03      313



I need the last record for each patient
result should be like
Patient      Start Date      End Date      Phase      Treatment      Evaluation      Evaluation Date      TTP
2      27-Aug-02      27-Aug-03      1      Treat - Medication Gleevec @ 400-mg      Eval - Scan Prog = -1      15-Feb-04      537
2      15-Feb-04      14-Apr-06      1      Treat - Medication Gleevec @ 400-mg      Eval - Scan Prog = -1      15-Feb-04      0
2      02-Mar-06      02-Mar-06      3      Treat - Surgery      Eval - Scan Prog = -1      15-Sep-07      562
2      15-Apr-06      14-Jun-06      3      Treat - Medication Gleevec @ 800-mg      Eval - Scan Prog = -1      15-Sep-07      518
2      15-Jun-06      19-Oct-07      3      Treat - Medication Gleevec @ 600-mg      Eval - Scan Prog = -1      15-Sep-07      457
2      15-Oct-07      15-Oct-07      4      Treat - Surgery      Eval - Scan Prog = -1      01-Apr-08      169
2      20-Oct-07      12-May-08      4      Treat - Medication Gleevec @ 400-mg      Eval - Scan Prog = -1      01-Apr-08      164
4      15-Jan-00      15-Jan-00      2      Treat - Surgery      Eval - Scan Prog = -1      15-May-01      486
4      07-Feb-00            2      Treat - Medication  @ -      Eval - Scan Prog = -1      15-May-01      463
4      01-Jun-01      14-Jul-01      3      Treat - Medication Gleevec @ 400-mg      Eval - Scan Prog = -1      15-Jul-07      2235
4      15-Jul-01      07-Apr-02      3      Treat - Medication Gleevec @ 600-mg      Eval - Scan Prog = -1      15-Jul-07      2191
4      08-Apr-02      14-Oct-06      3      Treat - Medication Gleevec @ 300-mg      Eval - Scan Prog = -1      15-Jul-07      1924
4      15-Oct-06      15-Jan-07      3      Treat - Medication Gleevec @ 600-mg      Eval - Scan Prog = -1      15-Jul-07      273
4      15-Jan-07      15-Jan-07      3      Treat - Medication Sutent @ 0-mg      Eval - Scan Prog = -1      15-Jul-07      181
4      15-Aug-07      15-Aug-07      4      Treat - No Treatment      Eval - Scan Prog = -1      23-Apr-08      252
4      15-Sep-07            4      Treat - Medication  @ -      Eval - Scan Prog = -1      23-Apr-08      221
7      04-Apr-06      14-Apr-06      1      Treat - Medication Gleevec @ 400-mg      Eval - Scan Prog = -1      13-Jul-06      100
7      15-Apr-06      14-May-06      1      Treat - Medication Gleevec @ 0-      Eval - Scan Prog = -1      13-Jul-06      89
7      15-Apr-06      14-May-06      1      Treat - Medication RAD001 @ 0-      Eval - Scan Prog = -1      13-Jul-06      89
7      15-May-06            1      Treat - Medication Sutent @ 0-      Eval - Scan Prog = -1      13-Jul-06      59
11      19-Apr-99      19-Apr-99      1      Treat - Surgery      Eval - Scan Prog = -1      15-Jun-01      788
11      09-Aug-01      01-May-07      3      Treat - Medication Gleevec @ 400-mg      Eval - Scan Prog = -1      15-Apr-07      2075
13      15-May-02      21-Nov-02      2      Treat - Medication Gleevec @ 400-mg      Eval - Scan Prog = -1      01-Oct-03      504
13      22-Nov-02      14-May-06      2      Treat - Medication Gleevec @ 600-mg      Eval - Scan Prog = -1      01-Oct-03      313



Please help how to apply totals in this particular query
0
Comment
Question by:titorober23
  • 6
  • 2
  • 2
  • +1
12 Comments
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Those two recordsets look the same to me.
0
 

Author Comment

by:titorober23
Comment Utility
Sorry
i copied the wrong ones

Should be these
Patient      Start Date      End Date      Phase      Treatment      Evaluation      Evaluation Date      TTP
2      20-Oct-07      12-May-08      4      Treat - Medication Gleevec @ 400-mg      Eval - Scan Prog = -1      01-Apr-08      164
4      15-Sep-07            4      Treat - Medication  @ -      Eval - Scan Prog = -1      23-Apr-08      221
7      15-May-06            1      Treat - Medication Sutent @ 0-      Eval - Scan Prog = -1      13-Jul-06      59
11      09-Aug-01      01-May-07      3      Treat - Medication Gleevec @ 400-mg      Eval - Scan Prog = -1      15-Apr-07      2075
13      22-Nov-02      14-May-06      2      Treat - Medication Gleevec @ 600-mg      Eval - Scan Prog = -1      01-Oct-03      313
0
 
LVL 28

Accepted Solution

by:
TextReport earned 125 total points
Comment Utility
Try creating a Group By query and selecting the LAST option
Cheers, Andrew
SELECT Patient

       LAST([Start Date]) As StartDate

       LAST([End Date]) AS EndDate

       LAST([Phase]) AS LastPhase

       LAST([Treatment]) AS LastTreatment

       LAST([Evaluation]) AS LastEvaluation

       LAST([Evaluation Date]) AS EvaluationDate

       LAST([TTP}) AS LastTTP

FROM MyTable

GROUP By Patient

Open in new window

0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 75 total points
Comment Utility
Assuming "last" is indicated by Evaluation Date...

SELECT t1.*
FROM SomeTable t1 INNER JOIN
      (SELECT t2.Patient, Max(t2.EvaluationDate) AS MaxDate)
      FROM SomeTable t2
      GROUP BY t2.Patient) AS z ON t1.Patient = z.Patient And t1.EvaluationDate = z.EvaluationDate
ORDER BY t1.Patient
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 100 total points
Comment Utility
Try this:

SELECT Patient, Max([Start Date]) AS LatestStDate, [End Date], Phase, Treatment, Evaluation, [Evaluation Date], TTP FROM myTable GROUP BY Patient, [End Date], Phase, Treatment, Evaluation, [Evaluation Date], TTP;


use your real table name for myTable
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 100 total points
Comment Utility
If your query returns the first recordset, then substitute the query name for myTable.  
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:titorober23
Comment Utility
Non opf them work, for  some reason everytime that play with the totals between LAST, MAX, GROUP BY it change the recordset with no pattern
i just need the last record or the most recent acccording with the date
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 75 total points
Comment Utility
titorober23,

It would be useful to see some sample data and/or a sample file.  EE now allows you
to directly upload files to your question.

Please be advised that once you upload a file, it can be publicly accessed, and that it
may not be possible to fully and permanently delete it.  Therefore, be very careful about
posting proprietary, confidential, or other sensitive information.  If necessary, use "fake"
and/or obfuscated data in your sample.

Please note that at present EE restricts uploads to certain file types.  If your file type
does not match those in the list, you can use www.ee-stuff.com instead, which is not
officially an EE site, but is run by people connected to EE.

Regards,

Patrick
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 100 total points
Comment Utility
Based on what you provided, my solution has to work.  Check things over very carefully.  Are there really spaces in the field names and very the field and table names.
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 100 total points
Comment Utility
Are there really spaces in the field names and verify the field and table names?
0
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 125 total points
Comment Utility
Hi I have been away and would appreciate feedback on my posting http:#a24132153, this should have worked for you but you have not responded to the posting with any feedback.
Cheers, Andrew
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Thanks, but why the B?  No feedback from you over the last four posts.  BTW, the idea is to award the first correct answer, not all of them.  
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now