?
Solved

Last record on a query

Posted on 2009-04-13
12
Medium Priority
?
480 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +1
12 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 24131984
Those two recordsets look the same to me.
0
 

Author Comment

by:titorober23
ID: 24132130
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 500 total points
ID: 24132153
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 300 total points
ID: 24132164
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 400 total points
ID: 24132211
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 400 total points
ID: 24132357
If your query returns the first recordset, then substitute the query name for myTable.  
0
 

Author Comment

by:titorober23
ID: 24140012
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 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 300 total points
ID: 24140645
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 400 total points
ID: 24142373
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 400 total points
ID: 24142382
Are there really spaces in the field names and verify the field and table names?
0
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 500 total points
ID: 24159494
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
ID: 24295552
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

Use Filtering Commands to Process Files in Linux

Learn how to manipulate data with the help of various filtering commands such as `cat`, `fmt`, `pr`, and others in Linux.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

800 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