Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL Server
Expertise: Beginner
Sep 14, 1999



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Creating a Schedule Timetable

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  Tour4
3               5/3     5/4    5/5    n/a 
2               5/4     5/3    5/5    5/6
4               n/a     5/4    5/3    5/4
My query table should show this:
Date         Tour1   Tour2  Tour3  Tour4
5/3          3       2      4      0
5/4          2       7      0      4
5/5          0       0      5      0
5/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?

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    TouristQty
Tour 1        5/3         3
Tour 1        5/4         2
Tour 2        5/3         2
Tour 2        5/4         7
I think this would simplify your issues, even though you are storing a total in TouristQty.
DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date