Remove duplicate rows 2

[JoPaBC]

Hello,
I have this query:
SELECT DISTINCTROW TIME.RCD_NUM, TIME.EMP_ID, TIME.PPERIOD, TIME.PRUN, TIME.TDATE, TIME.PC, TIME.RATE, TIME.HOURS, TIME.AMOUNT, TIME.JOB_ID, TIME.UPDATED, TIME.UPDATED_BY, TIME.LOG_DATE, TIME.ORIGINAL_REC_NUM
FROM [TIME]
WHERE (((TIME.EMP_ID)=376) AND ((TIME.TDATE)<=#12/31/2006# And (TIME.TDATE)>=#1/1/2006#) AND ((TIME.PC)=11 Or (TIME.PC)=151 Or (TIME.PC)=303));


it gives me this data (which is correct since the database records any changes/updates into duplicate rows and I cannot change that), but I need to remove duplicate rows (PC, RATE, HOURS, AMOUNT) for the same PPERIOD in order to get correct total AMOUNT for selected time period.

RCD_NUM EMP_ID PPERIOD PRUN TDATE PC RATE HOURS AMOUNT JOB_ID UPDATED UPDATED_BY LOG_DATE ORIGINAL_REC_NUM
145 376 200609 B 5/15/06 11 115 5 575 10 12:00:00 AM 5/14/06 12:59:28 PM
200 376 200610 B 5/31/06 11 115 15 1725 10 5/31/06 6/02/06 3:00:19 PM
813 376 200611 B 6/15/06 11 115 15 1725 10 6/15/06 6/12/06 3:00:10 PM
2110 376 200612 B 6/30/06 11 115 13 1495 10 6/30/06 7/04/06 10:12:46 AM
3714 376 200613 B 7/15/06 11 115 14.5 1667.5 10 7/17/06 4:40:16 PM LILLY 7/18/06 10:33:53 AM
3957 376 200613 B 7/15/06 11 115 14.5 1667.5 10 7/18/06 10:33:51 AM LILLY 7/18/06 10:33:59 AM
2499 376 200613 B 7/15/06 11 115 14.5 1667.5 10 7/15/06 7/17/06 4:03:29 PM
2745 376 200613 B 7/15/06 11 115 14.5 1667.5 10 7/15/06 7/17/06 4:12:04 PM
2985 376 200613 B 7/15/06 11 115 14.5 1667.5 10 7/17/06 4:12:02 PM LILLY 7/17/06 4:12:11 PM
4657 376 200614 B 7/31/06 11 115 0 0 10 7/25/06 10:54:34 AM LILLY 7/25/06 10:54:42 AM
4883 376 200614 B 7/31/06 11 115 0 0 10 7/25/06 10:54:34 AM LILLY 7/31/06 10:43:53 AM
5108 376 200614 B 7/31/06 11 115 0 0 10 7/31/06 10:43:53 AM KLAUS 7/31/06 10:43:56 AM
5335 376 200614 B 7/31/06 11 115 0 0 10 7/31/06 10:43:53 AM KLAUS
7972 376 200615 B 8/15/06 11 115 16 1840 10 8/15/06 KLAUS 8/13/06 4:23:08 PM
6746 376 200614 B 7/31/06 11 115 16 1840 10 7/31/06 12:28:08 PM KLAUS 7/31/06 12:32:07 PM
6983 376 200614 B 7/31/06 11 115 16 1840 10 7/31/06 12:32:06 PM KLAUS 7/31/06 12:32:10 PM
7220 376 200614 B 7/31/06 11 115 16 1840 10 7/31/06 12:32:06 PM KLAUS 7/31/06 12:35:14 PM
7457 376 200614 B 7/31/06 11 115 16 1840 10 7/31/06 12:35:13 PM KLAUS 7/31/06 12:35:17 PM
7679 376 200615 B 8/15/06 11 115 16 1840 10 8/15/06 KLAUS 8/12/06 4:47:40 PM
8230 376 200615 B 8/15/06 11 115 15 1725 10 8/13/06 4:23:06 PM LILLY 8/13/06 4:23:15 PM
8478 376 200615 B 8/15/06 11 115 15 1725 10 8/13/06 4:23:06 PM LILLY 8/13/06 4:29:41 PM
9478 376 200616 B 8/31/06 11 115 15 1725 10 8/31/06 LILLY 9/02/06 3:03:41 PM
8726 376 200615 B 8/15/06 11 115 15 1725 10 8/13/06 4:29:38 PM LILLY 8/13/06 4:29:47 PM
8975 376 200615 B 8/15/06 11 115 15 1725 10 8/13/06 4:29:38 PM LILLY 8/14/06 10:24:32 AM
9224 376 200615 B 8/15/06 11 115 15 1725 10 8/14/06 10:24:30 AM LILLY 8/14/06 10:24:38 AM
9746 376 200616 B 8/31/06 11 115 15 1725 10 8/31/06 LILLY 9/02/06 3:07:31 PM
10007 376 200616 B 8/31/06 11 115 15 1725 10 9/02/06 3:07:28 PM LILLY 9/02/06 3:07:38 PM
10268 376 200616 B 8/31/06 11 115 15 1725 10 9/02/06 3:07:28 PM LILLY 9/02/06 3:33:52 PM
10529 376 200616 B 8/31/06 11 115 15 1725 10 9/02/06 3:33:50 PM LILLY 9/02/06 3:33:59 PM
10761 376 200617 B 8/31/06 11 115 15 1725 10 9/15/06 LILLY 9/14/06 2:08:11 PM
10969 376 200617 B 8/31/06 11 115 15 1725 10 9/15/06 LILLY 9/14/06 2:21:41 PM
11160 376 200617 B 8/31/06 11 115 15 1725 10 9/14/06 2:21:39 PM LILLY 9/14/06 2:21:47 PM
11371 376 200617 B 8/31/06 11 115 15 1725 10 9/14/06 2:21:39 PM LILLY 9/14/06 3:09:11 PM
11572 376 200617 B 8/31/06 11 115 15 1725 10 9/14/06 3:09:09 PM LILLY 9/14/06 3:09:17 PM
11756 376 200618 B 9/30/06 11 115 7 805 10 9/30/06 LILLY 9/25/06 2:37:25 PM
11853 376 200618 B 9/30/06 151 0 0 1305 10 9/30/06 LILLY 9/25/06 2:37:29 PM
11854 376 200618 B 9/30/06 303 0 0 652.5 10 9/30/06 LILLY 9/25/06 2:37:29 PM
11893 376 200618 B 9/30/06 11 115 7 805 10 9/30/06 LILLY 9/25/06 2:57:20 PM
11894 376 200618 B 9/30/06 151 0 0 1305 10 9/30/06 LILLY 9/25/06 2:57:20 PM
11895 376 200618 B 9/30/06 303 0 0 652.5 10 9/30/06 LILLY 9/25/06 2:57:20 PM
12009 376 200618 B 9/30/06 11 115 7 805 10 9/25/06 2:57:19 PM LILLY 9/25/06 2:57:24 PM
12010 376 200618 B 9/30/06 151 0 0 1305 10 9/25/06 2:57:19 PM LILLY 9/25/06 2:57:24 PM
12011 376 200618 B 9/30/06 303 0 0 652.5 10 9/25/06 2:57:19 PM LILLY 9/25/06 2:57:24 PM
12115 376 200618 B 9/30/06 11 115 7 805 10 9/25/06 2:57:19 PM LILLY 9/25/06 3:02:42 PM
12116 376 200618 B 9/30/06 151 0 0 1305 10 9/25/06 2:57:19 PM LILLY 9/25/06 3:02:42 PM
12117 376 200618 B 9/30/06 303 0 0 652.5 10 9/25/06 2:57:19 PM LILLY 9/25/06 3:02:42 PM
12221 376 200618 B 9/30/06 11 115 7 805 10 9/25/06 3:02:41 PM LILLY 9/25/06 3:02:45 PM
12222 376 200618 B 9/30/06 151 0 0 1305 10 9/25/06 3:02:41 PM LILLY 9/25/06 3:02:45 PM
12223 376 200618 B 9/30/06 303 0 0 652.5 10 9/25/06 3:02:41 PM LILLY 9/25/06 3:02:45 PM

Any idea/help, please?

 

[JoPaBC]

I tried to modify the original Duane's query, which works perfectly for one employee:
SELECT *
FROM TIME1
WHERE RCD_NUM = (SELECT Max(RCD_NUM) FROM [TIME1] UQ WHERE UQ.PPERIOD = [TIME1].PPERIOD AND UQ.PC = [TIME1].PC);

with input date from this query:
SELECT TIME.RCD_NUM, TIME.EMP_ID, TIME.PPERIOD, TIME.PRUN, TIME.TDATE, TIME.PC, TIME.RATE, TIME.HOURS, TIME.AMOUNT, TIME.JOB_ID, TIME.UPDATED, TIME.UPDATED_BY, TIME.LOG_DATE, TIME.ORIGINAL_REC_NUM
FROM [TIME]
WHERE (((TIME.EMP_ID)=376) AND ((TIME.TDATE)<=#12/31/2006# And (TIME.TDATE)>=#1/1/2006#) AND ((TIME.PC)<599));

to make it work for more employees at once:
SELECT [EMP_ID], [PPERIOD], [AMOUNT]
FROM TIME1
WHERE RCD_NUM = (SELECT Max(RCD_NUM) FROM [TIME1] UQ WHERE (( UQ.PPERIOD = [TIME1].PPERIOD) AND (UQ.PC = [TIME1].PC) AND (UQ.EMP_ID = [TIME1].EMP_ID)));

or

SELECT *
FROM TIME1
WHERE RCD_NUM = (SELECT Max(RCD_NUM) FROM [TIME1] UQ WHERE (( UQ.PPERIOD = [TIME1].PPERIOD) AND (UQ.PC = [TIME1].PC) AND (UQ.EMP_ID = [TIME1].EMP_ID)));


in both cases using input data from this query:
SELECT DISTINCTROW TIME.RCD_NUM, TIME.EMP_ID, TIME.PPERIOD, TIME.TDATE, TIME.PC, TIME.AMOUNT
FROM [TIME]
WHERE (((TIME.TDATE)<=#12/31/2006# And (TIME.TDATE)>=#1/1/2006#) AND ((TIME.PC)<599));

but it makes whole MS Access 'not responding' each time.

I know it is a long threat now, but if you still can help, please...

 

[SkipVought]

I was trying to answer this additional requirement…

For example I need to all these three rows for PPERIOD 200618 (PC - 11, 151, 303)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JoPaBC]

Yes, that requirement was solved in Duane's query and this condition (TIME.PC)<599 in the input query. That part working OK

But when taking another step, got stuck with multiple employees query/report

 

[SkipVought]

Yes, that requirement was solved in Duane's query and this condition (TIME.PC)<599 in the input query.

???

You NEVER, EVER, stated that this was the requirement that got you...

For example I need to all these three rows for PPERIOD 200618 (PC - 11, 151, 303):

That’s why I subsequently stated:

You really need to clearly, completely and concisely state your requirements, not just a specific instance, although an example is always helpful.

I hope that when you post another thread (and I sincerely hope that you will) that you will think carefully regarding the clarity, completeness and crispness of your requirement. That’s why I spent so much time trying to meet your previously stated requirement before you basically said…
Oh, by the way, I plugged in this previously UNSTATED requirement.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Please post your SQL code that demonstrates what you did...

Yes, that requirement was solved in Duane's query and this condition (TIME.PC)<599 in the input query.

I guess I'm not plugging it in the right place.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]