I am currently running an online golf tournament on an NT server with an Access database and asp pages. I have access to a Unix server that supports asp script and mySQL database and I'd like to move my tour site (for several reasons) but the SQL statements don't all work. The following statement (in Access) sets the order of the leaderboard based on total and puts in a Pos (position) number for each record. I then loop thru the records and write them to a table on a web page. posSQL = "Select M20.Start_no, M20.Player, M20.Ctry, " _ & "M20.Game_1, M20.Game_2, M20.Game_3, M20.Game_4, " _ & "M20.Total, M20.Par, M20.Money, (SELECT Count(Total) " _ & "FROM M20 B WHERE B.Start_no Like MP% and B.Total < M20.Total)+1 AS Pos " & "FROM M20 WHERE (M20.Start_no Like MP%) and M20.Game_1 > 40 AND " _ & "M20.Game_2 > 40 " _ & "AND M20.Game_3 > 40 AND M20.Game_4 > 40 ORDER BY M20.Total;"
In the mySQL database with the same query I get the following error: ADODB.Recordset.1 error '80004005' SQLState: 42000 Native Error Code: 1064 [TCX][MyODBC]You have an error in your SQL syntax near 'SELECT Count(Total) FROM M20 B WHERE B.Start_no Like 'mp%' and B.Total < M20.Tot' at line 1 Does anyone have any suggestions or even a good sample query page that I might find something? Thanks, Kenny --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php