Database Discussion - SQL Problem

Print Page | Close Window

SQL Problem

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=3444
Printed Date: 29 March 2026 at 6:28pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: SQL Problem

Posted By: justinp
Subject: SQL Problem
Date Posted: 11 June 2003 at 4:29am

Hi,

I'm using an Access 2002 DB accessed via ASP.

This is my SQL query:
SELECT *, GoalsFor - GoalsAg AS GoalsDif FROM tblTeams ORDER BY TotalPoints DESC, GoalsDif DESC

I'm using an alias(GoalsDif) in the SQL query above to create a sum of two columns in my db table. The problem I'm having is that it wont let me sort the table by the alias GoalDif. It works fine with just TotalPoints DESC but not with GoalsDif DESC.

Can anyone let me know where I'm going wrong please??

Many Thanks

Justin

Replies:

Posted By: michael
Date Posted: 11 June 2003 at 3:34pm

In Access you can only sort on columns that exist not on virtual fields. Your query has a flaw anyway and I am sure will not work as you expect it to.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: justinp
Date Posted: 12 June 2003 at 5:00am

Hi Michael,

Thanks for your reply. I've sorted the problem now. A friend emailed me this the morning:

SELECT tblTeams.*, ([GoalsFor]-[GoalsAg]) AS GoalDif FROM tblTeams ORDER BY tblTeams.TotalPoints DESC , ([GoalsFor]-[GoalsAg]) DESC

It does the trick.

Thanks

Justin