SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 MS Access
 Using SQL Pivot table results in MS Access
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Angelflower
Starting Member

3 Posts

Posted - 10/07/2013 :  15:49:08  Show Profile  Reply with Quote
I have query that produces a pivot table (see below). I want to use those results that in a MS Access report. Normally I would use a view for this but I can't make it a view because of the #temp table part of the code. I tried to paste the code directly into a pass-through query but it errors out and tells me that "Pass-through query with ReturnsRecords property set to True did not return any records". When I run the code in SQL I get results.

Here is my pivot table:

Select
tbl_invoice_chart.ChartKey
, tbl_invoice_chart.start_of_coverage_date as Variable
, tbl_invoice_chart.SumOfqty_adj as VaribleValue

into #temp123

from tbl_invoice_chart
where tbl_invoice_chart.ChartKey not like '%EA%'
order by tbl_invoice_chart.start_of_coverage_date asc

DECLARE @columns VARCHAR(8000)
SELECT @columns = COALESCE(@columns + ',[' + cast(Variable as varchar) + ']',
'[' + cast(Variable as varchar)+ ']')

FROM #temp123
GROUP BY Variable
order by #temp123.variable asc

DECLARE @query VARCHAR(8000)
SET @query = '

SELECT *
FROM #temp123

PIVOT
(
MAX(VaribleValue)
FOR [Variable]
IN (' + @columns + ')
)
AS p'

EXECUTE(@query)

DROP TABLE #temp123

Sample output

Column headings:
ChartKey
Feb 1 2013 12:00AM
Mar 1 2013 12:00AM
Apr 1 2013 12:00AM
May 1 2013 12:00AM
Jun 1 2013 12:00AM

Row data:
5DZG03751Firm Gas951952
966.61
892.96
458.53
48144.40
0581.97
81



Live well... be happy
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000