Sorting "Expr1" Fields - hayelp!

[Guest_imported]

apparantly, Access sorts results by the first column requested, yes? Not working for me when I use the first column as another subquery;
select (select sum(field1) from table1 where t1.tid=t2.t1id) as expr1, t2.name, t2.banana from table2 t2
as a very rough example.

is there a simple way of doing this? I've tried adding;
order by expr1 desc
which then prompts me for the value of expr1. If I use;
order by (select sum(field1) from table1 where t1.tid=t2.t1id)
and get a syntax error.

little help? gracias.

 

[JoeMiller]

Are you using the QBE? That's the graphical interface for queries built into access. If you are there is an Sort field that lets you specify how to sort the query results. Access sorts from left to right based on the values in the sort column of the QBE.

HTH Joe Miller
joe.miller@flotech.net

 

[Guest_imported]

either QBE with the dropdown or SQL with the order by clause - still gives the same errors. . .

 

[JoeMiller]

Please post the full SQL code from SQL View of your query, click View-->SQL View and copy/paste to the forum. This should help us give a better answer.

Joe Miller
joe.miller@flotech.net

 

[Guest_imported]

sql;
SELECT (select sum(points) from teamMember tm where tm.teamID = t.teamID) AS points, t.tName, t.teamID, u.login FROM [user] AS u INNER JOIN team AS t ON u.userID = t.userID;

it's for a fantasy footy league table - i.e. I should be sorting by the points column. Since I'm more used to working with SQL Server, I can't understand why I just can't add order by points desc without being prompted to give a value for points . . .

as always, any help is greatly appreciated.

 

[JoeMiller]

You have an extra tm in there, I don't know if it's a field name or part of the table name (I suspect it's part of the table name) or if it shouldn't be there at all. Try these, one should work.

tm is a field:
SELECT (select sum(points) from teamMember.tm where tm.teamID = t.teamID) AS points, t.tName, t.teamID, u.login FROM [user] AS u INNER JOIN team AS t ON u.userID = t.userID;

tm is part of the table:
SELECT (select sum(points) from [teamMember tm] where tm.teamID = t.teamID) AS points, t.tName, t.teamID, u.login FROM [user] AS u INNER JOIN team AS t ON u.userID = t.userID;

tm should be there:
SELECT (select sum(points) from teamMember where tm.teamID = t.teamID) AS points, t.tName, t.teamID, u.login FROM [user] AS u INNER JOIN team AS t ON u.userID = t.userID;
Joe Miller
joe.miller@flotech.net

 

[Guest_imported]

tm is the alias for teamMember. You're quite right though - it's not necessary in that subquery. The problem still exists though. When I use
SELECT (select sum(points) from teamMember where teamID = t.teamID) AS points, t.tName, t.teamID, u.login FROM [user] AS u INNER JOIN team AS t ON u.userID = t.userID where t.status=1 order by (select sum(points) from teamMember where teamID = t.teamID) desc
I get a syntax error. if I use
SELECT (select sum(points) from teamMember where teamID = t.teamID) AS points, t.tName, t.teamID, u.login FROM [user] AS u INNER JOIN team AS t ON u.userID = t.userID where t.status=1 order by points desc
i get a request for the value of points . . .duh . . .
I just want to be able to sort by that subquery. Without the order by it doesn't seem to sort by anything . . grr . . .

 

[Philly44]

Just as and idea try using the OrderBy properties of the form or report that uses this queries results

ie: OrderBy - Points DESC

I don't if this helps

 

[jebry]

Hi!

Have you tried putting your order by clause in the subquery? I haven't tried this, but maybe it will work??

hth
Jeff Bridgham

 

[Guest_imported]

Holy cow - you're a genius Philly!! I've been trying to figure out how to use an Access equivalent of SQL Server Stored Procedures and as well, and this helped me out no end. I saved the query as LeagueTable1 and then called it within an ASP page with the SQL select * from LeagueTable1 order by points desc - perfect. I wish someone had told me you can do that when I started my damned site!! I was too damned used to SQL Server . . jeez.
Well - thanks you guys! If you ever want to see this in action, visit

http://www.jollyboyz.com!

) UK Pubcrawls, UK Pub searching and Fantasy Football (based on UK Carling Premiership). And leave an attempt to be funny on the caption competition!

 

[Philly44]

Glad I could help