Designing event database

Hello, I am trying to design a database that produces reports on an event
log. The table is automatically populated by a different program when I
perform
analysis runs on satellite collision avoidance - basically whether any of our
satellites are getting close to something else floating around out there and
we need to start thinking about moving out of the way. Here's the key fields:

Run_date_time: the date/time the analysis program was run
P_Name: the name of our satellite (names are unique)
S_Name: name of object our satellite is getting close to (names are unique)
TCA_Date_Time: The projected date/time when the objects will be closest
together
TCA_Range: the distance between the objects at TCA_Date_Time.

Now, I send out a report if the TCA_Range is under a certain value - for the
sake of this example, let's say that any time the TCA_Range is less than 1km,
I send a report. There are three different types of reports I send:
-initial: the first time a close approach
-follow-up: the close approach was reported by the last shift, and my
analysis runs will have updated TCA_Range data (P_Name, S_Name, TCA_Date_Time
will be the same)
-closu a close approach was reported by the last shift, but my current
analysis run shows the TCA_Range being greater than 1km now.

So the main issue I'm needing to tackle is to design this query so it can
determine not only which records meet the reporting threshold, but whether it
is an initial, follow-up, or closure report. Here's a quick example of what
might be in the table:

Run_Date_Time P_Name S_Name TCA_Date_Time
TCA_Range
9/08/2008 5:30:00PM OurSat1 Obj2132 9/12/2008 7:23:54AM 0.65
9/08/2008 5:30:00PM OurSat1 Obj3574 9/13/2008 8:47:14PM 1.05
9/08/2008 5:30:00PM OurSat1 Obj6854 9/14/2008 1:23:42AM 0.85
9/08/2008 8:30:00AM OurSat1 Obj3574 9/13/2008 8:47:14PM 0.98
9/08/2008 8:30:00AM OurSat1 Obj6854 9/14/2008 1:23:42AM 0.81

So in this case, the morning shift (Run Time of 0830) had two reported close
approaches. When I did the evening runs (5:30PM), I will need to send an
initial report for the Obj2132, since it's the first time it's shown up in
the analysis; a closure report for Obj3574, since it was reported last shift
but now the range is now greater than 1km; and an follow-up report for
Obj6854, since it was reported last shift but I have updated range info.

The end product I'm looking for is to run a report that will list the type
of reports I need and then display the appropriate records. So I was
planning on adding an additional field in the record to specify initial,
follow-up, and closure by which to sort in the report. I'm just not sure how
to set up the queries in order to determine which type of report is
appropriate.

Sorry for this being so long - just wanted to try and give you a clear
picture of what I need to do. Thanks in advance!

Add a Number field to your table. For each record that qualifies as
'reportable', assign a unique number to this field (leaving the
non-reportable records blank.) You can then locate the previous record for
the same pair of objects, and assign the same number to it, and the record
for the later report, and assign the same ReportableID to it again. This
field now identifies these 3 records as reporting on the same event.

Your reports then select the records where this ReportableID is not null.

I'm sure there's more to it than this quick summary, but hopefully that's
enough to help you identify the many (3) relating to the 1 reportable event.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JCricket" wrote in message
...
Hello, I am trying to design a database that produces reports on an event
log. The table is automatically populated by a different program when I
perform
analysis runs on satellite collision avoidance - basically whether any of
our
satellites are getting close to something else floating around out there
and
we need to start thinking about moving out of the way. Here's the key
fields:

Run_date_time: the date/time the analysis program was run
P_Name: the name of our satellite (names are unique)
S_Name: name of object our satellite is getting close to (names are
unique)
TCA_Date_Time: The projected date/time when the objects will be closest
together
TCA_Range: the distance between the objects at TCA_Date_Time.

Now, I send out a report if the TCA_Range is under a certain value - for
the
sake of this example, let's say that any time the TCA_Range is less than
1km,
I send a report. There are three different types of reports I send:
-initial: the first time a close approach
-follow-up: the close approach was reported by the last shift, and my
analysis runs will have updated TCA_Range data (P_Name, S_Name,
TCA_Date_Time
will be the same)
-closu a close approach was reported by the last shift, but my current
analysis run shows the TCA_Range being greater than 1km now.

So the main issue I'm needing to tackle is to design this query so it can
determine not only which records meet the reporting threshold, but whether
it
is an initial, follow-up, or closure report. Here's a quick example of
what
might be in the table:

Run_Date_Time P_Name S_Name TCA_Date_Time
TCA_Range
9/08/2008 5:30:00PM OurSat1 Obj2132 9/12/2008 7:23:54AM 0.65
9/08/2008 5:30:00PM OurSat1 Obj3574 9/13/2008 8:47:14PM 1.05
9/08/2008 5:30:00PM OurSat1 Obj6854 9/14/2008 1:23:42AM 0.85
9/08/2008 8:30:00AM OurSat1 Obj3574 9/13/2008 8:47:14PM 0.98
9/08/2008 8:30:00AM OurSat1 Obj6854 9/14/2008 1:23:42AM 0.81

So in this case, the morning shift (Run Time of 0830) had two reported
close
approaches. When I did the evening runs (5:30PM), I will need to send an
initial report for the Obj2132, since it's the first time it's shown up in
the analysis; a closure report for Obj3574, since it was reported last
shift
but now the range is now greater than 1km; and an follow-up report for
Obj6854, since it was reported last shift but I have updated range info.

The end product I'm looking for is to run a report that will list the type
of reports I need and then display the appropriate records. So I was
planning on adding an additional field in the record to specify initial,
follow-up, and closure by which to sort in the report. I'm just not sure
how
to set up the queries in order to determine which type of report is
appropriate.

Sorry for this being so long - just wanted to try and give you a clear
picture of what I need to do. Thanks in advance!