×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Remove duplicate rows
2

Remove duplicate rows

Remove duplicate rows

(OP)
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?

RE: Remove duplicate rows

(OP)
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

RE: Remove duplicate rows

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


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 

I expect you want to return only the

CODE --> sql

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

RE: Remove duplicate rows

@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...

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Remove duplicate rows

(OP)
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

RE: Remove duplicate rows

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

RE: Remove duplicate rows

(OP)
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

RE: Remove duplicate rows

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Remove duplicate rows

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

RE: Remove duplicate rows

(OP)
I did preview, it looked like this:



My TGML is checked, am I doing anything wrong?

RE: Remove duplicate rows

(OP)
"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 sad

RE: Remove duplicate rows

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?

CODE --> SQL

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

RE: Remove duplicate rows

(OP)
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

RE: Remove duplicate rows

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

CODE


RCD_NUM	EMP_ID	PPERIOD 	PRUN	TDATE           	PC	RATE	HOURS	AMOUNT	JOB_ID
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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Remove duplicate rows

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

CODE

Select * 
From ( 
SELECT *  
FROM [TIME] t 
WHERE t.EMP_ID = 376 
  AND Year(t.TDATE) = 2006 
  AND t.PC = 11 
) 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 t.PC = 11 
) uq 
WHERE ua.PPERIOD = uq.PPERIOD 
)
UNION ALL 
Select * 
From ( 
SELECT *  
FROM [TIME] t 
WHERE t.EMP_ID = 376 
  AND Year(t.TDATE) = 2006 
  AND t.PC = 151 
) 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 t.PC = 151 
) uq 
WHERE ua.PPERIOD = uq.PPERIOD 
)
UNION ALL 
Select * 
From ( 
SELECT *  
FROM [TIME] t 
WHERE t.EMP_ID = 376 
  AND Year(t.TDATE) = 2006 
  AND t.PC = 303 
) 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 t.PC = 303 
) uq 
WHERE ua.PPERIOD = uq.PPERIOD 
) 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Remove duplicate rows

(OP)
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!

RE: Remove duplicate rows


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
 

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

RE: Remove duplicate rows

(OP)


Doing it finally correctly now?



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
 

RE: Remove duplicate rows

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

RE: Remove duplicate rows

(OP)
OK, thanks, will do next time.

RE: Remove duplicate rows

(OP)
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...

RE: Remove duplicate rows

I was trying to answer this additional requirement…

Quote:

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Remove duplicate rows

(OP)
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 mad

RE: Remove duplicate rows

Quote (Jozef)

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...

Quote (Jozef)

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

That’s why I subsequently stated:

Quote (Skip)


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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Remove duplicate rows

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

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close