350 fields in one table, need data at 3 different time intervals 1

[SouthwestNut]

I have a SQL table that has 350 fields and it holds sales data for the past 3 years for a number of hotels. My client wants a report that shows sales as follows:

Previous Day
Month to Date
Last year month to date

I would need this information for about 200 different fields. I have written 3 SQL Stored procedures for each data grouping, but what is the optimal way to combine the procedures together so it can be included on one report? To further compound the issue, parameters are used to allow users to identify which hotel they want to query and the date the information is effective.

 

[RickCole]

Two methods you could use are one stored procedure and use the UNION clause e.g.

Select col_1,col_2 from hotel_table where date_input = previous_day

union

Select col_1,col_2 from hotel_table where date_input = month_to_date

union

Select col_1,col_2 from hotel_table where date_input = last_year_month_to_date

The other method would be to insert all the results into a temp table and then do a final select from the temp table. Again this can be all in one single stored procedure.

Rick.

 

[SouthwestNut]

I was successful getting the information over using union statments. However, when I tried to use the information in a Access report (within an Access Project) I was coming up with errors. The Daily, MTD and Last Year MTD numbers are running across the page. By using the union statement I am forced to have the same field names for each data grouping. I added a new column that identified the purpose of each row in the recordset. But when I am unsuccessful getting Access to differentiate the fields between Daily, MTD and Last Year.

Then I tried to create a temp table which would allow me to identify the purpose of each field within the field name, but I have been unsuccessful thus far in determining how to extract data within a temp table.

 

[RickCole]

Would this do the trick??

select 0,'Previous day Page Header',null, null
union
select 1,'Previous day Results' ,col_1,col_2 from hotel_table where date_input = previous_day
union
select 2,'Month to Date Page Header',null, null
union
select 3,'Month To Date Results',from hotel_table where date_input = month_to_date
union
select 4, 'Yearly Page Header',null, null
union
select 5,'Year to Date Results' ,date_input = last_year_month_to_date

Rick

 

[SouthwestNut]

I tried both methods Rick suggested. When doing the union queries it combined all the records successfully, but left me with a recordset of 3 records. Unfortuantely I couldn't use that recordset when I assigned it as a recordsource to an Access report.

Using temporary tables worked perfectly as it gave me 720 fields in one long record. I had no problem then adding them to the report.

Thanks Rick!!