A very Challenging Question?

[Guest]

Hi All,

Satellite Collars (Named PTT on the table) on Caribou acquire a position
"fix" everyday at a certain time. It records the Postion with a Latitude
(Lat) and Longitude (Lon) as well as the day and time it acquired the
position (FixDay and
FixTime on the table) with one more field indicating the status of the fix
(FixStatus) as good or bad.

The position information is stored in memory until a transmission is made
into the satellite every 5 days. I hope you follow me so far.

Now, On these days when it transmit the data, it does so numerous times
throughout that day. So what we have is a group of position fixes sent every
few minutes.

What we end up with is numerous similiar position information records in one
table.

The challenge I am facing is that I am trying to come up with a query that
will delete all extra records that have the same position information, but
keep at least record with one position for each day it fixes.

Some notes to consider. There are some records that have a "bad" in the
FixStatus.
When this happens, there is usually a null value somewhere in that record.
So for each block of txdate and txtime that have a "Bad", all of that block
can be deleted.

I can explain more as we discuss this.

Anyone who can discuss this with me would be much appreciated.

Thanks and take care

Eskimo

 

[OfficeDev18 via AccessMonster.com]

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

I think I can give you one better. Keep the data to be transmitted in one
table, and the "good" fixStatus-es in another. Have the program transmit the
data from the first table, and APPEND the "good" table the first time
fixStatus is "good." At that point, delete the data to be transmitted from
the to-be-transmitted table. That way 1) you don't transmit redundant
information, and 2) you always capture a "good" record.

This assumes that you ALWAYS have a "good" response among the "bad" ones.

Hope this helps,

Sam

 

[Guest]

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

 

[Casey via AccessMonster.com]

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

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]

 

[Guest]

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

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

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

 

[Michel Walsh]

Hi,



Assuming that if, for a given (TXDate, TXTime), there is a FixStatus =
"Bad", then all records with the same (TXDate, TXTime) are to NOT be
considered,

Assuming that for a given (TXDate, TXTime) that is to be considered, you
still want ANY (Longitude, Latitude) among the records with the same
(TXDate, TXTime) ; as example, the first four records are all "good", no
other record is "bad" for (04/06/2006 13:03:21), but all four records have
different Longitude, Latitude, but we will keep just one record (but anyone)

then
============================

SELECT TXDate, TXTime, LAST(longitude), LAST(latitude)

FROM myTable AS a

WHERE "good"= ALL( SELECT b.FixStatus
FROM myTable As b
WHERE b.TXDate=a.TXDate
AND b.TXTime=a.TXTime)

GROUP BY TXDate, TXTime
============================


should do. The WHERE clause eliminates the records where something else that
"good" appear under FixStatus for any record sharing the same date and time,
while the GROUP BY allows to define with precision what is to be "distinct"
(DISTINCT assumes all the selected fields while GROUP BY allows to
differentiate which fields are relevant and which fields, like longitude and
latitude, are just "accessory").


Hoping it may help,
Vanderghast, Access MVP

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

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

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

 

[Guest]

Hi Michel,

The first assumption is close, but wrong on two things. Every collar has a
position fix everyday. It then stores them in the memory. On those days when
does transmit the positions, it does so numerous times. (So we got, 4-5
position information records, for every TXday/TXtime combination.) It should
be Assumed that if, for a given combination of (TXDate, TXTime, PTT), there
is a FixNo = 1 AND FixStatus = "Bad", then all records with the same (TXDate,
TXTime, PTT) are to NOT be considered, And Vice Versa. If it says good for
FixNo 1, then all are "good"

The second assumption is also not accurate. I do not want just any Lat, Lon
Values. I need the values from those records that were all "good" and
transmitted first.

I'll post on another reply where I am so far. Just discussing all of these
with you all is helping me think through it. Thank you all.

Eskimo

Hi,



Assuming that if, for a given (TXDate, TXTime), there is a FixStatus =
"Bad", then all records with the same (TXDate, TXTime) are to NOT be
considered,

Assuming that for a given (TXDate, TXTime) that is to be considered, you
still want ANY (Longitude, Latitude) among the records with the same
(TXDate, TXTime) ; as example, the first four records are all "good", no
other record is "bad" for (04/06/2006 13:03:21), but all four records have
different Longitude, Latitude, but we will keep just one record (but anyone)

then
============================

SELECT TXDate, TXTime, LAST(longitude), LAST(latitude)

FROM myTable AS a

WHERE "good"= ALL( SELECT b.FixStatus
FROM myTable As b
WHERE b.TXDate=a.TXDate
AND b.TXTime=a.TXTime)

GROUP BY TXDate, TXTime
============================


should do. The WHERE clause eliminates the records where something else that
"good" appear under FixStatus for any record sharing the same date and time,
while the GROUP BY allows to define with precision what is to be "distinct"
(DISTINCT assumes all the selected fields while GROUP BY allows to
differentiate which fields are relevant and which fields, like longitude and
latitude, are just "accessory").


Hoping it may help,
Vanderghast, Access MVP

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

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

 

[Guest]

Hi All,

I have come to the conclusion that there are too many variables in my table
that following any given set of rules does not apply. I will keep trying
though and appreciate any deep thoughts you may have.

Eskimo

Hi Michel,

The first assumption is close, but wrong on two things. Every collar has a
position fix everyday. It then stores them in the memory. On those days when
does transmit the positions, it does so numerous times. (So we got, 4-5
position information records, for every TXday/TXtime combination.) It should
be Assumed that if, for a given combination of (TXDate, TXTime, PTT), there
is a FixNo = 1 AND FixStatus = "Bad", then all records with the same (TXDate,
TXTime, PTT) are to NOT be considered, And Vice Versa. If it says good for
FixNo 1, then all are "good"

The second assumption is also not accurate. I do not want just any Lat, Lon
Values. I need the values from those records that were all "good" and
transmitted first.

I'll post on another reply where I am so far. Just discussing all of these
with you all is helping me think through it. Thank you all.

Eskimo

Hi,



Assuming that if, for a given (TXDate, TXTime), there is a FixStatus =
"Bad", then all records with the same (TXDate, TXTime) are to NOT be
considered,

Assuming that for a given (TXDate, TXTime) that is to be considered, you
still want ANY (Longitude, Latitude) among the records with the same
(TXDate, TXTime) ; as example, the first four records are all "good", no
other record is "bad" for (04/06/2006 13:03:21), but all four records have
different Longitude, Latitude, but we will keep just one record (but anyone)

then
============================

SELECT TXDate, TXTime, LAST(longitude), LAST(latitude)

FROM myTable AS a

WHERE "good"= ALL( SELECT b.FixStatus
FROM myTable As b
WHERE b.TXDate=a.TXDate
AND b.TXTime=a.TXTime)

GROUP BY TXDate, TXTime
============================


should do. The WHERE clause eliminates the records where something else that
"good" appear under FixStatus for any record sharing the same date and time,
while the GROUP BY allows to define with precision what is to be "distinct"
(DISTINCT assumes all the selected fields while GROUP BY allows to
differentiate which fields are relevant and which fields, like longitude and
latitude, are just "accessory").


Hoping it may help,
Vanderghast, Access MVP

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

:

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

 

[QuickHare]

I'm new to Access, so I may not be great at giving the device for doing this,
but I'd have thought a trigger might be useful. If the current record's status
is bad, then delete all records with the same criteria as needed (same long/lat
or same date/time), then cancel the insert. Just a thought to get some minds
working...

--

QuickHare

Hi All,

I have come to the conclusion that there are too many variables in my table
that following any given set of rules does not apply. I will keep trying
though and appreciate any deep thoughts you may have.

Eskimo

Hi Michel,

The first assumption is close, but wrong on two things. Every collar has a
position fix everyday. It then stores them in the memory. On those days when
does transmit the positions, it does so numerous times. (So we got, 4-5
position information records, for every TXday/TXtime combination.) It should
be Assumed that if, for a given combination of (TXDate, TXTime, PTT), there
is a FixNo = 1 AND FixStatus = "Bad", then all records with the same (TXDate,
TXTime, PTT) are to NOT be considered, And Vice Versa. If it says good for
FixNo 1, then all are "good"

The second assumption is also not accurate. I do not want just any Lat, Lon
Values. I need the values from those records that were all "good" and
transmitted first.

I'll post on another reply where I am so far. Just discussing all of these
with you all is helping me think through it. Thank you all.

Eskimo

Hi,



Assuming that if, for a given (TXDate, TXTime), there is a FixStatus =
"Bad", then all records with the same (TXDate, TXTime) are to NOT be
considered,

Assuming that for a given (TXDate, TXTime) that is to be considered, you
still want ANY (Longitude, Latitude) among the records with the same
(TXDate, TXTime) ; as example, the first four records are all "good", no
other record is "bad" for (04/06/2006 13:03:21), but all four records have
different Longitude, Latitude, but we will keep just one record (but
anyone)

then
============================

SELECT TXDate, TXTime, LAST(longitude), LAST(latitude)

FROM myTable AS a

WHERE "good"= ALL( SELECT b.FixStatus
FROM myTable As b
WHERE b.TXDate=a.TXDate
AND b.TXTime=a.TXTime)

GROUP BY TXDate, TXTime
============================


should do. The WHERE clause eliminates the records where something else
that
"good" appear under FixStatus for any record sharing the same date and
time,
while the GROUP BY allows to define with precision what is to be "distinct"
(DISTINCT assumes all the selected fields while GROUP BY allows to
differentiate which fields are relevant and which fields, like longitude
and
latitude, are just "accessory").


Hoping it may help,
Vanderghast, Access MVP


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

:

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

 

[BruceM]

I have been lurking in this thread since it started. It can be difficult to
evaluate a long and somewhat unwieldy set of data, so it often helps when
posting such questions to limit time to hours and minutes, and latitude and
longitude to zero or one decimal places, or things like that. Also, the
least number of records needed to represent the problem will mean people who
would like to try helping will spend less time trying to understand the
problem and more trying to devise a solution.
Perhaps a format like this (maybe without the additional spaces I added)
would be clearer. By the way, I did some copying after changing the format,
so 3 and 4 are the same as 1 and 2. I did that in a few places. My point
was to illustrate, not to transcribe.

ID,TXDate,TXTime,PTT,FixNo,FixStatus,FixDate,FixTime,Longitude,Latitude
1,04/06/2006,13:03,722,1,Good,04/06/2006,13:00,-94.1,61.6
2,04/06/2006,13:03,722,2,Good,04/05/2006,13:00,-94.0,61.6
3,04/06/2006,13:03,722,1,Good,04/06/2006,13:00,-94.1,61.6
4,04/06/2006,13:03,722,2,Good,04/05/2006,13:00,-94.0,61.6
5,04/06/2006,13:06,722,1,Bad,04/06/2006,13:00,-94.1,55.1
6,04/06/2006,13:06,722,1,Bad,04/06/2006,13:00,-94.1,55.1
7,04/06/2006,13:06,722,3,Good,04/04/2006,13:00,-94.0,55.0
8,04/06/2006,13:06,722,4,Bad,04/03/2006,13:01,-93.8,61.6
9,04/06/2006,13:00,722,1,Bad,04/05/2006,13:40,-146.5,61.6

You could also do something like:
ID...TXDate.....TXTime...PTT...FixNo...FixStatus...FixDate...FixTime...Long...Lat
1....04/06/06.....13:03......722.......1.........Good.....04/06/06....13:00....-94.1...61.6

In any case, now when you say "As you can see" it is possible to glance up
and see what is needed. The point is to provide the minimal amount of
sample data needed to illustrate the problem. It would also help to
describe how the data gets into the table? Is it manual data entry (in
which case you have some flexibility with the format), or is it imported
from another program (in which case you need to work with what it gives
you). Maybe you explained this already and I missed it.

I don't think the problem is unsolvable. You can describe the rules in
terms of objective criteria, so Access can probably be made to understand.
The trick is to make it as simple as possible for all of the people here who
love challenges to understand what you need to do.

Hi All,

I have come to the conclusion that there are too many variables in my
table
that following any given set of rules does not apply. I will keep trying
though and appreciate any deep thoughts you may have.

Eskimo

Hi Michel,

The first assumption is close, but wrong on two things. Every collar has
a
position fix everyday. It then stores them in the memory. On those days
when
does transmit the positions, it does so numerous times. (So we got, 4-5
position information records, for every TXday/TXtime combination.) It
should
be Assumed that if, for a given combination of (TXDate, TXTime, PTT),
there
is a FixNo = 1 AND FixStatus = "Bad", then all records with the same
(TXDate,
TXTime, PTT) are to NOT be considered, And Vice Versa. If it says good
for
FixNo 1, then all are "good"

The second assumption is also not accurate. I do not want just any Lat,
Lon
Values. I need the values from those records that were all "good" and
transmitted first.

I'll post on another reply where I am so far. Just discussing all of
these
with you all is helping me think through it. Thank you all.

Eskimo

Hi,



Assuming that if, for a given (TXDate, TXTime), there is a FixStatus =
"Bad", then all records with the same (TXDate, TXTime) are to NOT be
considered,

Assuming that for a given (TXDate, TXTime) that is to be considered,
you
still want ANY (Longitude, Latitude) among the records with the same
(TXDate, TXTime) ; as example, the first four records are all "good",
no
other record is "bad" for (04/06/2006 13:03:21), but all four records
have
different Longitude, Latitude, but we will keep just one record (but
anyone)

then
============================

SELECT TXDate, TXTime, LAST(longitude), LAST(latitude)

FROM myTable AS a

WHERE "good"= ALL( SELECT b.FixStatus
FROM myTable As b
WHERE b.TXDate=a.TXDate
AND b.TXTime=a.TXTime)

GROUP BY TXDate, TXTime
============================


should do. The WHERE clause eliminates the records where something else
that
"good" appear under FixStatus for any record sharing the same date and
time,
while the GROUP BY allows to define with precision what is to be
"distinct"
(DISTINCT assumes all the selected fields while GROUP BY allows to
differentiate which fields are relevant and which fields, like
longitude and
latitude, are just "accessory").


Hoping it may help,
Vanderghast, Access MVP


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

:

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

 

[Guest]

Hi Bruce,

Your points are well accepted. Although I have asked a lot of questions
lately on the newsgroups, I did not think about how to present my problem in
a more understandable manner. I will try in any future questions I may have.

Back to the problem at hand. I was able to do first find all of the records
with Fix Status = "Bad" and "Fixno" = 1. That way I would have a list of all
of the TXDate and TXTime combinations with the criteria. Here is the SQL for
that....

Query1
--------
Select GPSData.ID, GPSData.TXDate, GPSData.TXTime, GPSData.PTT,
GPSData.FixNo, GPSData.FixStatus, GPSData.FixDate, GPSData.FixTime,
GPSData.Longitude, GPSData.Latitude
FROM GPSData
Where (((GPSData.Fixno)=1 AND ((GPSData.FixStatus)="Bad"));

Then I did another query to find all of the Records in GPSData where it
matched Query1's Criteria. Like So.....

Query2
---------
Select GPSData.*
FROM GPSData INNERJOIN Query1 ON (GPSData.PTT = Query1.PTT) AND
GPSData.TXDate = Query1.TXDate) AND (GPSData.TXTime = Query1.TXTime)

Finally, I made a Delete query from GPSData of all the records shown in
Query 2 like this...

Query3
---------
DELETE DISTINCTROW GPSData.*
FROM GPSData INNERJOIN Query2 ON (GPSData.ID=Query2.ID)

These queries all found by searching in the newsgroup.

That got me to a point where I have at least all the possible valid records.
There is still many duplicate "Fix" information (Combination of PTT,
FixDate, FixTime, Latitude, and Longitiude) that have the same duplicate
values.

I now require to find the record with the distinct combination of "Fix"
Information. However, I don't want just any combination, I am looking for the
ones that have the distinct combination but that was "Transmitted" first.

Any help with that? I get stuck just thinking about it too much I think.

Eskimo

 

[BruceM]

I am not skilled at understanding SQL. I was interested in the question,
and since you have clearly put so much effort into it I wanted to see if it
could be brought to a satisfactory conclusion.

Let me see if I understand. There are four fixes. If any of the four are
bad (or if there are fewer than 4) then that reading is bad, and is not
used. Only if all four are good will the reading be used. Question: in the
case of four good readings, which one is used, or are all used, or what
exactly happens?

It would help to know how this data gets into your database. One of the
things that makes this more difficult is repeated data. 18722 is a
particular animal, as I understand. If so, a logical setup would be to have
an Animal table (or a Transmitter table), with a related table for readings,
and maybe a table related to that for the four fixes that are a part of each
reading.

I need to stress that I am following this thread because of what I hope to
learn. When it appeared you were thinking about throwing in the towel I
tried to suggest a better way of communicating the question.

 

[Guest]

Hi Bruce,

I am progressing further to a point where I think I got exactly what I want
out of the whole database. Let me explain after I respond to your post first.

For every collar(PTT), there are a vaying number of "Fixes" in a span of
several days. One fix for each day. After a collar has stored the fix
information in it's memory for the last few days, the transmitter will "wake
up". When it wakes up, it starts saying "okay, I am ready to transmit all of
these fixes, but I need to wait until there is a satellite overhead" When
the satellite is overhead, it starts transmitting the information. As it
does so, it does numerous times so that its sure that the data is transmitted
correctly. It may be behind a rock or side of a hill so it will transmit
several times when its due.

So there may be more than four days worth of fixes because when it is ready,
it will wait until a satellite is overhead. I've seen the collars record as
many as six positions. It varies from 1-6, but it's usally 4. Sometimes it
frequents 5 positions because it will also send the position it acquired on
the day it is transmitting. That is why there is so many duplicates in the
table for the fix information.

The data is sent coded in an email. We have a program that will decipher
that code and provide us with a *.csv file for each collar. I then run an
import routine in access to combine all of those collar's *.CSV's together. I
can create the logical setup with the tables and have considered that, but I
was unable to decide how the structured database would help me with the whole
filtration process.

Further steps after the first query.....
Query4
--------
SELECT DISTINCT First(GPSData.TXTime) AS FirstOfTXTime, GPSData.PTT,
GPSData.FixDate
FROM GPSData
Group By GOSData.PTT, GPSData.FixDate;

This above grouped (PTT and FixDate) then gave me the first TXTime for that
group,

Then it was time to include all of the Records from GPSData that had matched
the TXTime, TXDate and PTT as found in Query4....Like this....

Query5
--------
Select GPSData.*
From GPSData INNERJOIN Query4 ON (GPSData.TXTime = Query4.FirstOfTXTime) AND
(GPSData.FixDate = Query4.FixDate) AND GPSData.PTT = Query4.PTT)

That pretty well did the filtering and I can then work with Query5 data,
export it back out to CSV and analyse it for errors and enter any missing
data. (in the step where Query 2 was made and query3, it also deleted some
groups that had a "good" all around). But there is only something like 70
records over the course of the year where I need to add that.

From my original table, which had something like 70,000 records, I was able
to find the 4120 records I needed following the 5 queries above. Access is
pretty neat and I wish I had more formal training so that I can gain the full
use of it's potential.

Thank you to all

Eskimo

 

[BruceM]

Glad to hear it is working out. Access has filtering and sorting features
that can make it very useful for reporting and various types of number
crunching even with a flat (i.e. one-table) structure. A related table and
a subform can make it relatively simple to do such things as produce a
record count for a subset of data (e.g. all of the information for a single
collar), then sort or filter from there. But in the end it's about adapting
to your particular situation, such as imported data.
Good luck with the project.

 

[Michel Walsh]

Hi,

In query4, you should use MIN, not FIRST; FIRST means the first the engine
saw, not the EARLIEST, necessary:


SELECT DISTINCT MIN(GPSData.TXTime) AS FirstOfTXTime, GPSData.PTT,
GPSData.FixDate
FROM GPSData
Group By GOSData.PTT, GPSData.FixDate;


And if you ever work around midnight, I would strongly suggest to make a
SINGLE field of the DATE AND TIME.



You may also have problem if data is like this:

FixDate, FixTime
x+1, 13:00:00
x , 12:59:00
x-1 , 12:59:00
x-2, 13:00:00



Your query5 will pick 2 records,while you said you want just one. If you
pre-combine date and time in a single field, that would help greatly, since
the MIN would occur on the full date_time_stamp.

And no, keeping the date and time in 2 fields, don't use:

MIN(FixDate), MIN(FixTime) in query4 since, with our hypothetical data just
here up, that will pick x-2, 12:59:00; which is NOT a combination we find
in the 4 initial (hypothetical) values. So, definitively, think of using a
single date_time field.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Hi Michel,

Thanks for your suggestions. I will incorporate them into my database. It
makes sense to. (especially considering that our foundation for research is
"Quality Assurance-Quality Control").

For this table, it just so happened that using FIRST and MIN worked out to
be the same thing because the transmit time that is also the MIN for each
combination of PTT and FixDate. It was just sorted that way from the import
routine. Lucky I guess. I double checked the results of the Query5 with that
of the manually completed ones for comparison and they returned the same
records.

The timing of the Fixes are programmed for when we assume animals are
active, everyday at around 1:00 PM. Any fix times that do not fall in that
time range are usally Bad fixes the filtration method should delete them too.
But I agree that the Dates and Times should be combined into one field. We
will still get the groupings we want based on those new values. Good Idea!

I think I may have found a solution that took about a week to discover with
help from this newsgroup. Just discussing this was great help and Thanks for
all your suggestions and comments. What a great site!

Eskimo