Solved

TRANSFORM query for MS Access using .NET

Posted on 2010-11-25
3
272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
default value of combobox 25 41
Omit After Update event 5 17
Trying to delete two linked tables from front-end 7 16
Balance After Payment 12 16
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

726 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