Friday, March 30, 2012
Multiple items in one row
I have an Access database that can be used to reserve resources. The user inputs a date range and then checks off what they want to reserve. It looks like this:
StartDate .............EndDate .........Laptop......Camera........Projector
10/29/2007...........10/29/2007........................X
10/29/2007...........10/31/2007..........X
10/31/2007.......... 11/01/2007........................X..................X
Of course there is other info, and a return date, but none of that is needed for this report.
I want the report to show what is available. A blank means the item is available and the X means it is unavailable. It should look like this:
Date...............Laptop.............Camera..........Projector
10/29/07.............X.....................X
10/30/07.............X
10/31/07.............X.....................X..................X
11/1/07...............X.....................X
The dates in the report come from a date table which make up Group Header 1.
This is a sample of the formula I used to get the X's to show up in the Detail section.
if {DateTable.Date}>={Reservation.StartDate}and{DateTable.Date}<={reservation.EndDate}
then if {reservation.Laptop}=true
then "X"
This works but the x's from different reservations show up on different lines. I was able to get the first detail line to match up with the date, by choosing Underlay Following Sections, but the others still show up in a different line. For example 10/31/07 looks like this:
Date.........Laptop.........Camera.............Projector
10/31/07.........X
....................................X.....................X
Your help is much appreciated!Try to create 1 formula for each of the fields you need to be shown as 'X',
for example:
@.Laptop:
if isnull(your_table.Laptop) then 'X'
@.Camera:
if isnull(your_table.Camera) then 'X'
.
.
.
Use them instead of the fields.|||I am not using fields. I am using a formula for each item. For example @.laptop looks like this:
if {DateTable.Date}>={Reservation.StartDate}and{DateTable.Date}<={reservation.EndDate}
then if {reservation.Laptop}=true
then "X"
I just want all the x's to appear on the same row as the date so if one person reserves something on 10/31 and someone else has reserved something else on the same day, (which is represented on two rows in the database) I want those items to have an X under them on the 10/31 row. I can get the first one to get up to the date row by using the Underlay, but I can not get the other rows up.
I've tried putting the details in with the group header, but everything disappears.
Maybe I should change the database? I don't know. Let me know what you think!
Wednesday, March 7, 2012
MULTIPLE COLUMNS using and GROUP BY
I need to return City Names from the City Table ordered by province and postal code. However, SELECT statement returns multiple occurrences of a City_Name from the Cities table. I only want the City Name to appear once though.
Here's the SQL statement:
"SELECT City_Name, Province_ID, City_PostCode
FROM Cities
WHERE City_PostCode >= " + startcode + " AND City_PostCode <= " + endcode
AND City_Name != '" + exclude
ORDER BY Province_ID ASC, City_PostCode ASC ";
I've tried to use distinct on the City Name but that didn't work. It seems the distinct keyword has to use all the column names to return a distinct record. An alternative would be to GROUP the records by City_Name however you have to use all the columns that were selected leaving me with the same distinct keyword problem.
Can anyone help me?
hi,
Well, if you have different zip codes to the same City, you should have duplications and no matter what you use; distinct or group by.
eg:
City_name Province_ID City_PostCode
Los Angeles CA 90150
Los Angeles CA 90151
In this case, you should not have one record, based on your select statement. Maybe you should appear a zip code range, instead of individual zips, then you should have distinct result set.
Also, if just change the second zip to the first one, and using distinct you will have one record. you do not need to specify column level the distinct, it applied on all enumerated column in the select statement.
I hope it helps.
Kind Regards,
Janos
|||If you want to show the multiple postal codes as single row then you can use the following query.. One row per city with out data loose..
Code Snippet
Create Table #cities (
[City_name] Varchar(100) ,
[Province_ID] Varchar(100) ,
[City_PostCode] Varchar(100)
);
Insert Into #cities Values('Los Angeles','CA','90150');
Insert Into #cities Values('Los Angeles','CA','90151');
Insert Into #cities Values('Austin','TX','73301');
Select
Distinct
Main.City_name
, Main.Province_ID
, Substring((Select ',' + City_PostCode as [text()]
From #cities Sub
Where Sub.City_name = Main.City_name
And Sub.Province_ID = Main.Province_ID For XML Path('')),2,8000) as [City_PostCodes]
From
#cities as Main
|||Thanks Jano, I updated the statement to reflect the following:
"SELECT DISTINCT City_Name, Province_ID FROM Cities WHERE City_Name IN ( SELECT City_Name FROM Cities WHERE City_PostCode >= " + startcode + " AND City_PostCode <= " + endcode AND City_Name != '" + exclude + "' )
ORDER BY Province_ID ASC ";
The only thing is I can't sort the selections according to there postal codes. however the intial problem of the distinct fields is resolved.
Thanks again.
Regards,
Simon
|||
You can sort by postal_code (using OVER clause)..
"SELECT DISTINCT City_Name, Province_ID,Max([City_PostCode]) Over (Partition By City_name,Province_ID) as [City_PostCode] FROM Cities WHERE City_Name IN ( SELECT City_Name FROM Cities WHERE City_PostCode >= " + startcode + " AND City_PostCode <= " + endcode AND City_Name != '" + exclude + "' ) ORDER BY [City_PostCode] ASC ";