Design of event database

[JCricket]

Hello, I am looking for some advice on designing a database that deals with a
lot of event data that is automatically stored in tables. The data is
basically about how close different satellites get to each other. The key
fields in the tables are the date/time the analysis was run, the names of the
two satellites, the distance between them, and the date/time they will be
closest together.

My office sends out three types of notifications:
-an initial report the first time we find an event where the distance
between the satellites will be lower than a specified threshold.
-A follow-up report giving updated dated to an initial report
-A closure report if a later analysis shows the objects will no longer meet
the reporting threshold.

So it's easy enough to query the last records against whether they meet the
reporting distance threshold. What I am having difficult figuring out is how
to design the queries in order to determine whether or not it's an initial or
follow-up report. The analyses are run several times a day, so unfortunately
I can't do something like a find duplicates between yesterday and today.

In short, it needs to be able to compare the most recent record to the
second to most recent record for a particular event in order to tell if it's
new or if it's just updated distance data. I feel like I will end up
building queries on top of queries, but I'm not sure if that's the most
efficient way to do it.

Thanks in advance!

 

[Jeff Boyce]

It all starts with the data ... and I'm still having a bit of trouble
visualizing your data structure.

From your description, it sounds like you have:

tblCloseCall
CloseCallID
AnalysisDateTime
Satellite1Name
Satellite2Name
SeparationRightNow
DateOfClosestApproach

A couple things jump out at me. First, it would seem you'd have to repeat
[DateOfClosestApproach] every time you write a new record for the two
satellites. Then, using satellite "names" might not be the best idea... if
you keep a table of satellites, you could use the SatelliteID from that
table instead of the name.

One more thing... I don't know how many satellites you are doing this for,
but it seems possible that you would have a LOT of the above records to
create/store. After all, if you are only comparing S1 with S2, and if you
have, say, 100 satellites, you'd have ... hmmm, combination or permutation
.... hmmm, do you need to know S1 vs. S2 AND S2 vs S1, or are they the
"same"?

Hmmm, might be on the order of thousands of comparisons each "analysis". I
think I'm missing something<g>!

Finally, I don't see any field that indicates how CLOSE the closest approach
will be. I thought you wanted a way to identify those approaches that are
too close, so you can notify folks.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JCricket]

Hi Jeff, sorry that wasn't clear before - I was trying to keep the post short
so everyone wouldn't have to slug your way through, but I guess it's better I
explain everything.

Here are the fields in the table:
Run_Date_Time (the date/time the analysis program was run)
P_Name (Name of our satellite involved in the close approach)
S_Name (Name of satellite #2)
TCA_Date_Time (Date/Time when the two are forecasted to be closest together)
TCA_Range (Forecasted distance between the two at the TCA_Date_Time)

TCA_Date_Time, P_Name, and S_Name are what identifies a unique event... so
if a later record has the same values for those three, then we know it's an
update. Otherwise, it would be a new event.

All that matters is at the moment the two objects pass closest to each
other, what date/time will be at that moment (TCA_Date_Time), and how close
they will be (TCA_Range).

We are screening just the satellites we own, roughly about 2 dozen, which is
the P_Name. S_Name could be any of the umpteen thousands of objects floating
around in orbit. However, the data in the table will only contain pairings
where the TCA_Range is getting close to the reporting threshold. So for
example, if the threshold to send a report is if TCA_Range is within 1 km,
the table will only contain records for everything within 5km. So that way
it isn't overloaded with thousands of irrelevant records, but there would
still be records of events that were close. This will be key to determining
when and event is closed... if say yesterday's analysis had TCA_Range at
0.98km and today's analysis put it at 1.1km, we send a report telling folks
to disregard the event since it's no longer within the reporting threshold.

The main issue I'm trying to get my head around is how to get the database
to determine when a report is new or an update to a previous report, and then
determine if one from earlier is now closed. One complicating factor is that
when this report is run during the evening shift, I'd have to have it
consider Run_Date_Time values that took place prior to 1500, instead of just
having it compare today's to yesterdays... I'm not sure how to make criteria
in a query for date/time records that prior to 1500 today. But I'm thinking
I basically have to take the LastOf for Run_Date_Time and TCA_Range that have
a Run_Date_Time > 1500 today and compare then to the LastOf for Run_Date_Time
and TCA_Range with a Run_Date_Time < 1500 today.

When I tried to sketch it out on paper, I ended up making a union of five
different queries (one to get records of new events, two to determine
ongoing, two to determine closed, etc), which feels very inefficient. I was
also planning on having the queries create a new field called Report_Type
that will record whether it is an initial report, follow up, or closure. I
feel like I need a way to write a bunch of nested if-then statements in one
query instead of trying to execute a bunch of queries at once.

Hope this helps clarify what I'm trying to do... if not, please let me know.

Thanks!

It all starts with the data ... and I'm still having a bit of trouble
visualizing your data structure.

From your description, it sounds like you have:

tblCloseCall
CloseCallID
AnalysisDateTime
Satellite1Name
Satellite2Name
SeparationRightNow
DateOfClosestApproach

A couple things jump out at me. First, it would seem you'd have to repeat
[DateOfClosestApproach] every time you write a new record for the two
satellites. Then, using satellite "names" might not be the best idea... if
you keep a table of satellites, you could use the SatelliteID from that
table instead of the name.

One more thing... I don't know how many satellites you are doing this for,
but it seems possible that you would have a LOT of the above records to
create/store. After all, if you are only comparing S1 with S2, and if you
have, say, 100 satellites, you'd have ... hmmm, combination or permutation
.... hmmm, do you need to know S1 vs. S2 AND S2 vs S1, or are they the
"same"?

Hmmm, might be on the order of thousands of comparisons each "analysis". I
think I'm missing something<g>!

Finally, I don't see any field that indicates how CLOSE the closest approach
will be. I thought you wanted a way to identify those approaches that are
too close, so you can notify folks.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello, I am looking for some advice on designing a database that deals
with a
lot of event data that is automatically stored in tables. The data is
basically about how close different satellites get to each other. The key
fields in the tables are the date/time the analysis was run, the names of
the
two satellites, the distance between them, and the date/time they will be
closest together.

My office sends out three types of notifications:
-an initial report the first time we find an event where the distance
between the satellites will be lower than a specified threshold.
-A follow-up report giving updated dated to an initial report
-A closure report if a later analysis shows the objects will no longer
meet
the reporting threshold.

So it's easy enough to query the last records against whether they meet
the
reporting distance threshold. What I am having difficult figuring out is
how
to design the queries in order to determine whether or not it's an initial
or
follow-up report. The analyses are run several times a day, so
unfortunately
I can't do something like a find duplicates between yesterday and today.

In short, it needs to be able to compare the most recent record to the
second to most recent record for a particular event in order to tell if
it's
new or if it's just updated distance data. I feel like I will end up
building queries on top of queries, but I'm not sure if that's the most
efficient way to do it.

Thanks in advance!

 

[Jeff Boyce]

See comments in-line below...

Hi Jeff, sorry that wasn't clear before - I was trying to keep the post
short
so everyone wouldn't have to slug your way through, but I guess it's
better I
explain everything.

Here are the fields in the table:
Run_Date_Time (the date/time the analysis program was run)
P_Name (Name of our satellite involved in the close approach)
S_Name (Name of satellite #2)
TCA_Date_Time (Date/Time when the two are forecasted to be closest
together)
TCA_Range (Forecasted distance between the two at the TCA_Date_Time)

I still have a concern about using a "name" for the satellites... but this
is based on my interpretation of the word "name". If "name" to you means a
"100% guaranteed unique identifier", that should work.

I still have a concern about whether you have two records for every possible
pair of satellites. First, is "SatA" & "SatB" the same as "SatB" & "SatA".
Next, how many satellites are you recording for?

TCA_Date_Time, P_Name, and S_Name are what identifies a unique event... so
if a later record has the same values for those three, then we know it's
an
update. Otherwise, it would be a new event.

I don't understand the difference between a "later" record/update and a "new
event".

All that matters is at the moment the two objects pass closest to each
other, what date/time will be at that moment (TCA_Date_Time), and how
close
they will be (TCA_Range).

We are screening just the satellites we own, roughly about 2 dozen, which
is
the P_Name. S_Name could be any of the umpteen thousands of objects
floating
around in orbit. However, the data in the table will only contain
pairings
where the TCA_Range is getting close to the reporting threshold. So for
example, if the threshold to send a report is if TCA_Range is within 1 km,
the table will only contain records for everything within 5km. So that
way
it isn't overloaded with thousands of irrelevant records, but there would
still be records of events that were close. This will be key to
determining
when and event is closed... if say yesterday's analysis had TCA_Range at
0.98km and today's analysis put it at 1.1km, we send a report telling
folks
to disregard the event since it's no longer within the reporting
threshold.

OK, thanks for the clarifications. Now, how do YOU know that the 1.1km
distance means not to worry? Do YOU have to look back for any/all previous
records for that pair and see if the distance was less, or do you only care
about the immediately preceding record for that pair? (and can the pair be
SatA - SatB OR SatB - SatA, or will it ALWAYS be SatA - SatB?)

The main issue I'm trying to get my head around is how to get the database
to determine when a report is new or an update to a previous report, and
then
determine if one from earlier is now closed. One complicating factor is
that
when this report is run during the evening shift, I'd have to have it
consider Run_Date_Time values that took place prior to 1500, instead of
just
having it compare today's to yesterdays... I'm not sure how to make
criteria
in a query for date/time records that prior to 1500 today. But I'm
thinking
I basically have to take the LastOf for Run_Date_Time and TCA_Range that
have
a Run_Date_Time > 1500 today and compare then to the LastOf for
Run_Date_Time
and TCA_Range with a Run_Date_Time < 1500 today.

If the critical fact is that the pair is NOW or is NO LONGER within the
critical distance, what difference will it make whether the date/time is
still today or is yesterday or happened after 1500 or ...?

When I tried to sketch it out on paper, I ended up making a union of five
different queries (one to get records of new events, two to determine
ongoing, two to determine closed, etc), which feels very inefficient. I
was
also planning on having the queries create a new field called Report_Type
that will record whether it is an initial report, follow up, or closure.
I
feel like I need a way to write a bunch of nested if-then statements in
one
query instead of trying to execute a bunch of queries at once.

Hope this helps clarify what I'm trying to do... if not, please let me
know.

Thanks!

Generically, I suspect what you'll end up with is a "sub-select" query.
You'll use that to find any pairs for which the distance is less than the
minimum ... for the PRECEDING measurement/record. You do that by finding
the most recent, then finding the most recent NOT INCLUDING what you found
in the first part.

It seems like that would give you the most recent measurement on all pairs,
and those pairs for which the preceding measurement was under the minimum.
If the most recent measurement is under minimum, and if the preceding
measurement for that pair is under the minimum, how will you handle that
situation? If the most recent measurement is OVER minimum, and if the
preceding measurement is under minimum, that sounds like the situation you'd
use to call off the warning.

I believe you could use a single query, add field(s) as needed to determine
the above situations, then use a report based on that query to output your
"reports".

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JCricket]

Thanks for the quick reply - here's the answers to your questions:

The satellite "names" are actually more like serial numbers, so it is a 100%
unique identifier. Unfortunately, I have to be a bit vague due to the
sensitivity of details about our satellites. So here I'll just write
"OurSat1" or "OurSat2" to talk about our satellites, but in really they have
a much more specific identifier.

As far as Sat A/Sat B vs Sat B/Sat A, they are NOT the same difference. The
analysis only looks from a Sat A to Sat B perspective. Basically, what the
analysis program does is loads the data for our satellite into Sat A, and
then screens it against the catalog of other object out there. The data it
writes to the table are only those instances when another object is getting
close to Sat A. So all the data in the table is strictly going to be where
Sat A is one of our satellites. The fact that 1.0km is the threshold is
something specifically set in our procedures and does not change. Basically,
anything closer than 1.0km, report... anything 1.0 and greater, nothing to
worry about.

As far as new event vs ongoing, etc... maybe explaining how we handle the
reporting will help clarify.

Let's say I'm coming in on the swing shift. When the day shift ran the
analysis, it found two new close approaches that had not shown up before in
any previous analysis. So they send out two "initial" reports. When I do
the swing shift analysis, I find that one of those two close approaches will
now be outside the reporting range, the other close approach is still inside
the reportable range, and the I also find one new close approach. Thus, I
will send 3 reports - an "initial" report on the new close approach I found,
a "follow-up" report on the updated data for the close approach first
reported by the day shift, and a "closure" report for the close approach that
was reported by the day shift but is no longer within reporting range.

The data in the table would look something like this:

Run_Date_Time P_Name S_Name TCA_Date_Time TCA_Range
9/4/2008 5:40:50PM OurSat1 Obj2211 9/8/2008 2:44:23PM 0.78
9/4/2008 5:40:50PM OurSat1 Obj2456 9/7/2008 6:45:34PM 1.21
9/4/2008 5:40:50PM OurSat1 Obj2987 9/8/2008 9:12:32AM 0.55
9/4/2008 9:35:42AM OurSat1 Obj2456 9/7/2008 6:45:34PM 0.98
9/4/2008 9:35:42AM OurSat1 Obj2987 9/8/2008 9:12:32AM 0.35

So what I'm looking for the end report to do is spit out a list that looks
something like:

Initial Reports:
9/4/2008 5:40:50PM OurSat1 Obj2211 9/8/2008 2:44:23PM 0.78
Follow-Up Reports:
9/4/2008 5:40:50PM OurSat1 Obj2987 9/8/2008 9:12:32AM 0.55
Closure Reports:
9/4/2008 5:40:50PM OurSat1 Obj2456 9/7/2008 6:45:34PM 1.21

On my last post, the whole look before/after 1500 was my attempt to explain
that I need to be able to tell which records were from the analysis I just
did for my shift, and which ones came from the last shift. So I'm assuming
that has to do with using the Run_Date_Time, but I'm not 100% sure how to
make the query tell the difference between my runs and the last shifts.
However, it sound like the sub-select query would do that, though I haven't
used one before so I'm not sure.

 

[Jeff Boyce]

I'll be out of range for a bit. You might want to re-post your question and
include some of the clarifications you've added along this thread.

That way, more folks will have a chance to see it and respond.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP