Cross-tab to look like Calendar

[ant1983]

Cant quite wrap my head around this one...

I've created a Training Database that is used to capture training bookings.

I want to create a “Training Calendar†so basically want to show the course
names (there are only 6) as column headings then have all the dates of the
year on the left as row headings (not just the training dates but rather
every single day of the year as it is going to be a calendar at the end of
the day and then have the Facilitators name where it crosses.

So I thought a cross tab query was the way to go and so I created it and it
works great except for the following problem:

Because the sessions are either a 1 day session or two day sessions I cant
quite figure out how I would show the facilitators name on both days. The
cross tab query works but shows the facilitator’s name on the first day but
not the second.

Here’s what ive done:

Step 1: I created a table called tblDates with only one field “Dates†– I
populated that field with each day of the year so it is 365 entries (this is
the only way I could figure out how to use this later as a row heading so
don’t laugh

Step 2: I created a query called qryCalendar and added tblDates as well as
tblTrainingSessions (tblTrainingSession are all of the actual training
sessions available including these fields (just some of them:
autTrainingSessionID, txtCourse (the course name), dateStartDate, dateEndDate)

Step 3: I linked the “Dates†from tblDates with dateStartDate in
tblTrainingSessions.

Step 4: I added “Dates†to the query and then added all fields from
tblTrainingSession and saved it as qryCalendar and Closed it.

Step 5: Created a cross-tab query called qryCalendar_Crosstab with
qryCalendar and tblFacilitator (where I capture all facilitators names and
other details) and in this query I added: “Dates†as the Row Heading,
txtCourse as the Column Heading and txtName (from tblFacilitator) as the
Value (Total: First)

The result is my calendar but the problem is it show the facilitators name
on day 1 but not day two. Now I know why – Its because in qryCalendar I
linked “Dates†with dateStartDate and not with dateEndDate but I cant quite
figure out how to do it differently? ïŠ

(Hows that for a detailed question? )

Thanks for the help and reading my very long question.

ant1983

 

[Allen Browne]

1. Create a query using tblDates and tblTrainingSession, but with *no* line
joining them in the upper pane of table design.

2. In the Criteria row under the date field from tblDates, enter:
Between tblTrainingSession.dateStartDate And tblTrainingSession.dateEndDate
This query gives you a record for each date that the event occurs (i.e. if
it runs over 2 days, you get 2 records.) Save.

3. Create another query using the query you just saved and tblDates as input
tables, and join them on the dates. Double-click the join line between the 2
tables in the upper pane of query design. Access pops up a dialog with 3
options. Choose the one that says:
All records from tblDates, and any matches from Query1.
This is known as an outer join, and it gives you a record for every day
(whether there's an event or not.)

You can then go onto building a crosstab based on that if you wish, but it
won't be easy to sort out multiple events on the same date.

It might be easier to use a main report bound to the dates, with a subreport
to list the events for the date.

Duane Hookom has an example of some calendar reports here:
http://www.access.hookom.net/Samples.htm