Trying to wrap my head around a query design.

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!
 
So, you won't really need to append the Bulletin# from Table 1 to Table 2, since they are already stored in Table 1. That would just be duplicating data. What you can do then is write a query that joins those two tables, so you have access to all the fields in Table 1 along with the Bulletin# from Table 2.

The query would join ROUTE from Table 1 to AFFECTED ROUTE in Table 2. This will ensure only results where these two fields match will be returned. For the Date portion, you would then use something similar to the following SQL for the Where clause:

Code:
Where [Table 1].Date between [Table 2].[START DATE] and [Table 2].[END DATE]

You might want to change DATE to something else since it's a reserved word in Access and can cause issues down the road.
 
Ok. That definitely helps to point me in the right direction. I'm sure I'll be back with more questions about this, but this is a good start. Thanks for the quick reply!

And don't worry, the column headers above were simplified for the explanation. I've already learned my lesson about using reserved names :P
 
No problem, good luck. We're always here if you have more questions.
 
Actually, I do have one more question.

The ROUTE field in Table 1 will always be one number ("120", for example) whereas the AFFECTED ROUTES field could be just "120", or it could be "32, 72, 120, 128". How can I configure the query to match those two fields and find the route in the list?
 
^ That or separate your AFFECTED ROUTES into their own columns. I think that might be a better long term solution as opposed to storing multiple values in one field.
 
For the record I got distracted and missed all the replies, so sorry for jumping in. My solution would address the dates issue. I would not store multiple values in one field, nor would I use columns. I believe a related table with a record for each route would be the normalized approach.
 
Alright guys, I appreciate the help. I have separated things so that there aren't multiple values in one field and everything is working splendidly! However, I have one last foible to work out:

If a route event matches more than one bulletin, then the query generates two records for that event, one for each attached bulletin. Because this query is going to be used to generate a report, is it possible to have the query append both of those bulletin numbers either to two fields in the same record, or even just list them in one field? I realize I'm now going the other direction and trying to combine information into one field, but the goal of this report is to look at our various events, and then have a field that says "these bulletins might be the reason for this having occurred." I don't want to have the same event show up twice just to signify that there are TWO bulletins that might have affected it.

Alternatively, I suppose I can run another query that combines the two rows somehow and dumps both bulletins into one field... hmmm...
 
Ok. I've got this almost working, but I still have one problem. To start with, here is the code I am using:

Code:
SELECT SrvcRest.*, ReRoutes.[Bulletin #], ConcatRelated("[Bulletin #]", "SrvcRestandBulletins", "SrvcRest.ID = " & SrvcRest.[ID])
FROM SrvcRest LEFT JOIN ReRoutes ON (SrvcRest.EventDate >= ReRoutes.[Start Date] AND SrvcRest.EventDate <= ReRoutes.[End Date]) AND (SrvcRest.Route=ReRoutes.Route1 OR SrvcRest.Route=ReRoutes.Route2 OR SrvcRest.Route=ReRoutes.Route3 OR SrvcRest.Route=ReRoutes.Route4 OR SrvcRest.Route=ReRoutes.Route5 OR SrvcRest.Route=ReRoutes.Route6 OR SrvcRest.Route=ReRoutes.Route7 OR SrvcRest.Route=ReRoutes.Route8 OR SrvcRest.Route=ReRoutes.Route9 OR SrvcRest.Route=ReRoutes.Route10);

As you can see, I have "injected" the ConcatRelated function into my Query. It has kinda-sorta solved my problem... ish. Here is an example of what I see now:

|---A---|---B---|---C---|---D---|---E---|
|---1---|---X---|---Q---|--101--|--101--|
|---2---|---Y---|---E---|--102--|102,103|
|---2---|---Y---|---E---|--103--|102,103|
|---3---|---Z---|---R---|--104--|104,105|
|---3---|---Z---|---R---|--105--|104,105|

The ConcatRelated function has added a new column (E) to my query. As you can see above, there are THREE records. Record 2 and 3 each have two associated bulletins listed in column D, and so each of them has become two records. Column E shows what I WANT the results to look like, however the code, as it stands above, still leaves me with what you see here. Duplicates, to which the ConcatRelated values have been attached. So I'm basically almost there. I could always run a second query and just have it delete column D and also delete duplicate entries, but I'd rather fix my problem rather than add more computation to the task for no reason.
 
Don't know the field names, but following your example try

SELECT A, B, C, ConcatRelated("[Bulletin #]", "SrvcRestandBulletins", "SrvcRest.ID = " & SrvcRest.[ID])
FROM ...
GROUP BY A, B, C
 
I had actually already tried that. I had added:

GROUP BY SrvcRest.ID

but that throws up the following error message:

"Cannot group on fields selected with '*' (SrvcRest)."
 
I should add that there is something that seems awfully shady about the way this is running. It really slows things down. The two tables only have 50 records each, and yet this concatenation function makes the query and report go from running instantly, to taking 2-3 seconds to refresh every time I do any sort of scrolling of the query/report.
 
I meant to take out the * and replace with the first 3 fields. The function is certainly a factor in the speed, but it may also be a function of the joins. With that few records I wouldn't expect the function to slow it down that much.
 

Users who are viewing this thread

Back
Top Bottom