As part of a query I have the following (needs to be Access 2003 sql compatible, I'm sill wearing the learner badge!) ...
Minimal Code:
However, I have a problem when I try to include Order By 5 Asc in that it doesn't work. I'm guessing this has something to do with the 2 previous subqueries (then AStrTotal adds them together).
Table Main (primary database to be searched) comprises 2 fields Ax and Ay...
Ax Ay
1 6
5 9
3 3
7
5 5
7 2
2
4 4
3
6 5
7 6
etc.... Blank entries above are simply null values. Blanks, Ax and Ay values can appear in either field.
Table Afull comprises 2 fields called Avalid and Astr...
Avalid AStr
1
2
3
4
5
6
7
8
9
Field Astr is initialised to Null at the start of each run. The first use of this table is to store all valid values for Ax and Ay in field Avalid. The second use is to allow for the selection, by the user, of search critera. To do this, table Afull is added as a subform in the user search form. The user then selects an Avalid value to search for by inputting any value >0 into Astr - next to the value to be searched. Later, the Astr value (variable number indicating strength) will be used in ORDER BY
An sql query string is then built up whose purpose is to return all records carrying any 'permutation' of user-selected Avalid values - I've not included this restriction in the minimal examples here...I'm trying to get the ORDER BY working first.
I've now reduced the original query to...
Minimal code:
Then adding...
But I get a syntax error in ORDER BY. Removing the brackets doesn't help. ORDER BY 3 ASC compiles but doesn't work.
Is there any get the order by working properly.
Minimal Code:
Code:
SELECT Main.Ax,Main.Ay,
(select Astr from Afull where Avalid = Main.Ax) as AxStr,
(select Astr from Afull where Avalid = Main.Ay) as AyStr,
Switch(AxStr>=0, AxStr,AxStr<0,0,isnull(AxStr),0)
+ Switch(AyStr>=0, AyStr,AyStr<0,0,isnull(AyStr),0) as AStrTotal
FROM Main
Table Main (primary database to be searched) comprises 2 fields Ax and Ay...
Ax Ay
1 6
5 9
3 3
7
5 5
7 2
2
4 4
3
6 5
7 6
etc.... Blank entries above are simply null values. Blanks, Ax and Ay values can appear in either field.
Table Afull comprises 2 fields called Avalid and Astr...
Avalid AStr
1
2
3
4
5
6
7
8
9
Field Astr is initialised to Null at the start of each run. The first use of this table is to store all valid values for Ax and Ay in field Avalid. The second use is to allow for the selection, by the user, of search critera. To do this, table Afull is added as a subform in the user search form. The user then selects an Avalid value to search for by inputting any value >0 into Astr - next to the value to be searched. Later, the Astr value (variable number indicating strength) will be used in ORDER BY
An sql query string is then built up whose purpose is to return all records carrying any 'permutation' of user-selected Avalid values - I've not included this restriction in the minimal examples here...I'm trying to get the ORDER BY working first.
I've now reduced the original query to...
Minimal code:
Code:
SELECT Main.Ax,Main.Ay,
(select Astr from Afull where Avalid = Main.Ax) as AxStr
FROM Main
Code:
ORDER BY (select Astr from Afull where Avalid = Main.Ax) ASC;
Is there any get the order by working properly.