Solved

TRANSFORM query for MS Access using .NET

Posted on 2010-11-25
3
267 Views
Last Modified: 2012-05-10
Below I can't do the first query but can do the second query. The queries are the same, creating a make table query on the result of another query, but the second query has a TRANSFORM and PIVOT part while the first doesn't. So, I don't know much abou that type of query... except that it didn't work... what's the reason for that? (I mean, both simply produce a table as a result... so I don't know why I can't call that table 'Temp' and then SELECT INTO a new table called RevenueByCountryAct.

CAN'T do this query

strSQL = "SELECT Temp.* INTO RevenueByCountryAct FROM"

strSQL = strSQL + " (TRANSFORM Sum([Table1].BillAmount) AS SumOfBillAmount"

strSQL = strSQL + " SELECT [Table1].FILESOURCE, [Table1].TYPE, [Table1].[Product Code], [Table1].Service, [Table1].Service2, [Table1].Service3, [Table1].COUNTRY"

strSQL = strSQL + " FROM [Table1]"

strSQL = strSQL + " GROUP BY [Table1].FILESOURCE, [Table1].TYPE, [Table1].[Product Code], [Table1].Service, [Table1].Service2, [Table1].Service3, [Table1].COUNTRY"

strSQL = strSQL + " PIVOT [Table1].[Billing Period]) AS Temp"

Open in new window


CAN do this query

strSQL = "SELECT Temp.* INTO RevenueByCountryAct FROM"

strSQL = strSQL + " (SELECT [Table1].FILESOURCE, [Table1].TYPE, [Table1].[Product Code], [Table1].Service, [Table1].Service2, [Table1].Service3, [Table1].COUNTRY"

strSQL = strSQL + " FROM [Table1]"

strSQL = strSQL + " GROUP BY [Table1].FILESOURCE, [Table1].TYPE, [Table1].[Product Code], [Table1].Service, [Table1].Service2, [Table1].Service3, [Table1].COUNTRY"

strSQL = strSQL + ") AS Temp"

Open in new window

0
Comment
Question by:AidenA
3 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34213594
That is not possible with Access syntax.
What you can do is store the TRANSFORM query by name, then follow up with another
select * into newtable from QueryWithTransform
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34214861
Pres Alt+F11, Microsoft Visual Basic Help, Microsoft Jet Reference, Data Manipulation Language, SQL Subqueries - this is of interest:

Some subqueries are allowed in crosstab queries — specifically, as predicates (those in the WHERE clause). Subqueries as output (those in the SELECT list) are not allowed in crosstab queries.



0
 

Author Comment

by:AidenA
ID: 34218119
thanks cyberkiwi, followed your suggestion and it worked fine. Not the way I'd prefer to do it, but it shouldn't be a problem at the same time so I'll go ahead with that.

Thanks, Aiden
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now