Re: A very Challenging Question?

Hi Casey,

When I pulled your sql into mine, it still gave me every record since the ID
is distinct for all records. So I did the same sql, but without the ID by
typing in each field name (ie. Select Distinct g.txdate, g.txtime, g.ptt and
so on. From GPSData as g where fixstatus = Good)

There were two problems with that query.

1.) it still gave me duplicate position info because the same position was
transmitted several times on the transmission day.
2. There are some fixstatus that says "good" when they are actually "Bad"
because when you have a FixNo 1 that has a FixStatus Bad, then the same block
of positions transmitted at the same date and time are all bad, regardless of
whether or not it says "good".

Hoping for more help, I remain.

Eskimo

"Casey via AccessMonster.com" wrote:

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

.