Report with unknown number of varying sections. Ideas?

[Larry]

I have to create a travel itinerary for a database I just created for a
large project that does a lot of traveling in our company. Now that I am at
the end of it, they do not like the printed itinerary I produced!

Basically I created 3 subreports. One for flight information, another for
hotel and another for car rental. I put these three subreports on a report
which has the traveler information at the top of the report. So the report
first shows all the flights the user will be taking (in departure order),
then all the hotels they will be staying in (sorted in arrival order) and
then all the car rentals (in pickup date order). The problem now, though, is
that the user wants the report to be completely sorted by date/time! So they
get the flight(s) first, then the hotels and car rental, then the return
flights.

Ok, so that I could probably do. But, since many of these engineers are
traveling all over the world to some holes in the wall, they could have
something like 2 flights, a hotel stay, then 3 flights, another hotel stay
and a car rental, then another flight and a hotel stay then another 4
flights to get home. And, you can do that in just about any order you want
with just about as many different ways as you can dream of.

I have been able to put a Union query together to get the PK's for each of
the records that would need to be printed, in the proper date order. So I
"could" probably put this into some kind of grid type report. But no, the
user does not want a grid. They want it sectioned and then information on
the report in what Access would call "Columnar" style. That was the main
reason I went with the subreports in the first place.

My ONLY thought is to try to figure out how to loop through this query I
created, get the associated subReport for the current record and add it to a
new report, dynamically, and set the filter for that report to that
particular PK (or range of PK's if there might be multiple records in a row,
i.e. flights).

But, first I don't know how to do this (yet) and second I bet this is a bad
idea. I can just imagine how large this database would become, creating and
destroying reports on the fly. It's just that I cannot think of any other
way to do this, other than the dynamic approach.

Does anyone have any other ideas? Can anyone tell me how to go about
creating a report from VBA and adding subReports in this manner?

Thanks for reading all this confusing problem.

 

[Marshall Barton]

I have to create a travel itinerary for a database I just created for a
large project that does a lot of traveling in our company. Now that I am at
the end of it, they do not like the printed itinerary I produced!

Basically I created 3 subreports. One for flight information, another for
hotel and another for car rental. I put these three subreports on a report
which has the traveler information at the top of the report. So the report
first shows all the flights the user will be taking (in departure order),
then all the hotels they will be staying in (sorted in arrival order) and
then all the car rentals (in pickup date order). The problem now, though, is
that the user wants the report to be completely sorted by date/time! So they
get the flight(s) first, then the hotels and car rental, then the return
flights.

Ok, so that I could probably do. But, since many of these engineers are
traveling all over the world to some holes in the wall, they could have
something like 2 flights, a hotel stay, then 3 flights, another hotel stay
and a car rental, then another flight and a hotel stay then another 4
flights to get home. And, you can do that in just about any order you want
with just about as many different ways as you can dream of.

I have been able to put a Union query together to get the PK's for each of
the records that would need to be printed, in the proper date order. So I
"could" probably put this into some kind of grid type report. But no, the
user does not want a grid. They want it sectioned and then information on
the report in what Access would call "Columnar" style. That was the main
reason I went with the subreports in the first place.

[snip the bad idea ;-) ]


The key to getting any report working the way you want is to
first create a query that provides the necessary data. In
this case probably by Joining the needed tables without
resorting to the use of subreports.

If you will post the tables and their relevant fields,
someone will be able to provide more specific assistance.

 

[Larry]

I have to create a travel itinerary for a database I just created for a
large project that does a lot of traveling in our company. Now that I am at
the end of it, they do not like the printed itinerary I produced!

Basically I created 3 subreports. One for flight information, another for
hotel and another for car rental. I put these three subreports on a report
which has the traveler information at the top of the report. So the report
first shows all the flights the user will be taking (in departure order),
then all the hotels they will be staying in (sorted in arrival order) and
then all the car rentals (in pickup date order). The problem now, though, is
that the user wants the report to be completely sorted by date/time! So they
get the flight(s) first, then the hotels and car rental, then the return
flights.

Ok, so that I could probably do. But, since many of these engineers are
traveling all over the world to some holes in the wall, they could have
something like 2 flights, a hotel stay, then 3 flights, another hotel stay
and a car rental, then another flight and a hotel stay then another 4
flights to get home. And, you can do that in just about any order you want
with just about as many different ways as you can dream of.

I have been able to put a Union query together to get the PK's for each of
the records that would need to be printed, in the proper date order. So I
"could" probably put this into some kind of grid type report. But no, the
user does not want a grid. They want it sectioned and then information on
the report in what Access would call "Columnar" style. That was the main
reason I went with the subreports in the first place.

[snip the bad idea ;-) ]


The key to getting any report working the way you want is to
first create a query that provides the necessary data. In
this case probably by Joining the needed tables without
resorting to the use of subreports.

If you will post the tables and their relevant fields,
someone will be able to provide more specific assistance.

Getting the data in the right order is not the answer to this problem. I
have figured out how to do that with a union statement. The problem is the
format of the report. It needs to be something like what is below. This is
the reason I created subreports, with each subreport being one of the
categories of information. You could have any category in any order and need
the report to print them in this format.

As far as the tables go, there is one table for each of the categories
below. There is a main table with the traveler information, which generates
a record locator (Autonumber) that ties all the tables together. Plus, each
category table has their own ID field that is an Autonumber. So, you
basically have the description of the tables with that information.

RESERVATION FOR
Last Name: xxxxxx First Name: xxxxxxx
Reservation Number: xxxxxxxxxxx

FLIGHT INFORMATION
Flight: xxxx
Departs: xxxxx
Arrives: xxxxx
Ticket #: xxxxx
Conf#: xxxxx

Flight: xxxx
Departs: xxxxx
Arrives: xxxxx
Ticket #: xxxxx
Conf#: xxxxx

HOTEL INFORMATION
Hotel Name: xxxxx
Address: xxxxxx
Phone: xxxxx Fax: xxxxx
Check In: xxxxxx
Conf #: xxxxx

CAR RENTAL INFORMATION
Agency: xxxxxx
Pick up date: xxxxx
Pick up Address: xxxx
Drop off date: xxxxx
Drop off Address: xxxx
Conf #: xxxxxx

FLIGHT INFORMATION
Flight: xxxx
Departs: xxxxx
Arrives: xxxxx
Ticket #: xxxxx
Conf#: xxxxx

HOTEL INFORMATION
Hotel Name: xxxxx
Address: xxxxxx
Phone: xxxxx Fax: xxxxx
Check In: xxxxxx
Conf #: xxxxx

FLIGHT INFORMATION
Flight: xxxx
Departs: xxxxx
Arrives: xxxxx
Ticket #: xxxxx
Conf#: xxxxx

Flight: xxxx
Departs: xxxxx
Arrives: xxxxx
Ticket #: xxxxx
Conf#: xxxxx

Flight: xxxx
Departs: xxxxx
Arrives: xxxxx
Ticket #: xxxxx
Conf#: xxxxx

 

[Marshall Barton]

I have to create a travel itinerary for a database I just created for a
large project that does a lot of traveling in our company. Now that I am at
the end of it, they do not like the printed itinerary I produced!

Basically I created 3 subreports. One for flight information, another for
hotel and another for car rental. I put these three subreports on a report
which has the traveler information at the top of the report. So the report
first shows all the flights the user will be taking (in departure order),
then all the hotels they will be staying in (sorted in arrival order) and
then all the car rentals (in pickup date order). The problem now, though, is
that the user wants the report to be completely sorted by date/time! So they
get the flight(s) first, then the hotels and car rental, then the return
flights.

Ok, so that I could probably do. But, since many of these engineers are
traveling all over the world to some holes in the wall, they could have
something like 2 flights, a hotel stay, then 3 flights, another hotel stay
and a car rental, then another flight and a hotel stay then another 4
flights to get home. And, you can do that in just about any order you want
with just about as many different ways as you can dream of.

I have been able to put a Union query together to get the PK's for each of
the records that would need to be printed, in the proper date order. So I
"could" probably put this into some kind of grid type report. But no, the
user does not want a grid. They want it sectioned and then information on
the report in what Access would call "Columnar" style. That was the main
reason I went with the subreports in the first place.

[snip the bad idea ;-) ]

The key to getting any report working the way you want is to
first create a query that provides the necessary data. In
this case probably by Joining the needed tables without
resorting to the use of subreports.

If you will post the tables and their relevant fields,
someone will be able to provide more specific assistance.

Getting the data in the right order is not the answer to this problem. I
have figured out how to do that with a union statement. The problem is the
format of the report. It needs to be something like what is below. This is
the reason I created subreports, with each subreport being one of the
categories of information. You could have any category in any order and need
the report to print them in this format.

As far as the tables go, there is one table for each of the categories
below. There is a main table with the traveler information, which generates
a record locator (Autonumber) that ties all the tables together. Plus, each
category table has their own ID field that is an Autonumber. So, you
basically have the description of the tables with that information.

RESERVATION FOR
Last Name: xxxxxx First Name: xxxxxxx
Reservation Number: xxxxxxxxxxx

FLIGHT INFORMATION
Flight: xxxx
Departs: xxxxx
Arrives: xxxxx
Ticket #: xxxxx
Conf#: xxxxx

Flight: xxxx
Departs: xxxxx
Arrives: xxxxx
Ticket #: xxxxx
Conf#: xxxxx

HOTEL INFORMATION
Hotel Name: xxxxx
Address: xxxxxx
Phone: xxxxx Fax: xxxxx
Check In: xxxxxx
Conf #: xxxxx

CAR RENTAL INFORMATION
Agency: xxxxxx
Pick up date: xxxxx
Pick up Address: xxxx
Drop off date: xxxxx
Drop off Address: xxxx
Conf #: xxxxxx

FLIGHT INFORMATION
Flight: xxxx
Departs: xxxxx
Arrives: xxxxx
Ticket #: xxxxx
Conf#: xxxxx

HOTEL INFORMATION
Hotel Name: xxxxx
Address: xxxxxx
Phone: xxxxx Fax: xxxxx
Check In: xxxxxx
Conf #: xxxxx

FLIGHT INFORMATION
Flight: xxxx
Departs: xxxxx
Arrives: xxxxx
Ticket #: xxxxx
Conf#: xxxxx

Flight: xxxx
Departs: xxxxx
Arrives: xxxxx
Ticket #: xxxxx
Conf#: xxxxx

Flight: xxxx
Departs: xxxxx
Arrives: xxxxx
Ticket #: xxxxx
Conf#: xxxxx

OK, I think I've got a clearer idea of what you're doing
now. I presume that your union query looks something like:

SELECT Resevations.ReservationNumber,
Resevations.FirstName,
Resevations.LastName,
Flights.Departs AS ResDate
FROM Resevations INNER JOIN Flights
ON Resevations.ReservationNumber =
Flights.ReservationNumber
UNION ALL
SELECT Resevations.ReservationNumber,
Resevations.FirstName,
Resevations.LastName,
Hotels.CheckIn
FROM Resevations INNER JOIN Hotels
ON Resevations.ReservationNumber =
Hotels.ReservationNumber
UNION ALL
SELECT Resevations.ReservationNumber,
Resevations.FirstName,
Resevations.LastName,
Cars.PickupDate
FROM Resevations INNER JOIN Cars
ON Resevations.ReservationNumber = Cars.ReservationNumber

where the ResDate field is crucial to the rest of this
approach. I'm sure you'll also want to apply some kind of
filter to apply to this data, probably using the OpenReport
method's WhereCondition argument.

Now, use a main report based on the union query. Sort the
main report on the ResDate field.

Use three subreports, one each based on the Flights, Hotels
and Cars table, one immediately after the other. Set the
Detail section's and all of the subreport control's CanGrow
and CanShrink to Yes. Set all of the subreport's Link
Master property to the ResDate field and each subreport's
Link Child property to Departs, CheckIn and PickupDate
respectively.

I'm sure I left out a bunch of details, but I think that
kind of setup will come pretty close to what you're looking
for.

 

[Larry Jones]

Marshall,

I don't see how this will work. But I will try it over the weekend (if
I get a chance) or first thing Monday when I get to work.

It seems to me this is close to what I have already. So if I have the
subreports in Flight, Hotel, Car order. It seems to me that the flight
subreport would print all the flights for the current travler, then
all the hotels, then all the cars. At least, that's what it's doing
now.

I know, right now, I am not basing anything on the union yet, so maybe
that's the big difference. Right now the main report is based on the
traveler table. So that each of the subreports get the data for that
traveler. I'm not sure how your version will get the data for the
traveler, and print the subreports in the order as needed. But I am
more than willing to give it a try.

I'll post my results after I check it out.

Thanks for your help so far.

Larry

 

[Larry Jones]

Marshall,

It worked!

I couldn't wait any longer because I realized what the difference was
between what you suggested and what I was doing. I was only linking
the subreports by the Record Locator (that ties all the records
together). Once I added "date" to the sub report links, it does just
what I need.

This is part of what makes working with Access so much fun. Even after
12 years of working with it, I can still learn something new.

Thanks again for the help.

Larry

 

[Marshall Barton]

It worked!
Hooray!


I couldn't wait any longer because I realized what the difference was
between what you suggested and what I was doing. I was only linking
the subreports by the Record Locator (that ties all the records
together). Once I added "date" to the sub report links, it does just
what I need.

I had a horrible thought this morning that some of your pick
up or check in dates might not have a time part, in which
case they might not be in the right order for a day that has
both. I think there may be a way to deal with this by
adding a sort and link on a reservation type field.

This is part of what makes working with Access so much fun. Even after
12 years of working with it, I can still learn something new.

Ain't that the truth ;-)