titorober23
asked on
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
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
GRayL
Those two recordsets look the same to me.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
i just need the last record or the most recent acccording with the date
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.