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]

Note: The line with the highest RCD_NUM contains the correct values

for example for PPRIOD 200615 the correct row wold be the last one (RCD_NUM 9224)

RCD_NUM EMP_ID PPERIOD PRUN TDATE PC RATE HOURS AMOUNT JOB_ID UPDATED UPDATED_BY LOG_DATE ORIGINAL_REC_NUM
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

 

[dhookom]

You need to use TGML pre tags to format your records so they can be read without guessing.

[pre]

RCD_NUM EMP_ID PPERIOD PRUN TDATE PC RATE HOURS AMOUNT JOB_ID UPDATED UPDATED_BY LOG_DATE ORIGINAL_REC_NUM
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[/pre]

I expect you want to return only the

SELECT *
FROM [YourQuery]
WHERE RCD_NUM = (SELECT Max(RCD_NUM) FROM [YourQuery] UQ WHERE UA.PPERIOD = [YourQuery].PPERIOD)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SkipVought]

@Jozef,

I notice that you've been posting here at Tek-Tips for about two years and apparently got some good answers, even from Duane in the past.

One way of expressing thanks for a particularly helpful suggestion is to use the Great post! button in the Lower Right-Hand corner of each reply block. These Little Purple Stars act both as a "Thank You!" and as a flag identifier for other members who are browsing for Good Tips.

@Duane, did you mean...

SELECT *
FROM [YourQuery] [b]UA[/b]
WHERE RCD_NUM = (SELECT Max(RCD_NUM) FROM [YourQuery] UQ WHERE UA.PPERIOD = [b]UQ[/b].PPERIOD)

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

 

[JoPaBC]

Thank you.
Sorry for TGML pre tags, when I clicked preview it showed lines correctly.

Your query works almost as I wanted, except I need to include all rows with all different PC.
For example I need to all these three rows for PPERIOD 200618 (PC - 11, 151, 303):

RCD_NUM EMP_ID PPERIOD PRUN TDATE PC RATE HOURS AMOUNT
12221 376 200618 B 30-Sep-06 11 115 7 805
12222 376 200618 B 30-Sep-06 151 0 0 1305
12223 376 200618 B 30-Sep-06 303 0 0 652.5

 

[dhookom]

Thanks Skip,

I should have used UQ in both places in the subquery.

Jozef, please try posting again using TGML tags and all requirements.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[JoPaBC]

Yes, I had TGML checked when I copied from Access and Excel. Thanks.
I noticed that UA vs UQ mistake and fixed that.

Only issue now is I need to include all rows with all different PC.
For example I need to all these three rows for PPERIOD 200618 (PC - 11, 151, 303):

RCD_NUM EMP_ID PPERIOD PRUN TDATE PC RATE HOURS AMOUNT
12221 376 200618 B 30-Sep-06 11 115 7 805
12222 376 200618 B 30-Sep-06 151 0 0 1305
12223 376 200618 B 30-Sep-06 303 0 0 652.5

 

[SkipVought]

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

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

 

[dhookom]

Please preview your posts prior to submitting them. I do and expect Skip does.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[JoPaBC]

I did preview, it looked like this:

My TGML is checked, am I doing anything wrong?

 

[JoPaBC]

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

I agree, but sometimes it is hard to expect all possible scenarios. I found the additional requirements after I got working the first update, sorry :-(

 

[dhookom]

You need to select a section of text in your reply and then click a format/TGML icon so your columns appear like columns and not run-on text. You should be able to see the difference between your post and my post. Without using TGML all multiple spaces are replaced by a single space.

Does this SQL meet your requirements?

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

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[JoPaBC]

Yes, this last query does exactly what I wanted. Thank you!


PS: Below is a sample, just trying to properly use TGML. Hope it works correctly this time.

RCD_NUM EMP_ID PPERIOD PRUN TDATE PC RATE HOURS AMOUNT
145 376 200609 B 15-May-06 11 115 5 575.00
200 376 200610 B 31-May-06 11 115 15 1,725.00
813 376 200611 B 15-Jun-06 11 115 15 1,725.00
2110 376 200612 B 30-Jun-06 11 115 13 1,495.00
3957 376 200613 B 15-Jul-06 11 115 14.5 1,667.50
7457 376 200614 B 31-Jul-06 11 115 16 1,840.00
9224 376 200615 B 15-Aug-06 11 115 15 1,725.00
10529 376 200616 B 31-Aug-06 11 115 15 1,725.00
11572 376 200617 B 31-Aug-06 11 115 15 1,725.00
12221 376 200618 B 30-Sep-06 11 115 7 805.00
12222 376 200618 B 30-Sep-06 151 0 0 1,305.00
12223 376 200618 B 30-Sep-06 303 0 0 652.50

 

[SkipVought]

Is this the result you're looking for, (however truncated)

[b]
RCD_NUM	EMP_ID	PPERIOD 	PRUN	TDATE           	PC	RATE	HOURS	AMOUNT	JOB_ID[/b]
145.0	376.0	200609.0	B	2006-05-15 00:00:00	11.0	115.0	5.0	575.0	10.0
200.0	376.0	200610.0	B	2006-05-31 00:00:00	11.0	115.0	15.0	1725.0	10.0
813.0	376.0	200611.0	B	2006-06-15 00:00:00	11.0	115.0	15.0	1725.0	10.0
2110.0	376.0	200612.0	B	2006-06-30 00:00:00	11.0	115.0	13.0	1495.0	10.0
3957.0	376.0	200613.0	B	2006-07-15 00:00:00	11.0	115.0	14.5	1667.5	10.0
7457.0	376.0	200614.0	B	2006-07-31 00:00:00	11.0	115.0	16.0	1840.0	10.0
9224.0	376.0	200615.0	B	2006-08-15 00:00:00	11.0	115.0	15.0	1725.0	10.0
10529.0	376.0	200616.0	B	2006-08-31 00:00:00	11.0	115.0	15.0	1725.0	10.0
11572.0	376.0	200617.0	B	2006-08-31 00:00:00	11.0	115.0	15.0	1725.0	10.0
12221.0	376.0	200618.0	B	2006-09-30 00:00:00	11.0	115.0	7.0	805.0	10.0
12222.0	376.0	200618.0	B	2006-09-30 00:00:00	151.0	0.0	0.0	1305.0	10.0
12223.0	376.0	200618.0	B	2006-09-30 00:00:00	303.0	0.0	0.0	652.5	10.0

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

 

[SkipVought]

My Solution using Duane's as a guide. Maybe it can be simplified???

Select * 
From ( 
SELECT *  
FROM [TIME] t 
WHERE t.EMP_ID = 376 
  AND Year(t.TDATE) = 2006 
  AND [b]t.PC = 11[/b] 
) ua 
WHERE ua.RCD_NUM = ( 
SELECT Max(RCD_NUM) 
FROM (
SELECT *  
FROM [TIME] t 
WHERE t.EMP_ID = 376 
  AND Year(t.TDATE) = 2006 
  AND [b]t.PC = 11[/b] 
) uq 
WHERE ua.PPERIOD = uq.PPERIOD 
)
[b]UNION ALL[/b] 
Select * 
From ( 
SELECT *  
FROM [TIME] t 
WHERE t.EMP_ID = 376 
  AND Year(t.TDATE) = 2006 
  AND [b]t.PC = 151[/b] 
) ua 
WHERE ua.RCD_NUM = ( 
SELECT Max(RCD_NUM) 
FROM (
SELECT *  
FROM [TIME] t 
WHERE t.EMP_ID = 376 
  AND Year(t.TDATE) = 2006 
  AND [b]t.PC = 151[/b] 
) uq 
WHERE ua.PPERIOD = uq.PPERIOD 
)
[b]UNION ALL[/b] 
Select * 
From ( 
SELECT *  
FROM [TIME] t 
WHERE t.EMP_ID = 376 
  AND Year(t.TDATE) = 2006 
  AND [b]t.PC = 303[/b] 
) ua 
WHERE ua.RCD_NUM = ( 
SELECT Max(RCD_NUM) 
FROM (
SELECT *  
FROM [TIME] t 
WHERE t.EMP_ID = 376 
  AND Year(t.TDATE) = 2006 
  AND [b]t.PC = 303[/b] 
) uq 
WHERE ua.PPERIOD = uq.PPERIOD 
)

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

 

[JoPaBC]

Yes, your query gives exactly the same correct result as Duane's for specific criteria.
IMHO, his solution is more flexible since the PC value can different from this specific sample (1,2,3,10,11, ...) depending on type of pay rate (hourly regular time, hourly overtime, hourly double time, day rate, statutory holiday, salary, bonus, vacation pay, ...)
But thank you for your input anyway!

 

[dhookom]

[pre]
RCD_NUM EMP_ID PPERIOD PRUN TDATE PC RATE HOURS AMOUNT
145 376 200609 B 15-May-06 11 115 5 575.00
200 376 200610 B 31-May-06 11 115 15 1,725.00
813 376 200611 B 15-Jun-06 11 115 15 1,725.00
2110 376 200612 B 30-Jun-06 11 115 13 1,495.00
3957 376 200613 B 15-Jul-06 11 115 14.5 1,667.50
7457 376 200614 B 31-Jul-06 11 115 16 1,840.00
9224 376 200615 B 15-Aug-06 11 115 15 1,725.00
10529 376 200616 B 31-Aug-06 11 115 15 1,725.00
11572 376 200617 B 31-Aug-06 11 115 15 1,725.00
12221 376 200618 B 30-Sep-06 11 115 7 805.00
12222 376 200618 B 30-Sep-06 151 0 0 1,305.00
12223 376 200618 B 30-Sep-06 303 0 0 652.5
[/pre]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[JoPaBC]

Doing it finally correctly now?

[pre]
RCD_NUM EMP_ID DIV PPERIOD PRUN TDATE PC RATE HOURS AMOUNT
145 376 2 200609 B 15-May-06 11 115 5 575
200 376 2 200610 B 31-May-06 11 115 15 1725
813 376 2 200611 B 15-Jun-06 11 115 15 1725
2110 376 2 200612 B 30-Jun-06 11 115 13 1495
3957 376 2 200613 B 15-Jul-06 11 115 14.5 1667.5
7457 376 2 200614 B 31-Jul-06 11 115 16 1840
9224 376 2 200615 B 15-Aug-06 11 115 15 1725
10529 376 2 200616 B 31-Aug-06 11 115 15 1725
11572 376 2 200617 B 31-Aug-06 11 115 15 1725
12221 376 2 200618 B 30-Sep-06 11 115 7 805
12222 376 2 200618 B 30-Sep-06 151 0 0 1305
12223 376 2 200618 B 30-Sep-06 303 0 0 652.5
[/pre]

 

[dhookom]

Looks better although I added more spaces in front of PC to get it to align with the correct column of data. I also try to remember to bold the top line if they are column headings.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[JoPaBC]

OK, thanks, will do next time.