Access to ASP.Net with CrossTab Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Access to ASP.Net with CrossTab Query

Author

Topic

Denisdh
Starting Member

1 Post

Posted - 2008-08-12 : 06:31:26

Hi,
I'm a bit of an SQL novice and I'm having trouble importing my Access table into my .Net Web Project. The issue is that the query I'm using is using TRANSFORM which isn't supported by SQL. Below is the query code:

TRANSFORM Count(tblReportData.ID) AS CountOfID
SELECT tblReportData.Agent, Count(tblReportData.ID) AS [Total Of ID]
FROM tblReportData
GROUP BY tblReportData.Agent
PIVOT tblReportData.County;

Does anyone know how I can bind the query to an AccessDataSource in ASP.Net? I found [url]http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables[/url] but I'm lost after reading through the article.

More Details

The orginal table has two columns;

Property_Agent, Property_Location
James Joyce, Dublin
Harrot Ford, Dublin
Harrot Ford, Dublin
James Joyce, Dublin
James Joyce, Dublin
James Joyce, Offaly
Harrot Ford, Offaly
Harrot Ford, Offaly
James Joyce, Offaly
James Joyce, Offaly
James Joyce, Offaly

I want to display a CrossTab like;

Property_Agent, Dublin, Offaly
James Joyce, 3, 4
Harrot Ford, 2, 2

Thanks
Denis

Cheers
Denis

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-08-12 : 12:39:04

in ASP.NET, the easiest way to do a cross tab is to use a matrix report.

see: http://msdn.microsoft.com/en-us/library/aa337450.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources