Re: A very Challenging Question?

Eskimo,

I may be thinking about this wrong and the only thing that this does not
cover is if they are in the same position at different times.

select distinct g.*
from GPSData AS g
where g.FixStatus = 'Good';

Distinct gets rid of all duplicate values, but once again we still have the
time problem...

HTH
Casey

Eskimo wrote:
Hi Office,

I am from Canada, near the hudson's bay coast.

Perhaps I am misunderstood, er...I missed the explanation of my dillema.

Here is an example of the table. (Named GPSData)

ID,TXDate,TXTime,PTT,FixNo,FixStatus,FixDate,FixTime,Longitude,Latitude
1,04/06/2006,13:03:21,18722,1,Good,04/06/2006,13:00:00,-94.1095,61.6494
2,04/06/2006,13:03:21,18722,2,Good,04/05/2006,13:00:00,-94.0999,61.6293
3,04/06/2006,13:03:21,18722,3,Good,04/04/2006,13:00:00,-94.0152,61.5956
4,04/06/2006,13:03:21,18722,4,Good,04/03/2006,13:01:00,-93.822,61.6361
5,04/06/2006,13:06:41,18722,1,Bad,04/06/2006,13:00:00,-94.1095,55.0958
6,04/06/2006,13:06:41,18722,2,Good,04/05/2006,13:00:00,-94.0999,55.0757
7,04/06/2006,13:06:41,18722,3,Good,04/04/2006,13:00:00,-94.0152,55.042
8,04/06/2006,13:06:41,18722,4,Bad,04/03/2006,13:01:00,-93.822,61.6361
9,04/06/2006,13:00:06,18722,1,Bad,04/05/2006,13:40:00,-146.5319,61.6293
10,04/06/2006,13:06:41,18722,1,Good,04/06/2006,13:00:00,-94.1095,61.6494
11,04/06/2006,13:06:41,18722,2,Good,04/05/2006,13:00:00,-94.0999,61.6293
12,04/06/2006,13:06:41,18722,3,Good,04/04/2006,13:00:00,-94.0152,61.5956
13,04/06/2006,13:06:41,18722,4,Good,04/03/2006,13:01:00,-93.822,61.6361
14,04/06/2006,14:43:21,18722,1,Good,04/06/2006,13:00:00,-94.1095,61.6494
15,04/06/2006,14:43:21,18722,2,Good,04/05/2006,13:00:00,-94.0999,61.6293
16,04/06/2006,14:43:21,18722,3,Good,04/04/2006,13:00:00,-94.0152,61.5956
17,04/06/2006,14:43:21,18722,4,Good,04/03/2006,13:01:00,-93.822,61.6361
20,04/06/2006,13:06:43,18968,1,Good,04/06/2006,13:00:00,-94.3077,61.2471
21,04/06/2006,13:06:43,18968,2,Good,04/05/2006,13:00:00,-94.317,61.1685
22,04/06/2006,13:06:43,18968,3,Good,04/04/2006,13:01:00,-94.3055,61.1489
23,04/06/2006,13:06:43,18968,4,Good,04/03/2006,13:01:00,-94.334,61.0713
24,04/06/2006,13:10:06,18968,1,Good,04/06/2006,13:00:00,-94.3077,61.2471
25,04/06/2006,13:10:06,18968,2,Good,04/05/2006,13:00:00,-94.317,61.1685
26,04/06/2006,13:10:06,18968,3,Good,04/04/2006,13:01:00,-94.3055,61.1489
27,04/06/2006,13:10:06,18968,4,Good,04/03/2006,13:01:00,-94.334,61.0713
28,04/06/2006,14:47:44,18968,1,Good,04/06/2006,13:00:00,-94.3077,61.2471
29,04/06/2006,14:47:44,18968,2,Good,04/05/2006,13:00:00,-94.317,61.1685
30,04/06/2006,14:47:44,18968,3,Good,04/04/2006,13:01:00,-94.3055,61.1489
31,04/06/2006,14:47:44,18968,4,Good,04/03/2006,13:01:00,-94.334,61.0713
32,04/06/2006,15:44:58,18968,1,Good,04/06/2006,13:00:00,-94.3077,61.2471
33,04/06/2006,15:44:58,18968,2,Good,04/05/2006,13:00:00,-94.317,61.1685
34,04/06/2006,15:44:58,18968,3,Good,04/04/2006,13:01:00,-94.3055,61.1489
35,04/06/2006,15:44:58,18968,4,Good,04/03/2006,13:01:00,-94.334,61.0713
36,04/06/2006,17:25:58,18968,1,Good,04/06/2006,13:00:00,-94.3077,61.2471
37,04/06/2006,17:25:58,18968,2,Good,04/05/2006,13:00:00,-94.317,61.1685
38,04/06/2006,17:25:58,18968,3,Good,04/04/2006,13:01:00,-94.3055,61.1489
39,04/06/2006,17:25:58,18968,4,Good,04/03/2006,13:01:00,-94.334,61.0713
40,04/06/2006,17:42:48,18968,1,Good,04/06/2006,13:00:00,-94.3077,61.2471
41,04/06/2006,17:42:48,18968,2,Good,04/05/2006,13:00:00,-94.317,61.1685
42,04/06/2006,17:42:48,18968,3,Good,04/04/2006,13:01:00,-94.3055,61.1489
43,04/06/2006,17:42:48,18968,4,Good,04/03/2006,13:01:00,-94.334,61.0713
44,04/06/2006,19:00:15,18968,1,Good,04/06/2006,13:00:00,-94.3077,61.2471
45,04/06/2006,19:00:15,18968,2,Good,04/05/2006,13:00:00,-94.317,61.1685
46,04/06/2006,19:00:15,18968,3,Good,04/04/2006,13:00:00,-94.3055,61.1489
47,04/06/2006,19:00:15,18968,4,Good,04/03/2006,13:00:00,-94.334,61.0713
48,04/06/2006,19:27:11,18968,1,Good,04/06/2006,13:00:00,-94.3077,61.2471
49,04/06/2006,19:27:11,18968,2,Good,04/05/2006,13:00:00,-94.317,61.1685
50,04/06/2006,19:27:11,18968,3,Good,04/04/2006,13:00:00,-94.3055,61.1489
51,04/06/2006,19:27:11,18968,4,Good,04/03/2006,13:00:00,-94.334,61.0713
52,04/06/2006,19:37:17,18968,1,Good,04/06/2006,13:00:00,-94.3077,61.2471
53,04/06/2006,19:37:17,18968,2,Good,04/05/2006,13:00:00,-94.317,61.1685
54,04/06/2006,19:37:17,18968,3,Good,04/04/2006,13:00:00,-94.3055,61.1489
55,04/06/2006,19:37:17,18968,4,Good,04/03/2006,13:00:00,-94.334,61.0713
56,04/06/2006,19:40:39,18968,1,Good,04/06/2006,13:00:00,-94.3077,61.2471
57,04/06/2006,19:40:39,18968,2,Good,04/05/2006,13:00:00,-94.317,61.1685
58,04/06/2006,19:40:39,18968,3,Good,04/04/2006,13:00:00,-94.3055,61.1489
59,04/06/2006,19:40:39,18968,4,Good,04/03/2006,13:00:00,-94.334,61.0713
60,04/11/2006,13:47:09,18968,1,Good,04/11/2006,13:00:00,-94.0009,61.3654
61,04/11/2006,13:47:09,18968,2,Good,04/10/2006,13:00:00,-93.9967,61.3641
62,04/11/2006,13:47:09,18968,3,Good,04/09/2006,13:00:00,-94.0491,61.4088
63,04/11/2006,13:47:09,18968,4,Good,04/08/2006,13:00:00,-94.1091,61.3708
64,04/11/2006,14:27:33,18968,1,Good,04/11/2006,13:00:00,-94.0009,61.3654
65,04/11/2006,14:27:33,18968,2,Good,04/10/2006,13:00:00,-93.9967,61.3641
66,04/11/2006,14:27:33,18968,3,Good,04/09/2006,13:00:00,-94.0491,61.4088
67,04/11/2006,14:27:33,18968,4,Good,04/08/2006,13:00:00,-94.1091,61.3708
68,04/11/2006,15:18:03,18968,1,Good,04/11/2006,13:00:00,-94.0009,61.3654
69,04/11/2006,15:18:03,18968,2,Good,04/10/2006,13:00:00,-93.9967,61.3641
70,04/11/2006,15:18:03,18968,3,Good,04/09/2006,13:00:00,-94.0491,61.4088
71,04/11/2006,15:18:03,18968,4,Good,04/08/2006,13:00:00,-94.1091,61.3708
72,04/11/2006,15:28:09,18968,1,Good,04/11/2006,13:00:00,-94.0009,61.3654
73,04/11/2006,15:28:09,18968,2,Good,04/10/2006,13:00:00,-93.9967,61.3641
74,04/11/2006,15:28:09,18968,3,Good,04/09/2006,13:00:00,-94.0491,61.4088
75,04/11/2006,15:28:09,18968,4,Good,04/08/2006,13:00:00,-94.1091,61.3708
76,04/11/2006,16:05:11,18968,1,Good,04/11/2006,13:00:00,-94.0009,61.3654
77,04/11/2006,16:05:11,18968,2,Good,04/10/2006,13:00:00,-93.9967,61.3641
78,04/11/2006,16:05:11,18968,3,Good,04/09/2006,13:00:00,-94.0491,61.4088
79,04/11/2006,16:05:11,18968,4,Bad,04/08/2006,13:00:00,-94.109,61.3708
80,04/11/2006,16:52:19,18968,1,Good,04/11/2006,13:00:00,-94.0009,61.3654
81,04/11/2006,16:52:19,18968,2,Good,04/10/2006,13:00:00,-93.9967,61.3641
82,04/11/2006,16:52:19,18968,3,Good,04/09/2006,13:00:00,-94.0491,61.4088
83,04/11/2006,16:52:19,18968,4,Good,04/08/2006,13:00:00,-94.1091,61.3708
84,04/11/2006,17:05:47,18968,1,Bad,04/27/2005,13:00:00,-94.0009,61.3815
85,04/11/2006,17:05:47,18968,2,Bad,04/26/2005,13:00:00,-89.6319,57.0124
86,04/11/2006,17:05:47,18968,3,Bad,04/25/2005,13:00:00,-98.6425,63.8162
87,04/11/2006,17:05:47,18968,4,Bad,04/24/2005,13:00:00,-87.7664,62.0426

As you can see, ID's one to four have four distinct PTT,FixDate,Fix Time.
However, there TXDates and TXTimes are the same. ID's 5 to 8 start with a
"Bad" for the fix status, so therefore, all the records 5-8 therefore have a
bad fixstatus, regardless of whether or not it says good. ID 9 has a bad and
is isolated from all others as the only one with it's own fixdate and fix
time, therefore that record can be deleted. ID's 10-13 have the same
PTT,FixDate,FixTime as ID's 1-4, but the data was transmitted at a later time
in the day. Since the ptt,fixDate,FixTime is the same, we can delete these as
they are duplicates, just transmitted later is all. Same thing applies to
ID's 14-17 as 10-13. ID's 18-87 follow the same rules as 1-18, just with a
different PTT Number.

So from that above, I would like the following....
ID,TXDate,TXTime,PTT,FixNo,FixStatus,FixDate,FixTime,Longitude,Latitude
1,04/06/2006,13:03:21,18722,1,Good,04/06/2006,13:00:00,-94.1095,61.6494
2,04/06/2006,13:03:21,18722,2,Good,04/05/2006,13:00:00,-94.0999,61.6293
3,04/06/2006,13:03:21,18722,3,Good,04/04/2006,13:00:00,-94.0152,61.5956
4,04/06/2006,13:03:21,18722,4,Good,04/03/2006,13:01:00,-93.822,61.6361
20,04/06/2006,13:06:43,18968,1,Good,04/06/2006,13:00:00,-94.3077,61.2471
21,04/06/2006,13:06:43,18968,2,Good,04/05/2006,13:00:00,-94.317,61.1685
22,04/06/2006,13:06:43,18968,3,Good,04/04/2006,13:01:00,-94.3055,61.1489
23,04/06/2006,13:06:43,18968,4,Good,04/03/2006,13:01:00,-94.334,61.0713
60,04/11/2006,13:47:09,18968,1,Good,04/11/2006,13:00:00,-94.0009,61.3654
61,04/11/2006,13:47:09,18968,2,Good,04/10/2006,13:00:00,-93.9967,61.3641
62,04/11/2006,13:47:09,18968,3,Good,04/09/2006,13:00:00,-94.0491,61.4088
63,04/11/2006,13:47:09,18968,4,Good,04/08/2006,13:00:00,-94.1091,61.3708

I hope that further explains the situation. Very complex for my simple,
arctic mind.

If anyone does have a solution, I'd be glad to hear from you.

Thanks, Eskimo

Hi, Eskimo (Do you really live in Alaska?),

[quoted text clipped - 47 lines]

Eskimo

--
Casey
College Student

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200605/1
.

Relevant Pages

  • Re: How to refresh Debian to install software set
    ... Fix one machine to function correctly, then duplicate the selections using the method described by "s. ... Fix one machine to function correctly, then use the df/netcat method to duplicate the hard disk partition. ... To UNSUBSCRIBE, email to debian-user-REQUEST@xxxxxxxxxxxxxxxx with a subject of "unsubscribe". ...
    (Debian-User)
  • Re: [PATCH] [-mm] FS: file name must be unique in the same dir in procfs
    ... Then I'd say fix the callers. ... duplicate names are totally unusable for user space. ... We should fix this kind of bugs ASAP if there are any. ... Bug report and a simple fix can be found here: ...
    (Linux-Kernel)
  • Re: 2.6.16-rc6-mm2
    ... architectures, which all duplicate this piece of code: ... and to do this fix on the asm-generic headers. ... I've never seen any evidence that the complex and bloated code generated by ...
    (Linux-Kernel)
  • Re: Derived types and allocatable
    ... It is probable that the fix went into an update to 10.1. ... IMPLICIT NONE ... TYPE:: duplicate ... So apparently the allocatable components have to be nested a couple of ...
    (comp.lang.fortran)