Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

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  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?

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    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.

 

 

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