SQL Subquery and sort

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

This is a bit weird - I can get the expression working in Access but as soon
as I try and sort by the totalPointScore (using query design) - Access
complains!

I have THE SQL Expression:

SELECT tblPlayers.playerID, tblPlayers.surname, tblPlayers.forename,
tblPlayers.position, tblPlayers.Value, tblClubs.clubName,
tblPlayerScores.playerScore, (SELECT SUM(t1.playerScore) FROM tblPlayerScores
AS t1 WHERE t1.playerID = tblPlayers.playerID AND t1.weekID <3) AS
totalPointScore
FROM tblAdminSetup, (tblClubs INNER JOIN tblPlayers ON tblClubs.clubID =
tblPlayers.clubID) INNER JOIN tblPlayerScores ON tblPlayers.playerID =
tblPlayerScores.playerID
WHERE (((tblPlayerScores.weekID)<=[tblAdminSetup].[tblCurrentWeek]))
;


I would like to sort the totalPointScore, if possible. Any advice is much
appreciated!!!

Thanks in advance,

David Apthorpe
 
Hi David,

I'm pretty sure that the problem is that Access will not accept the field
alias in the Order By. Instead, you will need to repeat the subquery in the
Order By clause, such as:

ORDER BY (SELECT SUM(t1.playerScore) FROM tblPlayerScores AS t1 WHERE
t1.playerID = tblPlayers.playerID AND t1.weekID <3)

Unfortunately, I don't have documentation on when you are or aren't allowed
to use the column alias's in the WHERE or ORDER BY clause, but it seems that
they won't usually work for calculated fields and subqueries. Maybe one of
the MVP's will post a more definite answer.

HTH, Ted Allen
 
Hi Ted,

Thank you for your quick response!!

Unfortunately, when using that as the order by, access responds that there
is a syntax error, yet I am led to believe that this is the correct way of
doing that? (I'm still researching).

Thanks for your reply, and any further advice is much appreciated.

David Apthorpe

Ted Allen said:
Hi David,

I'm pretty sure that the problem is that Access will not accept the field
alias in the Order By. Instead, you will need to repeat the subquery in the
Order By clause, such as:

ORDER BY (SELECT SUM(t1.playerScore) FROM tblPlayerScores AS t1 WHERE
t1.playerID = tblPlayers.playerID AND t1.weekID <3)

Unfortunately, I don't have documentation on when you are or aren't allowed
to use the column alias's in the WHERE or ORDER BY clause, but it seems that
they won't usually work for calculated fields and subqueries. Maybe one of
the MVP's will post a more definite answer.

HTH, Ted Allen


David Apthopre said:
Hi,

This is a bit weird - I can get the expression working in Access but as soon
as I try and sort by the totalPointScore (using query design) - Access
complains!

I have THE SQL Expression:

SELECT tblPlayers.playerID, tblPlayers.surname, tblPlayers.forename,
tblPlayers.position, tblPlayers.Value, tblClubs.clubName,
tblPlayerScores.playerScore, (SELECT SUM(t1.playerScore) FROM tblPlayerScores
AS t1 WHERE t1.playerID = tblPlayers.playerID AND t1.weekID <3) AS
totalPointScore
FROM tblAdminSetup, (tblClubs INNER JOIN tblPlayers ON tblClubs.clubID =
tblPlayers.clubID) INNER JOIN tblPlayerScores ON tblPlayers.playerID =
tblPlayerScores.playerID
WHERE (((tblPlayerScores.weekID)<=[tblAdminSetup].[tblCurrentWeek]))
;


I would like to sort the totalPointScore, if possible. Any advice is much
appreciated!!!

Thanks in advance,

David Apthorpe
Click to expand...
Click to expand...
 
Hmm, now that I thnk about it, Jet may not allow a subquery in the ORDER BY.
If not, you may have to replace the subquery with DSUM(), which may be very
slow depending on your number of records, or you could create a second query
based on the first and order by the desired field. I'll try a google search
to see if I can find out anything more helpful.

David Apthorpe said:
Hi Ted,

Thank you for your quick response!!

Unfortunately, when using that as the order by, access responds that there
is a syntax error, yet I am led to believe that this is the correct way of
doing that? (I'm still researching).

Thanks for your reply, and any further advice is much appreciated.

David Apthorpe

Ted Allen said:
Hi David,

I'm pretty sure that the problem is that Access will not accept the field
alias in the Order By. Instead, you will need to repeat the subquery in the
Order By clause, such as:

ORDER BY (SELECT SUM(t1.playerScore) FROM tblPlayerScores AS t1 WHERE
t1.playerID = tblPlayers.playerID AND t1.weekID <3)

Unfortunately, I don't have documentation on when you are or aren't allowed
to use the column alias's in the WHERE or ORDER BY clause, but it seems that
they won't usually work for calculated fields and subqueries. Maybe one of
the MVP's will post a more definite answer.

HTH, Ted Allen


David Apthopre said:
Hi,

This is a bit weird - I can get the expression working in Access but as soon
as I try and sort by the totalPointScore (using query design) - Access
complains!

I have THE SQL Expression:

SELECT tblPlayers.playerID, tblPlayers.surname, tblPlayers.forename,
tblPlayers.position, tblPlayers.Value, tblClubs.clubName,
tblPlayerScores.playerScore, (SELECT SUM(t1.playerScore) FROM tblPlayerScores
AS t1 WHERE t1.playerID = tblPlayers.playerID AND t1.weekID <3) AS
totalPointScore
FROM tblAdminSetup, (tblClubs INNER JOIN tblPlayers ON tblClubs.clubID =
tblPlayers.clubID) INNER JOIN tblPlayerScores ON tblPlayers.playerID =
tblPlayerScores.playerID
WHERE (((tblPlayerScores.weekID)<=[tblAdminSetup].[tblCurrentWeek]))
;


I would like to sort the totalPointScore, if possible. Any advice is much
appreciated!!!

Thanks in advance,

David Apthorpe
Click to expand...
Click to expand...
Click to expand...
 
I wasn't able to find much. One other thing that you could try is to alias
the table name in the ORDER BY as t2 rather than t1, but I'm thinking that
Jet may not accept the subquery in the ORDER BY clause.

I found a statement in the Jet SQL reference that explicitly states that
subqueries can be used in SELECT or WHERE clauses, but it didn't mention
ORDER BY.

Hopefully someone else will be able to provide a more definitive answer.

-Ted Allen

Ted Allen said:
Hmm, now that I thnk about it, Jet may not allow a subquery in the ORDER BY.
If not, you may have to replace the subquery with DSUM(), which may be very
slow depending on your number of records, or you could create a second query
based on the first and order by the desired field. I'll try a google search
to see if I can find out anything more helpful.

David Apthorpe said:
Hi Ted,

Thank you for your quick response!!

Unfortunately, when using that as the order by, access responds that there
is a syntax error, yet I am led to believe that this is the correct way of
doing that? (I'm still researching).

Thanks for your reply, and any further advice is much appreciated.

David Apthorpe

Ted Allen said:
Hi David,

I'm pretty sure that the problem is that Access will not accept the field
alias in the Order By. Instead, you will need to repeat the subquery in the
Order By clause, such as:

ORDER BY (SELECT SUM(t1.playerScore) FROM tblPlayerScores AS t1 WHERE
t1.playerID = tblPlayers.playerID AND t1.weekID <3)

Unfortunately, I don't have documentation on when you are or aren't allowed
to use the column alias's in the WHERE or ORDER BY clause, but it seems that
they won't usually work for calculated fields and subqueries. Maybe one of
the MVP's will post a more definite answer.

HTH, Ted Allen


:

Hi,

This is a bit weird - I can get the expression working in Access but as soon
as I try and sort by the totalPointScore (using query design) - Access
complains!

I have THE SQL Expression:

SELECT tblPlayers.playerID, tblPlayers.surname, tblPlayers.forename,
tblPlayers.position, tblPlayers.Value, tblClubs.clubName,
tblPlayerScores.playerScore, (SELECT SUM(t1.playerScore) FROM tblPlayerScores
AS t1 WHERE t1.playerID = tblPlayers.playerID AND t1.weekID <3) AS
totalPointScore
FROM tblAdminSetup, (tblClubs INNER JOIN tblPlayers ON tblClubs.clubID =
tblPlayers.clubID) INNER JOIN tblPlayerScores ON tblPlayers.playerID =
tblPlayerScores.playerID
WHERE (((tblPlayerScores.weekID)<=[tblAdminSetup].[tblCurrentWeek]))
;


I would like to sort the totalPointScore, if possible. Any advice is much
appreciated!!!

Thanks in advance,

David Apthorpe
Click to expand...
Click to expand...
Click to expand...
 
Not sure but try:

....
ORDER BY 8

(8 is the Column number from the selection list if I counted correctly).

What concerned me is the Cartesian join between Table tblAdminSetup and the
joined remaining Tables and the WHERE clause:

WHERE (((tblPlayerScores.weekID)<=[tblAdminSetup].[tblCurrentWeek]))

Are you telling us that there is a Field named [tblCurrentWeek] in the Table
[tblAdminSetup]? In that case, the use of the prefix "tbl" in
[tblCurrentWeek] is a bit unconvetional and can be misleading.

Hopefully, you have only minimal number of Records in Table [tblAdminSetup],
e.g. 1 Record in the Table.
 
Thank you for the quick reply - ORDER BY 8 doesn't seem to work (it remains
as though ignored - very strange!)

The tblCurrentWeek is a mistake, I created it by accident and forgot about
it, thanks for reminding me to remove it!

Thanks for all assistance so far,

David Apthorpe

Van T. Dinh said:
Not sure but try:

....
ORDER BY 8

(8 is the Column number from the selection list if I counted correctly).

What concerned me is the Cartesian join between Table tblAdminSetup and the
joined remaining Tables and the WHERE clause:

WHERE (((tblPlayerScores.weekID)<=[tblAdminSetup].[tblCurrentWeek]))

Are you telling us that there is a Field named [tblCurrentWeek] in the Table
[tblAdminSetup]? In that case, the use of the prefix "tbl" in
[tblCurrentWeek] is a bit unconvetional and can be misleading.

Hopefully, you have only minimal number of Records in Table [tblAdminSetup],
e.g. 1 Record in the Table.

--
HTH
Van T. Dinh
MVP (Access)



David Apthorpe said:
Hi Ted,

Thank you for your quick response!!

Unfortunately, when using that as the order by, access responds that there
is a syntax error, yet I am led to believe that this is the correct way of
doing that? (I'm still researching).

Thanks for your reply, and any further advice is much appreciated.

David Apthorpe
Click to expand...
Click to expand...
 
Thank you for your answer,

Unfortunately that did not seem to work! (I wish it did!)

Ted Allen said:
I wasn't able to find much. One other thing that you could try is to alias
the table name in the ORDER BY as t2 rather than t1, but I'm thinking that
Jet may not accept the subquery in the ORDER BY clause.

I found a statement in the Jet SQL reference that explicitly states that
subqueries can be used in SELECT or WHERE clauses, but it didn't mention
ORDER BY.

Hopefully someone else will be able to provide a more definitive answer.

-Ted Allen

Ted Allen said:
Hmm, now that I thnk about it, Jet may not allow a subquery in the ORDER BY.
If not, you may have to replace the subquery with DSUM(), which may be very
slow depending on your number of records, or you could create a second query
based on the first and order by the desired field. I'll try a google search
to see if I can find out anything more helpful.

David Apthorpe said:
Hi Ted,

Thank you for your quick response!!

Unfortunately, when using that as the order by, access responds that there
is a syntax error, yet I am led to believe that this is the correct way of
doing that? (I'm still researching).

Thanks for your reply, and any further advice is much appreciated.

David Apthorpe

:

Hi David,

I'm pretty sure that the problem is that Access will not accept the field
alias in the Order By. Instead, you will need to repeat the subquery in the
Order By clause, such as:

ORDER BY (SELECT SUM(t1.playerScore) FROM tblPlayerScores AS t1 WHERE
t1.playerID = tblPlayers.playerID AND t1.weekID <3)

Unfortunately, I don't have documentation on when you are or aren't allowed
to use the column alias's in the WHERE or ORDER BY clause, but it seems that
they won't usually work for calculated fields and subqueries. Maybe one of
the MVP's will post a more definite answer.

HTH, Ted Allen


:

Hi,

This is a bit weird - I can get the expression working in Access but as soon
as I try and sort by the totalPointScore (using query design) - Access
complains!

I have THE SQL Expression:

SELECT tblPlayers.playerID, tblPlayers.surname, tblPlayers.forename,
tblPlayers.position, tblPlayers.Value, tblClubs.clubName,
tblPlayerScores.playerScore, (SELECT SUM(t1.playerScore) FROM tblPlayerScores
AS t1 WHERE t1.playerID = tblPlayers.playerID AND t1.weekID <3) AS
totalPointScore
FROM tblAdminSetup, (tblClubs INNER JOIN tblPlayers ON tblClubs.clubID =
tblPlayers.clubID) INNER JOIN tblPlayerScores ON tblPlayers.playerID =
tblPlayerScores.playerID
WHERE (((tblPlayerScores.weekID)<=[tblAdminSetup].[tblCurrentWeek]))
;


I would like to sort the totalPointScore, if possible. Any advice is much
appreciated!!!

Thanks in advance,

David Apthorpe
Click to expand...
Click to expand...
Click to expand...
 
OK. Try creating a new Query using the existing Query as the DataSource
with

SELECT *
FROM [YourQuery]
ORDER BY TotalPointScore

Alternatively, you can try using the existing SQL as the SubQuery, e.g.

SELECT *
FROM ( {The modified posted SQL here} )
ORDER BY TotalPointScore
 
Brilliant!!

Worked first time - thank you ever so much!!!

Van T. Dinh said:
OK. Try creating a new Query using the existing Query as the DataSource
with

SELECT *
FROM [YourQuery]
ORDER BY TotalPointScore

Alternatively, you can try using the existing SQL as the SubQuery, e.g.

SELECT *
FROM ( {The modified posted SQL here} )
ORDER BY TotalPointScore

--
HTH
Van T. Dinh
MVP (Access)


David Apthorpe said:
Thank you for the quick reply - ORDER BY 8 doesn't seem to work (it remains
as though ignored - very strange!)

The tblCurrentWeek is a mistake, I created it by accident and forgot about
it, thanks for reminding me to remove it!

Thanks for all assistance so far,

David Apthorpe
Click to expand...
Click to expand...
 
Too bad. Did you ever try using DSum(). I'm pretty sure that should work,
it just may be a little slow.

David Apthorpe said:
Thank you for your answer,

Unfortunately that did not seem to work! (I wish it did!)

Ted Allen said:
I wasn't able to find much. One other thing that you could try is to alias
the table name in the ORDER BY as t2 rather than t1, but I'm thinking that
Jet may not accept the subquery in the ORDER BY clause.

I found a statement in the Jet SQL reference that explicitly states that
subqueries can be used in SELECT or WHERE clauses, but it didn't mention
ORDER BY.

Hopefully someone else will be able to provide a more definitive answer.

-Ted Allen

Ted Allen said:
Hmm, now that I thnk about it, Jet may not allow a subquery in the ORDER BY.
If not, you may have to replace the subquery with DSUM(), which may be very
slow depending on your number of records, or you could create a second query
based on the first and order by the desired field. I'll try a google search
to see if I can find out anything more helpful.

:

Hi Ted,

Thank you for your quick response!!

Unfortunately, when using that as the order by, access responds that there
is a syntax error, yet I am led to believe that this is the correct way of
doing that? (I'm still researching).

Thanks for your reply, and any further advice is much appreciated.

David Apthorpe

:

Hi David,

I'm pretty sure that the problem is that Access will not accept the field
alias in the Order By. Instead, you will need to repeat the subquery in the
Order By clause, such as:

ORDER BY (SELECT SUM(t1.playerScore) FROM tblPlayerScores AS t1 WHERE
t1.playerID = tblPlayers.playerID AND t1.weekID <3)

Unfortunately, I don't have documentation on when you are or aren't allowed
to use the column alias's in the WHERE or ORDER BY clause, but it seems that
they won't usually work for calculated fields and subqueries. Maybe one of
the MVP's will post a more definite answer.

HTH, Ted Allen


:

Hi,

This is a bit weird - I can get the expression working in Access but as soon
as I try and sort by the totalPointScore (using query design) - Access
complains!

I have THE SQL Expression:

SELECT tblPlayers.playerID, tblPlayers.surname, tblPlayers.forename,
tblPlayers.position, tblPlayers.Value, tblClubs.clubName,
tblPlayerScores.playerScore, (SELECT SUM(t1.playerScore) FROM tblPlayerScores
AS t1 WHERE t1.playerID = tblPlayers.playerID AND t1.weekID <3) AS
totalPointScore
FROM tblAdminSetup, (tblClubs INNER JOIN tblPlayers ON tblClubs.clubID =
tblPlayers.clubID) INNER JOIN tblPlayerScores ON tblPlayers.playerID =
tblPlayerScores.playerID
WHERE (((tblPlayerScores.weekID)<=[tblAdminSetup].[tblCurrentWeek]))
;


I would like to sort the totalPointScore, if possible. Any advice is much
appreciated!!!

Thanks in advance,

David Apthorpe
Click to expand...
Click to expand...
Click to expand...
 
Oops, just saw your post from yesterday that you got it to work using Van's
suggestion. Very good idea Van. I realized after I saw your post that I had
used that method before once on a SQL Server pass-thru as well, but had
forgotten about it.

Ted Allen said:
Too bad. Did you ever try using DSum(). I'm pretty sure that should work,
it just may be a little slow.

David Apthorpe said:
Thank you for your answer,

Unfortunately that did not seem to work! (I wish it did!)

Ted Allen said:
I wasn't able to find much. One other thing that you could try is to alias
the table name in the ORDER BY as t2 rather than t1, but I'm thinking that
Jet may not accept the subquery in the ORDER BY clause.

I found a statement in the Jet SQL reference that explicitly states that
subqueries can be used in SELECT or WHERE clauses, but it didn't mention
ORDER BY.

Hopefully someone else will be able to provide a more definitive answer.

-Ted Allen

:

Hmm, now that I thnk about it, Jet may not allow a subquery in the ORDER BY.
If not, you may have to replace the subquery with DSUM(), which may be very
slow depending on your number of records, or you could create a second query
based on the first and order by the desired field. I'll try a google search
to see if I can find out anything more helpful.

:

Hi Ted,

Thank you for your quick response!!

Unfortunately, when using that as the order by, access responds that there
is a syntax error, yet I am led to believe that this is the correct way of
doing that? (I'm still researching).

Thanks for your reply, and any further advice is much appreciated.

David Apthorpe

:

Hi David,

I'm pretty sure that the problem is that Access will not accept the field
alias in the Order By. Instead, you will need to repeat the subquery in the
Order By clause, such as:

ORDER BY (SELECT SUM(t1.playerScore) FROM tblPlayerScores AS t1 WHERE
t1.playerID = tblPlayers.playerID AND t1.weekID <3)

Unfortunately, I don't have documentation on when you are or aren't allowed
to use the column alias's in the WHERE or ORDER BY clause, but it seems that
they won't usually work for calculated fields and subqueries. Maybe one of
the MVP's will post a more definite answer.

HTH, Ted Allen


:

Hi,

This is a bit weird - I can get the expression working in Access but as soon
as I try and sort by the totalPointScore (using query design) - Access
complains!

I have THE SQL Expression:

SELECT tblPlayers.playerID, tblPlayers.surname, tblPlayers.forename,
tblPlayers.position, tblPlayers.Value, tblClubs.clubName,
tblPlayerScores.playerScore, (SELECT SUM(t1.playerScore) FROM tblPlayerScores
AS t1 WHERE t1.playerID = tblPlayers.playerID AND t1.weekID <3) AS
totalPointScore
FROM tblAdminSetup, (tblClubs INNER JOIN tblPlayers ON tblClubs.clubID =
tblPlayers.clubID) INNER JOIN tblPlayerScores ON tblPlayers.playerID =
tblPlayerScores.playerID
WHERE (((tblPlayerScores.weekID)<=[tblAdminSetup].[tblCurrentWeek]))
;


I would like to sort the totalPointScore, if possible. Any advice is much
appreciated!!!

Thanks in advance,

David Apthorpe
Click to expand...
Click to expand...
Click to expand...
 
Back
Top