devxlogo

Creating a Schedule Timetable

Creating a Schedule Timetable

Question:
I have a table with five columns. They are “No. of People,” “Tour1,” Tour2,” “Tour3,” and “Tour4.” I want to set up a query that shows me how many people went to each tour by date.

My input table looks like this:

No. of People   Tour1   Tour2  Tour3  Tour43               5/3     5/4    5/5    n/a 2               5/4     5/3    5/5    5/64               n/a     5/4    5/3    5/4

My query table should show this:

 Date         Tour1   Tour2  Tour3  Tour45/3          3       2      4      05/4          2       7      0      45/5          0       0      5      05/6          0       0      0      2

That means having “Date” as my row heading, “Tour#” as my column heading, and “No. of People” (who joined that tour) as my cell content. How do I do this?

Answer:
Take a deep breath.

I think what you really have a design problem here, not a query problem. The first red herring is the column names you use; Tour 1 … Tour N is a text-book illustration of a repeating group and, as such, is a non-normal relation.

What if the table were structured like this instead?

 TourName      TourDate    TouristQtyTour 1        5/3         3Tour 1        5/4         2Tour 2        5/3         2Tour 2        5/4         7

I think this would simplify your issues, even though you are storing a total in TouristQty.

devx-admin

Share the Post: