Align Records from Two Tables in One Report

[pepandmax]

Hi,

I am working on a database to hold schedules for a public transit
agency. I have one table called "Trips" with fields such as "Route#,"
"Trip#," "Stop01Time," "Stop02Time," etc. Another table
("StopsPerRoute") has the stop names for each route.

I have set up a report to show the schedule for one route, with trip
numbers in one column on the left and stop times in columns on the
right. I used DLookup to get the stop names for the route in
question, and put them in the page header. Looks great!

Now, we break our routes up into inbound and outbound segment. Thus,
Route 35 is actually two routes: 3500 (outbound) and 3510 (inbound).
The next logical step is to set up a report that shows both the Rt.
3500 and 3510 schedule on one page, with "trip 1" of the Rt. 3500
lined up with "trip 1" of the Rt. 3510.

I have tried setting up the report in a similar manner to the "single
route only" report by setting up a query to call for the first route
and another query to call for the second route, but this resulted in
all of the trips of the Rt. 3500 grouped together, and all of the
trips of the Rt. 3510 grouped under that. Then, I tried to group
based on trip, but since I'm calling trip numbers from two separate
queries, I got the same results. What do you think is the best way to
approach this task?

In addition, since the user will really be looking for "Rt. 35," not
"Rts. 3500 and 3510," is there any way to set up one query based on
the first two digits of the route number? I realize that this group
is for Access Reports, so if no one can help on this one, I'll ask
elsewhere.

Thank you in advance for any help that you might be able to provide!
Looking forward to your responses.

Regards,
Nicole

 

[krissco]

Hi,

I am working on a database to hold schedules for a public transit
agency. I have one table called "Trips" with fields such as "Route#,"
"Trip#," "Stop01Time," "Stop02Time," etc. Another table
("StopsPerRoute") has the stop names for each route.

I have set up a report to show the schedule for one route, with trip
numbers in one column on the left and stop times in columns on the
right. I used DLookup to get the stop names for the route in
question, and put them in the page header. Looks great!

Now, we break our routes up into inbound and outbound segment. Thus,
Route 35 is actually two routes: 3500 (outbound) and 3510 (inbound).
The next logical step is to set up a report that shows both the Rt.
3500 and 3510 schedule on one page, with "trip 1" of the Rt. 3500
lined up with "trip 1" of the Rt. 3510.

I have tried setting up the report in a similar manner to the "single
route only" report by setting up a query to call for the first route
and another query to call for the second route, but this resulted in
all of the trips of the Rt. 3500 grouped together, and all of the
trips of the Rt. 3510 grouped under that. Then, I tried to group
based on trip, but since I'm calling trip numbers from two separate
queries, I got the same results. What do you think is the best way to
approach this task?

In addition, since the user will really be looking for "Rt. 35," not
"Rts. 3500 and 3510," is there any way to set up one query based on
the first two digits of the route number? I realize that this group
is for Access Reports, so if no one can help on this one, I'll ask
elsewhere.

Thank you in advance for any help that you might be able to provide!
Looking forward to your responses.

Regards,
Nicole

Nicole,

You will continue to have maintenance issues with this database. Every
time business needs change - and they have a way of changing more than
we sometimes want - you will have to work very hard at making the
database accommodate the change. Why such a gloomy outlook? Your
database is not normalized. Yes, I know, these things have a learning
curve. Additionally, there is not always an "immediate benefit" from
doing things the "right way" - but it will pay off when things change.

Ok. Now that I've fulfilled my rant quota, back to your question. Do I
understand you correctly that your data looks something like this?

Route#, Trip#, Stop01Time, Stop02Time, etc. . .
3500 1 8AM 8:15AM
3510 1 4PM 4:30PM

Do you want to display the data in the same way? (Is this what you
mean by "lined up"?)
If that is the case, you can accomplish this using "sorting and
grouping."

1. Group and sort on Left([Route#], 2)
2. Sort by [Trip#]
3. Sort by Right([Route#], 2)


As far as the user being able to specify "35" instead of "3500 and
3510", include the following in your query:

.. . .
Where left([Route#], 2) = [Enter two digit route number]
.. . .


I may have misunderstood what you are asking for with your report. If
so, please post back.

-Kris

 

[pepandmax]

Kris:

Thank you for your response.

You will continue to have maintenance issues with this database. Every
time business needs change - and they have a way of changing more than
we sometimes want - you will have to work very hard at making the
database accommodate the change. Why such a gloomy outlook? Your
database is not normalized. Yes, I know, these things have a learning
curve. Additionally, there is not always an "immediate benefit" from
doing things the "right way" - but it will pay off when things change.

I'm sorry, but I have no idea where this paragraph came from. What do
you mean about "business needs change"? Why do you suspect that I'm
going for an "immediate benefit" and not interested in doing things
the "right way"?

We have other systems that depend on dividing up the routes into
inbound and outbound, so if your suggestion of normalization referred
to combining route segments into one route (and hence one record in
the trips table), that's pretty much out of the question.

Ok. Now that I've fulfilled my rant quota, back to your question. Do I
understand you correctly that your data looks something like this?

Route#, Trip#, Stop01Time, Stop02Time, etc. . .
3500 1 8AM 8:15AM
3510 1 4PM 4:30PM

Sort of. Except it's more like this:

Route#, Trip#, Stop01Time, Stop02Time, StopTime03Time
3500 1 8:00 8:15 8:20
3510 1 8:25 8:30 8:35

Do you want to display the data in the same way? (Is this what you
mean by "lined up"?)

Not quite. I want a report that shows Rt. 3500 on the left, and then
Rt. 3510 on the right, like this:

Trip#, Stop01, Stop02, Stop03, Stop01, Stop02, Stop03
1 8:00 8:15 8:20 8:25 8:30 8:35

The records are grouped based on trip number. However, sometimes the
inbound trip 1 (ending in 10) happens before the outbound trip 2
(ending in 00), so I would then need to sort columns 2 and 5 based on
time. Does that make sense?

As far as the user being able to specify "35" instead of "3500 and
3510", include the following in your query:

. . .
Where left([Route#], 2) = [Enter two digit route number]
. . .

Perfect! That was exactly what I was looking for.

Thank you again for your response, and I hope I have clarified the
situation a little.

Nicole

 

[pepandmax]

Also RE: normalization, the "trips" table is created elsewhere, so I'm
sort of stuck with that design...

 

[krissco]

I'm sorry, but I have no idea where this paragraph came from. What do

you mean about "business needs change"? Why do you suspect that I'm
going for an "immediate benefit" and not interested in doing things
the "right way"?

We have other systems that depend on dividing up the routes into
inbound and outbound, so if your suggestion of normalization referred
to combining route segments into one route (and hence one record in
the trips table), that's pretty much out of the question.

This table structure would lead to less problems, and easier
maintenance:

Bus: {BusNumber}
RouteCode: {RouteCodeNumber, RouteCodeDescription}
Route: {BusNumber, RouteCodeNumber}
Trip: {TripNumber, BusNumber, RouteCodeNumber, StopsInTrip}
Stop: {BusNumber, RouteCodeNumber, TripNumber, StopNumber, StopTime} -
optionally "StopName" too

Data would look like:
Bus: (35)
Route: (35, 00), (35, 10)
RouteCode: (00, Outbound), (01, Inbound)
Trip: (1, 35, 00, 3), (1, 35, 10, 3)
Stop: (35, 00, 1, 1, 8:00), (35, 00, 1, 2, 8:15), (35, 00, 1, 3,
8:25), (35, 10, 1, 1, 8:30), (35, 10, 1, 2, 8:45). . .

Oh, I'm reading your other posting now. Well then, we will just work
with what we have.

Not quite. I want a report that shows Rt. 3500 on the left, and then
Rt. 3510 on the right, like this:

Trip#, Stop01, Stop02, Stop03, Stop01, Stop02, Stop03
1 8:00 8:15 8:20 8:25 8:30 8:35

You can create this report using your current table by aliasing the
table twice within the same query:

select a.route, a.trip, a.stop1, a.stop2, a.stop3, b.stop1 as return1,
b.stop2 as return2, b.stop3 as return3
from Trips as a, Trips as b
where a.route = b.route
and a.trip = b.trip

The records are grouped based on trip number. However, sometimes the
inbound trip 1 (ending in 10) happens before the outbound trip 2
(ending in 00), so I would then need to sort columns 2 and 5 based on
time. Does that make sense?

No. You lost me on this part. Everything makes sense except for
"columns 2 and 5" - I don't know what this means.

Perfect! That was exactly what I was looking for.

Thank you again for your response, and I hope I have clarified the
situation a little.

You're very welcome. Too bad about that "Trips" table.


-Kris

 

[pepandmax]

The records are grouped based on trip number. However, sometimes the

No. You lost me on this part. Everything makes sense except for
"columns 2 and 5" - I don't know what this means.

Apologies--I was referring to the first stop time for outbound and the
first stop time for inbound.

You're very welcome. Too bad about that "Trips" table.

I agree. What I might end up doing is creating an "ideal" database,
and then working with the people who do the scheduling to get the data
into a format that could be imported from excel. The "Trips" table is
pretty much a spreadsheet imported into Access, anyway... defeats the
purpose of a database. :-X

You can create this report using your current table by aliasing the
table twice within the same query:

select a.route, a.trip, a.stop1, a.stop2, a.stop3, b.stop1 as return1,
b.stop2 as return2, b.stop3 as return3
from Trips as a, Trips as b
where a.route = b.route
and a.trip = b.trip

Ok that makes sense conceptually... let's see if I can get it to
work. But first I'm going to doodle around with another database
design.

Here's an unrelated question (that probably doesn't belong here in the
"reports" usenet group): some trips run Monday through Friday, some
run Saturday, some run all weekend, some run all weekdays, etc. How
would I handle that?

Thanks for your input Kris. I was just going to run with what I had
inherited based on the "Trips" table, but now I'm beginning to think
that it's worth the time to do this right... or just convince the
agency to drop six figures on a commercial software. ;-)

Nicole

 

[krissco]

Apologies--I was referring to the first stop time for outbound and the
first stop time for inbound.

Ok. So you would want to display 3510 on the left when stop1 of 3510
happens prior to stop1 of 3500?
You would want to display 3500 on the left when stop 1 of 3500 happens
prior to stop 1of 3510?

I agree. What I might end up doing is creating an "ideal" database,
and then working with the people who do the scheduling to get the data
into a format that could be imported from excel. The "Trips" table is
pretty much a spreadsheet imported into Access, anyway... defeats the
purpose of a database. :-X

If you are using Access only for reporting, you may want to leave
things as they are - it won't be too much work to fudge this report.
However, if you suspect your company will want to query the data ("how
many stops did we do last year for route XXXX?") then it will be very
worthwhile to store this data in a relational design.

If the people doing the scheduling in Excel are consistent with their
spreadsheet format, it will be possible to automate excel, cell-by-
cell, and import the data into Access. The schedulers could even be
left in the dark. Consistency is the thing - if you are expecting the
route# to be in column B, and they place it in column A - watch out!

I forgot something in this query - it joins an inbound route with
itself as well as with the outbound. You would want a couple more
criteria on it:

1. replace "a.route = b.route" with "left(a.route, 2) = left(b.route,
2)"

2. Add the following criteria:
.. . .
and right(a.route, 2) = '00'

Here's an unrelated question (that probably doesn't belong here in the
"reports" usenet group): some trips run Monday through Friday, some
run Saturday, some run all weekend, some run all weekdays, etc. How
would I handle that?

How does the day of week, and number of times ran through week
correspond to your data? Is this the trip number? Are you saying that
some routes have Trip# 1-5 (all weekdays), some have 1-2 (weekends
only), some have 1-6 (M-F + Sat). Can you post some more sample data
and explain what the data means?


-Kris

 

[pepandmax]

Ok. So you would want to display 3510 on the left when stop1 of 3510

happens prior to stop1 of 3500?
You would want to display 3500 on the left when stop 1 of 3500 happens
prior to stop 1of 3510?
Exactly.

If you are using Access only for reporting, you may want to leave
things as they are - it won't be too much work to fudge this report.
However, if you suspect your company will want to query the data ("how
many stops did we do last year for route XXXX?") then it will be very
worthwhile to store this data in a relational design.

If the people doing the scheduling in Excel are consistent with their
spreadsheet format, it will be possible to automate excel, cell-by-
cell, and import the data into Access. The schedulers could even be
left in the dark. Consistency is the thing - if you are expecting the
route# to be in column B, and they place it in column A - watch out!

Part of the reason why the agency needs to move toward a database is
precisely because the schedulers don't always put things where they
should. Stops are known by various names and no one knows which is
the "correct" name, trip numbers don't always happen sequentially or
there are duplicates, and in general there are tons of errors due
simply to the fact that all of the schedules and driver assignments
are handled with SEPARATE SPREADSHEETS. Not even one workbook/ many
sheets--but actual separate files, none of which are standardized or
consistent and none of which reference the others.

Having said all of that, my ideal is that the schedulers would use the
database to create the service (routes & assignments). Because of
that, I've decided to take a step back and to try to do this right.
If you go here: http://i111.photobucket.com/albums/n127/lady_gerbil/misc/ScheduleDatabaseRelationships18AUG0.jpg
you can see how I've set up the tables and relationships... what do
you think? Let me know if there's any new terminology that you're
unsure of... to be perfectly honest, I'm not that experienced with
Access at all, and I'm doing all of this on my own time, learning as I
go along. Anyway thanks for all of your help, I'm sure I'm going to
be posting other questions as I dig deeper into this project.

How does the day of week, and number of times ran through week
correspond to your data? Is this the trip number? Are you saying that
some routes have Trip# 1-5 (all weekdays), some have 1-2 (weekends
only), some have 1-6 (M-F + Sat). Can you post some more sample data
and explain what the data means?

A trip can occur on any day of the week, or multiple days of the
week. Right now I have a table "Days" as follows:

DaysID Day
1 Weekdays
2 Weekends
3 Mondays
4 Tuesdays
....

I'm assigning a DaysID to each trip based on when it occurs. This
isn't an ideal solution, however, because the "Days" table contains
more than one kind of information (both single days and groups of
days). An alternative would be to have a column for each day, and
then to use Yes/No. Which would you recommend? Are there other
solutions? I will probably post a similar question in the "Tables"
group.

Thanks again for your help.
Nicole

 

[krissco]

Part of the reason why the agency needs to move toward a database is

precisely because the schedulers don't always put things where they
should. Stops are known by various names and no one knows which is
the "correct" name, trip numbers don't always happen sequentially or
there are duplicates, and in general there are tons of errors due
simply to the fact that all of the schedules and driver assignments
are handled with SEPARATE SPREADSHEETS. Not even one workbook/ many
sheets--but actual separate files, none of which are standardized or
consistent and none of which reference the others.

Out w/ the old, in with the new (and improved). The company I work for
was still using pen & paper when I started - it was a nightmare.

Having said all of that, my ideal is that the schedulers would use the
database to create the service (routes & assignments). Because of
that, I've decided to take a step back and to try to do this right.
If you go here: http://i111.photobucket.com/albums/n127/lady_gerbil/misc/ScheduleData...
you can see how I've set up the tables and relationships... what do
you think? Let me know if there's any new terminology that you're
unsure of... to be perfectly honest, I'm not that experienced with
Access at all, and I'm doing all of this on my own time, learning as I
go along. Anyway thanks for all of your help, I'm sure I'm going to
be posting other questions as I dig deeper into this project.

Ok. I have a couple suggestions.

1. I (personally) find symbols in field names to be an aggravation
(Route#) - they cause you to type the [] brackets around each field
name.
2. I don't like plural table names - it makes documentation confusing
(and when you think of a table as a collection of records, they are
all plural in a way aren't they?).

Here is how I understand your data:
A muni has many stops (each stop is contained within a muni).
A route has many stops. A stop can be visited by many routes.
A trip is a route + a series of stops + a time of arrival at each
stop. Trips are numbered.
A trip, due to the timed nature of things, may be driven only once per
day.
A driver may drive more than one trip in a given day.
A schedule includes a driver, a trip, and a date. (this is the
simplest form. You grouped several into more of an administrative
schedule than a trip schedule).

I don't understand what Block# is all about.
I would get rid of the DaysID stuff altogether - you may decide to
keep it to help the person(s) scheduling to enter good data.

1. Define your munis
2. Define all the stops within those munis
3. Define the stops within a route (no sequence at this point - this
will handle both inbound and outbound)
4. To build a trip, select a route, select a stop in that route, and
give it a time. Repeat.
5. To schedule a trip, select a driver, a trip, and a date.
6. Modify to your heart's content.

Don't. Schedule them day-by-day using the date. You could link a field
in the Trip table to your DaysID (ValidDays?) and use the BeforeUpdate
of the Schedule form to make sure the Date given corresponds with the
ValidDays of the Trip selected:

daysAvail = DLookup(valid days for the current trip number)

select case daysAvail
case 1 'Monday only
if weekday(me.ScheduleDate) <> vbMonday then blah
case 2 'M-F
if weekday(me.ScheduleDate) = vbSunday or . . . blah
end select

If that is unclear, let me know.

-Kris