[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

Last record on a query

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
titorober23
Asked:
titorober23
  • 6
  • 2
  • 2
  • +1
8 Solutions
 
GRayLCommented:
Those two recordsets look the same to me.
0
 
titorober23Author Commented:
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
 
TextReportCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Patrick MatthewsCommented:
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
 
GRayLCommented:
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
 
GRayLCommented:
If your query returns the first recordset, then substitute the query name for myTable.  
0
 
titorober23Author Commented:
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
 
Patrick MatthewsCommented:
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
 
GRayLCommented:
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
 
GRayLCommented:
Are there really spaces in the field names and verify the field and table names?
0
 
TextReportCommented:
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
 
GRayLCommented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now