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.
