Solved

Last record on a query

Posted on 2009-04-13
12
477 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 125 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 75 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 100 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 100 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 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 75 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 100 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 100 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 125 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

734 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