SQL Statement

  • Thread starter Thread starter Brian P. Hammer
  • Start date Start date
B

Brian P. Hammer

Cross Posted = 3


All - I have a SQL statement that I used in access and VB.Net to display as a pivot table and a chart on a VB form. I am now in the process of changing it all over to SQL data adapters. I cannot seem to get the Access statement to covert over to SQL. I get an error stating that there is an error at or before SoldRetailPrice. This worked just fine in the Access back end with straight ADO code but does not for the Data Adapters.


Dim sqlPivotData As String = "TRANSFORM Avg([SoldRetailPrice]/[AverageEquipmentPrice])" & _
"AS PercentValue Select YearOfManufacturer FROM tblAircraftRetail INNER JOIN " & _
"tblSoldHistory ON tblAircraftRetail.IDAircraftRetail = tblSoldHistory.RetailHistoryID " & _
"GROUP BY tblAircraftRetail.YearOfManufacturer, tblAircraftRetail.IDAircraftRetail " & _
"PIVOT [YearOfSale]+([QuarterOfSale]/10)"

Thanks,
Brian P. Hammer
 
Hi Brian,
TRANSFORM is an Access SQL addition . MS SQL server doesn't support a
transform extension.

You should rewrite the SQL using group by queries or you can use a third
party product

try this http://www.ag-software.com/xp_ags_crosstab.aspx

--
Kind regards
Greg Obleshchuk
Partner
A & G Software
http://www.ag-software.com


Cross Posted = 3


All - I have a SQL statement that I used in access and VB.Net to display as
a pivot table and a chart on a VB form. I am now in the process of changing
it all over to SQL data adapters. I cannot seem to get the Access statement
to covert over to SQL. I get an error stating that there is an error at or
before SoldRetailPrice. This worked just fine in the Access back end with
straight ADO code but does not for the Data Adapters.


Dim sqlPivotData As String = "TRANSFORM
Avg([SoldRetailPrice]/[AverageEquipmentPrice])" & _
"AS PercentValue Select YearOfManufacturer FROM tblAircraftRetail
INNER JOIN " & _
"tblSoldHistory ON tblAircraftRetail.IDAircraftRetail =
tblSoldHistory.RetailHistoryID " & _
"GROUP BY tblAircraftRetail.YearOfManufacturer,
tblAircraftRetail.IDAircraftRetail " & _
"PIVOT [YearOfSale]+([QuarterOfSale]/10)"

Thanks,
Brian P. Hammer
 
Check out the RAC utility for S2k.Similar to Access
crosstab but much more powerful with many options/features.
Easy to use with no complicated sql coding required.

RAC v2.2 and QALite @
www.rac4sql.net
 
I did check it out. So, once you get to the SQL View tab, and receive the expected output, what's next. How do I use it in my app? Sorry, kind of new to the whole SQL thing.

Thanks,
Brian

--
Brian P. Hammer
Check out the RAC utility for S2k.Similar to Access
crosstab but much more powerful with many options/features.
Easy to use with no complicated sql coding required.

RAC v2.2 and QALite @
www.rac4sql.net
 
Thanks Greg - I'll have to check it out.

--
Brian P. Hammer
Hi Brian,
TRANSFORM is an Access SQL addition . MS SQL server doesn't support a
transform extension.

You should rewrite the SQL using group by queries or you can use a third
party product

try this http://www.ag-software.com/xp_ags_crosstab.aspx

--
Kind regards
Greg Obleshchuk
Partner
A & G Software
http://www.ag-software.com


Cross Posted = 3


All - I have a SQL statement that I used in access and VB.Net to display as
a pivot table and a chart on a VB form. I am now in the process of changing
it all over to SQL data adapters. I cannot seem to get the Access statement
to covert over to SQL. I get an error stating that there is an error at or
before SoldRetailPrice. This worked just fine in the Access back end with
straight ADO code but does not for the Data Adapters.


Dim sqlPivotData As String = "TRANSFORM
Avg([SoldRetailPrice]/[AverageEquipmentPrice])" & _
"AS PercentValue Select YearOfManufacturer FROM tblAircraftRetail
INNER JOIN " & _
"tblSoldHistory ON tblAircraftRetail.IDAircraftRetail =
tblSoldHistory.RetailHistoryID " & _
"GROUP BY tblAircraftRetail.YearOfManufacturer,
tblAircraftRetail.IDAircraftRetail " & _
"PIVOT [YearOfSale]+([QuarterOfSale]/10)"

Thanks,
Brian P. Hammer
 
Back
Top