| Author |
Topic |
|
gcool12
Starting Member
22 Posts |
 Posted - 2007-12-10 : 17:25:51
|
| I have created a query in access to display the data the way i want and have put the SQL into my ASP page but im not sure how to build a table to display it.The table should look like this: week 1 week 2 week 3 week4Dept1 2 3 3.5 3.4Dept2 2 2.5 2.75 4ETC......The SQL is as Follows<%Dim connection, sql, rsSet connection = Server.CreateObject ("ADODB.Connection")connection.open "DSN=5s"sql= "TRANSFORM Sum([5s_Audit].Average) AS SumOfAverage "sql = sql & "SELECT Departments.Departments " sql = sql & "FROM Departments INNER JOIN 5s_Audit ON Departments.Departments = [5s_Audit].Department "sql = sql & "GROUP BY Departments.Departments "sql = sql & "PIVOT DatePart('ww',[Due_Date])"set rs = server.createObject("ADODB.RecordSet")rs.Open sql, connection%>Thanks, |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-10 : 17:34:30
|
Are you running the query against a Microsoft Access database or a Microsoft SQL Server?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
gcool12
Starting Member
22 Posts |
Posted - 2007-12-10 : 17:35:42
|
quote:
Originally posted by Peso
Are you running the query against a Microsoft Access database or a Microsoft SQL Server?
E 12°55'05.25"N 56°04'39.16"
The Database is an access database on the server |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-10 : 17:38:19
|
You have to loop every record and put corresponding tags to the html document.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
gcool12
Starting Member
22 Posts |
Posted - 2007-12-10 : 17:40:51
|
quote:
Originally posted by Peso
You have to loop every record and put corresponding tags to the html document.
E 12°55'05.25"N 56°04'39.16"
When I try to get due date and average I get an error:Item cannot be found in the collection corresponding to the requested name or ordinal.but it works for departments:<%=rs("Departments") %> |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-10 : 17:43:15
|
Are you referring to the columns by name or by ordinal?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
gcool12
Starting Member
22 Posts |
Posted - 2007-12-10 : 17:45:23
|
quote:
Originally posted by Peso
Are you referring to the columns by name or by ordinal?
E 12°55'05.25"N 56°04'39.16"
By name I think, what is by ordinal? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-10 : 17:48:56
|
And you know the name is NOT "week 1"? It is "1", "2", "3", "4" etc.By name - rs.Fields("Departments"), rs.Fields("1")By ordinal - rs.Fields(1)
E 12°55'05.25"N 56°04'39.16" |
|
|
|
gcool12
Starting Member
22 Posts |
Posted - 2007-12-10 : 17:52:05
|
quote:
Originally posted by Peso
And you know the name is NOT "week 1"? It is "1", "2", "3", "4" etc.By name - rs.Fields("Departments"), rs.Fields("1")By ordinal - rs.Fields(1)
E 12°55'05.25"N 56°04'39.16"
Yes i was goign to add the "week" but yes I am refering them by name. |
|
|
|
gcool12
Starting Member
22 Posts |
Posted - 2007-12-10 : 18:58:54
|
| Ok new query looks like:<%Dim connection, sql, rsSet connection = Server.CreateObject ("ADODB.Connection")connection.open "DSN=5s"sql= "TRANSFORM Sum([5s_Audit].Average) AS SumOfAverage "sql = sql & "SELECT Department FROM 5s_Audit GROUP BY Department PIVOT DatePart('ww',[Due_Date])"set rs = server.createObject("ADODB.RecordSet")rs.Open sql, connection%>refering to departments works fine but not due_date or average. They are not in the SELECT, when i add them to Select it says i must add them to group by, when i do that it shows the department multiple times, once for each record. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-11 : 04:06:30
|
OMG!There is not "duedate" column anymore! You have PIVOT the data with DATEPART function!The column names are now named by the value that the function return, ie "1", "2" and so on...That's why I asked if you refer to the columns by name or by ordinal...
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
ASP, SQL table help