2-column pickup/dropoff report? 2

[dwmatteson]

Hi all --

I'm trying to create a 2-column report with pickup and dropoff appointments. The information for pickups and dropoffs is located in two different tables (clients and sponsors, respectively).

I would like to generate an integrated schedule with dropoffs in the left column, sorted and separated by appointment time, and pickups in the right column, also sorted and separated by appointment time.

Ideally, I'd have a single time heading with dropoffs on the left and pickups on the right, so it'd look something like this:
[tt]
8:30AM
Dropoff 1 | Pickup 1
Dropoff 2 | Pickup 2

9:00AM
| Pickup 3
| Pickup 4

9:30AM
Dropoff 3 |
Dropoff 4 |
[/tt]
and so forth....

Can this be done? I'm out of ideas!

Thanks in advance,

Don

 

[hsitz]

You can do it, but you won't want to use a report which is truly "2 column", which really just means that it starts printing in a second column after it reaches the bottom of the first.

Use a regular report that isn't split into columns.

Then create a query that generates information in the row format you need. The query recordset should end up with three fields:

Time | Dropoff | Pickup

In your report, set it to group on the time field. This will make it easy to get the kind of output you want. Put the time in the header of the group, and put the dropoff and pickup fields in the group detail. The dropoff and pickup fields will be your "two columns".

Tricky thing will be to design the query so you get the information in the form you want, but that really shouldn't be too hard either. Without more information about your table structures nobody can give you much help on how to do that. -- Herb

 

[dwmatteson]

Thanks Herb --

Good point about table structure. The information is set up essentially like this:

Sponsors (dropoffs):
Sponsor ID, Sponsor Name, Sponsor Dropoff Day, Sponsor Dropoff Time

Clients (pickups):
Client ID, Client Name, Client Pickup Day, Client Pickup Time, Sponsor ID

The clients and sponsors are linked on Sponsor ID.

The first thing I tried is similar to your suggestion: I put the sponsor and client fields in the detail, but I ran into problems immediately after that -- how to sort both by Sponsor Dropoff Time and by Client Pickup Time. This report was based on a single query that returned clients and their associated sponsor, sort of glomming together all the fields I listed above.

I also tried setting up a report with two subreports, which is almost what I need. The problem with this strategy is that I can't get the times to line up the way I need them to, since they really should be under a single heading.

I'm getting to the point where it's going to be printout + scissors + copier = custom report!

Again, thanks for any suggestions.

Don

 

[hsitz]

The query was a little more complicated than I'd thought!

Below is the SQL for a set of four queries that should get you close to what you need.

The first two (qrySponsorTimes and qryClientTimes) just get separate unique lists of the date/times of all dropoffs and pickups.

The third query performs a union operation so that you have one of every date/time combination that appears in either of the first two. (To make a UNION query you have to choose 'SQL specific' from query menu and you won't have any visual design tools, just the SQL window.)

Then in the fourth query you link up the times in the first query with the sponsor and client times records, making sure that you combines them so that you LEFT joins so that records will show up even if there is just a dropoff or just a pickup but not both. Not sure what fields you wanted showing for "Dropoff1" and "Pickup1", I have the client name and sponsor name showing in the fourth query below. (Feel free to let me know if you have problems):

qrySponsorTimes:
"SELECT DISTINCT sponsor.sponsordropoffday, sponsor.sponsordropofftime
FROM sponsor
ORDER BY sponsor.sponsordropoffday, sponsor.sponsordropofftime;"

qryClientTimes:
"SELECT DISTINCT clients.clientpickupday, clients.clientpickuptime
FROM clients
ORDER BY clients.clientpickupday, clients.clientpickuptime;"

qryTimesUnion:
"SELECT * FROM qrySponsorTimes UNION SELECT * FROM qryClientTimes"

qryWhatYouWant:
"SELECT qryTimesUnion.clientpickupday, qryTimesUnion.clientpickuptime, clients.clientname, sponsor.sponsorname
FROM (qryTimesUnion LEFT JOIN clients ON (qryTimesUnion.clientpickuptime = clients.clientpickuptime) AND (qryTimesUnion.clientpickupday = clients.clientpickupday)) LEFT JOIN sponsor ON (qryTimesUnion.clientpickuptime = sponsor.sponsordropofftime) AND (qryTimesUnion.clientpickupday = sponsor.sponsordropoffday);"

 

[hsitz]

Hmm. Just checked and these don't quite get you all the way there. Problems with records getting repeated with multiple dropoff and/or pickups at same time. Maybe you already tried something like this. Well, I'll take a quick look to see if there's an easy fix or workaround. . . -- Herb

 

[hsitz]

OKAY. . . Not sure how to get there with SQL, though I'm sure there's some way, but here's how I just did what you want.

I took the 3rd query in the post above, the query that gets the union of all client date/times and all sponsor date/times, and made it the record source of a report. Then I set up grouping on the report on date and time.

Then I put in two subreports (I actually inserted subforms into the report) one for clients and one for sponsors. Both are linked to the recordsource of the report by date and time. And both are set to have their 'Can Grow' property set to yes. This gives just what you were looking for, I think. -- Herb

 

[dwmatteson]

Herb --

WOW! That was an amazingly helpful series of posts. I've put together the queries you listed above and it definitely gets me most of the way there.

If I understand correctly, the union query constructs a table consisting of both sponsor and client names, IDs, and appointments. From there, I need to separate out the clients from the sponsors, then drop them into separate subreports, each sorted by dates/times.

Where I'm getting caught short (again), is how to separate these groups from one another. That is, how can I specify that one subreport is for clients, the other for sponsors if I don't have a unique identifier for the table source? Or is that exactly what I'm missing?

Sorry for being dense -- it's just frustrating to be so close but unable to figure out how to go those last few steps.

Don

 

[hsitz]

The union query actually just compiles a unique list of date/time records, regardless of whether they are for pickups or dropoffs. Presumably some of the times will have pickups, some will have dropoffs, and some will have both.

Then you use that as the record source of your report. So that report recordsource has only two fields: date and time. Report is grouped on date and time.

You then create two subreports and link them on both the date field and the time field. So for each date/time group the clients subreport will show records with that date/time and the sponsors subreport will show all records with that date/time. Can visualize it like this:

The report will make a separate group for each different date/time item.

for each:
-------------------------------------------
Report Group: date/time
-------------------------------------------
--client subreport-- ---sponsor subreport ---
clientname for all records sponsorname for all
matching group date/time records matching group
date/time
------------------------------------------


This pattern will repeat over and over for all date/times included in the report recordsource.

If that doesn't get you there, email me and I'll send you an .mdb with a working setup. -- Herb

 

[hsitz]

"Where I'm getting caught short (again), is how to separate these groups from one another. That is, how can I specify that one subreport is for clients, the other for sponsors if I don't have a unique identifier for the table source? Or is that exactly what I'm missing?"

That may be what you're missing. The identifier for the client and sponsor records to include in each subreport is in both cases the date/time field combination. (Not necesarily a unique identifier, since there may be multiple clients or sponsors for any given date/time.)

The recordsources for the client subreport is simply the CLIENTS table, the recordsource for the sponsor subreport is simply the SPONSORS table.

 

[dwmatteson]

Hi hsitz --

Just wanted to say thanks for the help. Your design worked perfectly and I was able to provide the scheduling as I'd envisioned it. It's folks like you who make sites like this go!

Again, many thanks.

Don