If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Design of event database
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! |
#2
|
|||
|
|||
Design of event database
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 somethingg! 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" wrote in message news 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! |
#3
|
|||
|
|||
Design of event database
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! "Jeff Boyce" wrote: 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 somethingg! 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" wrote in message news 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! |
#4
|
|||
|
|||
Design of event database
See comments in-line below...
"JCricket" wrote in message ... 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 |
#5
|
|||
|
|||
Design of event database
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" wrote: See comments in-line below... "JCricket" wrote in message ... 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 |
#6
|
|||
|
|||
Design of event database
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 "JCricket" wrote in message ... 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" wrote: See comments in-line below... "JCricket" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|