Sub-select queries

[JCricket]

Hello, I am trying to design a database that produces reports on an event
log, and was told that sub-select queries might be what I need to do, though
I haven't used them before so I'm not sure how to set them up right. Here is
what I'm trying to do.

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)
-closure: 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!

 

[Lord Kelvan]

ok here it is nice and big and tedious basically it will add a label
of initial closure follow-up close call or a blank depending on the
conditions you provided above

i have based the field names on the fields provided above but i names
the table tblTCA because i didnt know what you used

so basically whereever you see tblTCA replace it with your table name

SELECT tblTCA.Run_Date_Time, tblTCA.P_Name, tblTCA.S_Name,
tblTCA.TCA_Date_Time, tblTCA.TCA_Range, IIf([TCA_Range]<1 And [s_name]
Not In (select subtca.s_name from tblTCA as subTCA Where
format(subtca.run_date_time,"dd/mm/yyyy") =
format(tbltca.run_date_time,"dd/mm/yyyy") and Format(subtca.
[run_date_time],"AM/PM")="AM"),"Initial",IIf([TCA_Range]<1 And
Format([run_date_time],"AM/PM")="AM","close call",IIf([TCA_Range]>1
And [s_name] In (select subtca.s_name from tblTCA as subTCA Where
format(subtca.run_date_time,"dd/mm/yyyy") =
format(tbltca.run_date_time,"dd/mm/yyyy") and Format(subtca.
[run_date_time],"AM/pm")="AM") And Format([run_date_time],"AM/
PM")="PM","closure",IIf([TCA_Range]<1 And [s_name] In (select
subtca.s_name from tblTCA as subTCA Where
format(subtca.run_date_time,"dd/mm/yyyy") =
format(tbltca.run_date_time,"dd/mm/yyyy") and Format(subtca.
[run_date_time],"AM/pm")="AM") And Format([run_date_time],"AM/
PM")="PM","Follow-up",Null)))) AS Report_Type
FROM tblTCA;


My results were
Run_Date_Time P_Name S_Name TCA_Date_Time TCA_Range Report_Type
09/08/2008 05:30:00pm OurSat1 Obj2132 9/12/2008 07:23:54 0.65 Initial
09/08/2008 05:30:00pm OurSat1 Obj3574 13/09/2008 20:47:14 1.05 closure
09/08/2008 05:30:00pm OurSat1 Obj6854 14/09/2008 01:23:42 0.85 Follow-
up
09/08/2008 08:30:00am OurSat1 Obj3574 13/09/2008 20:47:14 0.98 close
call
09/08/2008 08:30:00am OurSat1 Obj6854 14/09/2008 01:23:42 0.81 close
call

do note that my date time format is dd/mm/yyyy and 24 hour time not mm/
dd/yyyy 12 hour time i forced the rundatetime field to display 12 hour
time though

AS AN IMPORTANT NOTE this query will only work if you run the two
tests 1 in the morning and 1 in the afternoon because of the am/pm
thing

hope this helps

Regards
Kelvan

 

[JCricket]

Wow, thanks so much for your time and effort. That's quite the block o'
code! I'll get the names tweaked and put it in my database and let you know
how it turns out.

 

[Lord Kelvan]

yea well what you want isnt simple basically using that query you can
see the types of reports you have to do and if you haev to run it
through the access reports thing just filter it by the text stored in
report_type in that query

hope this helps

Regards
Kelvan