Stang70Fastback
Registered User.
- Local time
- Today, 10:05
- Joined
- Dec 24, 2012
- Messages
- 132
Hello all! I'm trying to design a query to perform a certain task, and I'm having trouble figuring out how to approach this (I'm not terribly savvy with Access.)
Here is a simple example:
Table 1: DATE | ROUTE | OTHER FIELDS...
Table 2: BULLETIN # | START DATE | END DATE | AFFECTED ROUTES | OTHER FIELDS...
Basically, Table 1 is a list of events that occurred which disrupted our route service. Table 2 is a list of existing bulletins for issues we knew about.
What I am trying to do:
In a nutshell, I would like to append the BULLETIN # field from records in Table 2, to any records in Table 1, based on whether or not the DATE in Table 1 falls between the START DATE and END DATE in Table 2 AND based on whether the ROUTE in Table 1 matches the AFFECTED ROUTE in Table 2.
In other words, for any given event in Table 1, is there a corresponding Bulletin that was in effect for THAT ROUTE, ON THAT GIVEN DAY.
How might I go about approaching designing a query for this? I've only even done simple matching, so trying to wrap my head around this makes my brain melt. Any help would be appreciated, and I can give more explanations about anything if it helps!
Here is a simple example:
Table 1: DATE | ROUTE | OTHER FIELDS...
Table 2: BULLETIN # | START DATE | END DATE | AFFECTED ROUTES | OTHER FIELDS...
Basically, Table 1 is a list of events that occurred which disrupted our route service. Table 2 is a list of existing bulletins for issues we knew about.
What I am trying to do:
In a nutshell, I would like to append the BULLETIN # field from records in Table 2, to any records in Table 1, based on whether or not the DATE in Table 1 falls between the START DATE and END DATE in Table 2 AND based on whether the ROUTE in Table 1 matches the AFFECTED ROUTE in Table 2.
In other words, for any given event in Table 1, is there a corresponding Bulletin that was in effect for THAT ROUTE, ON THAT GIVEN DAY.
How might I go about approaching designing a query for this? I've only even done simple matching, so trying to wrap my head around this makes my brain melt. Any help would be appreciated, and I can give more explanations about anything if it helps!