Not to show Repeating same values

[Waqas ali]

Dear Experts,

in below table i have startedjob and endjob columns in these columns repeating values are existing.

i want to show in startedjob first value and in endjob is last value from these repeating values and don't want to show other repeating values.

i want to calculate in new columns of these startedjob and endjob time.

can any one assist me how can i do this?

Conn_Id	sender	Desk	AddJobId	startedJob	Conn_Time	DisConn	endJob	ConnSecond
20160301001122175194	A-B02	50001	1024	01-Mar-16 00:10:13	01-Mar-16 00:11:42	01-Mar-16 00:11:45	01-Mar-16 00:13:36	03
20160301002156079926	A-B02	50016	1025	01-Mar-16 00:21:37	01-Mar-16 00:22:16	01-Mar-16 00:22:21	01-Mar-16 00:24:58	05
20160301002221579807	A-B02	50016	1025	01-Mar-16 00:21:37	01-Mar-16 00:22:41	01-Mar-16 00:22:48	01-Mar-16 00:24:58	07
20160301002248377012	A-B02	50015	1025	01-Mar-16 00:21:37	01-Mar-16 00:23:08	01-Mar-16 00:23:31	01-Mar-16 00:24:58	23
20160301002916558650	A-B02	50012	1026	01-Mar-16 00:28:36	01-Mar-16 00:29:36	01-Mar-16 00:30:00	01-Mar-16 00:31:44	24
20160301005953042634	A-B02	50001	1027	01-Mar-16 00:57:46	01-Mar-16 01:00:12	01-Mar-16 01:00:20	01-Mar-16 01:04:48	08
20160301010201472532	A-B02	50015	1027	01-Mar-16 00:57:46	01-Mar-16 01:02:21	01-Mar-16 01:04:48	01-Mar-16 01:04:48	27
20160301010201472532	A-B02	50015	1028	01-Mar-16 01:04:49	01-Mar-16 01:04:49	01-Mar-16 01:04:50	01-Mar-16 01:06:21	01
20160301010944024953	A-B02	50016	1030	01-Mar-16 01:06:53	01-Mar-16 01:10:03	01-Mar-16 01:10:16	01-Mar-16 01:15:14	13
20160301011208011020	A-B02	50012	1030	01-Mar-16 01:06:53	01-Mar-16 01:12:27	01-Mar-16 01:14:23	01-Mar-16 01:15:14	56
20160301011712430296	A-B02	50015	1031	01-Mar-16 01:15:16	01-Mar-16 01:17:32	01-Mar-16 01:18:31	01-Mar-16 01:18:31	59
20160301012803133844	A-B02	50015	1032	01-Mar-16 01:27:13	01-Mar-16 01:28:23	01-Mar-16 01:28:53	01-Mar-16 01:30:20	30
20160301013347117971	A-B02	50001	1033	01-Mar-16 01:32:33	01-Mar-16 01:34:07	01-Mar-16 01:35:28	01-Mar-16 01:35:28	21
20160301013347117971	A-B02	50001	1034	01-Mar-16 01:35:31	01-Mar-16 01:35:31	01-Mar-16 01:35:31	01-Mar-16 01:39:57	00
20160301013706378677	A-B02	50016	1034	01-Mar-16 01:35:31	01-Mar-16 01:37:26	01-Mar-16 01:39:57	01-Mar-16 01:39:57	31

<tbody>
</tbody>

 

[xenou]

If you remove the fields that are not repeating you can use DISTINCT:

SELECT DISTINCT AddJobId, startedJob, endJob FROM Table1

 

[XLearner]

Forget about it. I though this was an Excel question...

Hi
Assming AddJobId is column D, startedJob is column E, endJob is column H and actual data are in rows 2 to 16.
Create 3 columns (K, L and M) with Title UAddJobId, MinStartedJob, MaxEndJob. In the 3 formulas replace ; with , if this is your usual separator

In K2:
=IF(ROW(1:1)>SUMPRODUCT( ($D$2:$D$16 <> "") / COUNTIF($D$2:$D$16; $D$2:$D$16&"") );""; INDEX($D$1:$D$16;SMALL(IF(FREQUENCY($D$2:$D$16;$D$2:$D$16);ROW($D$2:$D$16)); ROW(1:1));1))
Validate with [Ctrl]+[Shift]+[Enter] as this is an array formula. Then copy it down until row 16 with this example

In L2:
=IF(ROW(1:1)>SUMPRODUCT( ($D$2:$D$16 <> "") / COUNTIF($D$2:$D$16; $D$2:$D$16&"") );""; MIN( IF($D$2:$D$16=$K2; $E$2:$E$16)))
Validate with [Ctrl]+[Shift]+[Enter] as this is an array formula. Then copy it down until row 16 with this example

In M2:
=IF(ROW(1:1)>SUMPRODUCT( ($D$2:$D$16 <> "") / COUNTIF($D$2:$D$16; $D$2:$D$16&"") );""; MAX( IF($D$2:$D$16=$K2; $H$2:$H$16)))
Validate with [Ctrl]+[Shift]+[Enter] as this is an array formula. Then copy it down until row 16 with this example

1st formula brings you a list of Unique AddJobId. 2nd formula brings you the earliest date/time by JobId. 3rd formula brings you the latest date/time by JobId

Regards
XLearner

 

[Waqas ali]

thanks for both,

it is not excel file this data in access table and distinct will not work because jobstarted time and endtime is unique but Conn_Time and DisConn can be more than one in between jobstarted time and end time for the dame jobId.

i want to show for each jobid jobstarted time and endtime only once but if conn_time and disconn is exist more than one also show all.

this how can i do it because i have to calculate jobstarted and endtime against each job and in between how many times machine is connected and disconnected with particular job also to be calculate.

 

[xenou]

You can do it only separately, not together, because this implies different types of grouping. Possibly it can be done in an access Report.