By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,639 Members | 2,000 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,639 IT Pros & Developers. It's quick & easy.

CrossTab Query: FieldName/Expression not Recognized

P: 22
1. I have a table 'Table1' with the fields (student,subject,score ).
2. I create a CrossTabQuery 'QryXTab' to return students performance per subject plus the total. The subject names now appear as colum names of the query and the totalScores for each subject as well.

The following is the query in access.

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(Table1.score) AS TotalOfScore
  2. SELECT Table1.student, Sum(Table1.score) AS Total_Score
  3. FROM Table1
  4. GROUP BY Table1.student
  5. PIVOT Table1.subject;
  6.  
This query works perfectly.

3. I now want to insert another column of the students position in the class based on the field 'Total_Score' in the X-Tab query. To do this, i create another query that gets the data from the crosstab qry. The following is the new query

Expand|Select|Wrap|Line Numbers
  1. select student, total_score,  (select (count(student) + 1)  as pos from QryXTab where  total_score<a.total_score) from QryXTab a  order by  total_score Desc
But the query does not run as it returns an error " ... does not recognize a.total_score as a valid fieldname or expression ..."

However, if i create a table with the same fields and data as the X-Tab query, the same code works very well. Other simple queries like

Expand|Select|Wrap|Line Numbers
  1. SELECT a.* FROM QryXTab WHERE a.total_score
also work very well.

Any ideas where i could be going wrong?
Aug 9 '07 #1
Share this Question
Share on Google+
2 Replies


ADezii
Expert 5K+
P: 8,633
1. I have a table 'Table1' with the fields (student,subject,score ).
2. I create a CrossTabQuery 'QryXTab' to return students performance per subject plus the total. The subject names now appear as colum names of the query and the totalScores for each subject as well.

The following is the query in access.

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(Table1.score) AS TotalOfScore
  2. SELECT Table1.student, Sum(Table1.score) AS Total_Score
  3. FROM Table1
  4. GROUP BY Table1.student
  5. PIVOT Table1.subject;
  6.  
This query works perfectly.

3. I now want to insert another column of the students position in the class based on the field 'Total_Score' in the X-Tab query. To do this, i create another query that gets the data from the crosstab qry. The following is the new query

Expand|Select|Wrap|Line Numbers
  1. select student, total_score,  (select (count(student) + 1)  as pos from QryXTab where  total_score<a.total_score) from QryXTab a  order by  total_score Desc
But the query does not run as it returns an error " ... does not recognize a.total_score as a valid fieldname or expression ..."

However, if i create a table with the same fields and data as the X-Tab query, the same code works very well. Other simple queries like

Expand|Select|Wrap|Line Numbers
  1. SELECT a.* FROM QryXTab WHERE a.total_score
also work very well.

Any ideas where i could be going wrong?
I didn't get it to work via the SQL Route, but I did create an alternative solution:
  1. Create a Table named tblResults with the following Fields:
    1. Position [LONG]
    2. Student [TEXT]
    3. Total_Score [INTEGER] or [SINGLE]
  2. Execute the following code:
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, intCounter As Long
    2. Dim MyRS_2 As DAO.Recordset, MySQL As String
    3.  
    4. DoCmd.SetWarnings False
    5.   DoCmd.RunSQL "Delete * From tblResults"
    6. DoCmd.SetWarnings True
    7.  
    8. MySQL = "Select * From QryXTab Order By [Total_Score] Desc"
    9.  
    10. Set MyDB = CurrentDb()
    11. Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenDynaset)
    12. Set MyRS_2 = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
    13.  
    14. MyRS.MoveFirst
    15.  
    16. Do While Not MyRS.EOF
    17.   MyRS_2.AddNew
    18.     intCounter = intCounter + 1
    19.       MyRS_2![Position] = intCounter
    20.       MyRS_2![Student] = MyRS![Student]
    21.       MyRS_2![Total_Score] = MyRS![Total_Score]
    22.   MyRS_2.Update
    23.   MyRS.MoveNext
    24. Loop
    25.  
    26. MyRS.Close
    27. MyRS_2.Close
  3. Your proper positions by Rank, Student, and Total Score will now be listed in tblResults.
  4. Let me know how you make out.
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Position    Student              Total_Score
  2. 1            Dezii               272
  3. 2            Stef               252
  4. 3            Charlie           197
  5.  
Aug 26 '07 #2

Jim Doherty
Expert 100+
P: 897
1. I have a table 'Table1' with the fields (student,subject,score ).
2. I create a CrossTabQuery 'QryXTab' to return students performance per subject plus the total. The subject names now appear as colum names of the query and the totalScores for each subject as well.

The following is the query in access.

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(Table1.score) AS TotalOfScore
  2. SELECT Table1.student, Sum(Table1.score) AS Total_Score
  3. FROM Table1
  4. GROUP BY Table1.student
  5. PIVOT Table1.subject;
  6.  
This query works perfectly.

3. I now want to insert another column of the students position in the class based on the field 'Total_Score' in the X-Tab query. To do this, i create another query that gets the data from the crosstab qry. The following is the new query

Expand|Select|Wrap|Line Numbers
  1. select student, total_score,  (select (count(student) + 1)  as pos from QryXTab where  total_score<a.total_score) from QryXTab a  order by  total_score Desc
But the query does not run as it returns an error " ... does not recognize a.total_score as a valid fieldname or expression ..."

However, if i create a table with the same fields and data as the X-Tab query, the same code works very well. Other simple queries like

Expand|Select|Wrap|Line Numbers
  1. SELECT a.* FROM QryXTab WHERE a.total_score
also work very well.

Any ideas where i could be going wrong?

This is using the SQL Route with a proviso..... when you open this SQL in the query design Grid just dump the SUBJECTS that you see visible in the field listings into the grid also and hit the run button. I don't know which subjects you want to group by

IF you do not include the subjects listed then you will only see
Student name total_score and ranking as output columns

IF you do include the subjects then you see the above PLUS the subjects

SELECT a1.Student, a1.Total_Score, Count(a2.Total_Score) AS Ranking
FROM qryXTab AS a1, qryXTab AS a2
WHERE (((a1.Total_Score)<=[a2].[total_score])) OR (((a1.Total_Score)=[a2].[total_score]) AND ((a1.Student)=[a2].[Student]))
GROUP BY a1.Student, a1.Total_Score
ORDER BY a1.Total_Score DESC;


Hope this is of help to you

Jim
Aug 26 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.