saabStory
asked on
Help needed with creating a query with 3 tables
I'm stuck on this -- I need to create a query - the results of which will eventually be viewed via a spreadsheet. It needs to show the capacities of different lodges/units on our properties on a given weekend during the year. The report I've been asked to create will have headers with the property name in column 1 followed by the name of each individual lodge/unit in separate columns.
The data will be the start and end dates in column 1 followed by the capacities for each lodge during that time frame - and of course the capacities need to line up under the correct lodge/unit name. The number of lodges varies for each of the 6 camps and not every lodge is used every weekend. I've attached an excel file with a sample of what I'm talking about.
At some point in the future, we plan on putting a web front end on this data and let the content owners update their own data. But first thing is to get a report working.
The tables have been created but we can change schemas if needed. There's more to it than what I'm showing here but this is the thing at it's most basic.
The current layout of the tables are:
tbl_CampNames
=========================
campID - int (PK)
campName - varchar(50)
tbl_CampUnits
=========================
unitID - int (PK)
campID - int
unitName - varchar(50)
unitType - varchar(25)
tbl CampDates
=========================
id - int (PK)
campID - int
untiID - int
dateStart - date
dateEnd - date
capacity - int
As always, many thanks in advance.
experts-example.xlsx
The data will be the start and end dates in column 1 followed by the capacities for each lodge during that time frame - and of course the capacities need to line up under the correct lodge/unit name. The number of lodges varies for each of the 6 camps and not every lodge is used every weekend. I've attached an excel file with a sample of what I'm talking about.
At some point in the future, we plan on putting a web front end on this data and let the content owners update their own data. But first thing is to get a report working.
The tables have been created but we can change schemas if needed. There's more to it than what I'm showing here but this is the thing at it's most basic.
The current layout of the tables are:
tbl_CampNames
=========================
campID - int (PK)
campName - varchar(50)
tbl_CampUnits
=========================
unitID - int (PK)
campID - int
unitName - varchar(50)
unitType - varchar(25)
tbl CampDates
=========================
id - int (PK)
campID - int
untiID - int
dateStart - date
dateEnd - date
capacity - int
As always, many thanks in advance.
experts-example.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Nice work lwadwell
saabStory, I have written a couple of articles about pivot that might be of interest for your studying.
https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html
https://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_4256-Dynamic-Pivot-Procedure-without-the-Pivot-function.html
saabStory, I have written a couple of articles about pivot that might be of interest for your studying.
https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html
https://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_4256-Dynamic-Pivot-Procedure-without-the-Pivot-function.html
ASKER
Thanks so much - have a great weekend!