[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Last record on a query

Posted on 2009-04-13
12
Medium Priority
?
483 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
Independent Software Vendors: 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!

 
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

656 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