Sorting on calculated field (MSAccess 2007) 2

[JITA]

I am trying to sort a calculated field in a query (ascending order). The field involved is calculated from three field that all give back a sorting order on fields from the table (with thanks to you already).

My SQL statement is as follows

SELECT tblCl.ClNaam, tblVerm.Verm, tblVerm.Rev, tblVerm.Fet, Val(DCount("*","tblVerm","[Verm]>" & [Verm])+1) AS Rank1, Val(DCount("*","tblVerm","[Rev]>" & [Rev])+1) AS Rank2, Val(DCount("*","tblVerm","[Fet]>" & [Fet])+1) AS Rank3, [Rank1]+[Rank2]+[Rank3] AS TotalRank
FROM tblCl INNER JOIN tblVerm ON tblCl.ClNR = tblVerm.ClNR
ORDER BY [TotalRank];

Runnig this query gives a table that seems to be in random order. Apart from that it always shows a "Enter Parameter Value" dialog box for the TotalRank field.

Could somebody tell me what I do wrong?

 

[TheAceMan1]

How are ya JITA . . .

As a quick guess try the following:

[blue]SELECT tblCl.ClNaam, tblVerm.Verm, tblVerm.Rev, tblVerm.Fet, DCount("*","tblVerm","[Verm]>[purple][tblC1][/purple].[Verm])+1 AS Rank1, DCount("*","tblVerm","[Rev]>[purple][tblC1][/purple].[Rev])+1 AS Rank2, DCount("*","tblVerm","[Fet]>[purple][tblC1][/purple].[Fet])+1 AS Rank3, [Rank1]+[Rank2]+[Rank3] AS TotalRank FROM tblCl INNER JOIN tblVerm ON tblCl.ClNR = tblVerm.ClNR ORDER BY [purple]([Rank1]+[Rank2]+[Rank3])[/purple];[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[JITA]

Sorry. Does not work.
I learned today from a colleague that I should use my query as input for another query. With that second query I should be able to sort on the calculated data. I will try this tonight
Thanks for your time

 

[dhookom]

SELECT tblCl.ClNaam, tblVerm.Verm, tblVerm.Rev, tblVerm.Fet, Val(DCount("*","tblVerm","[Verm]>" & [Verm])+1) AS Rank1, Val(DCount("*","tblVerm","[Rev]>" & [Rev])+1) AS Rank2, Val(DCount("*","tblVerm","[Fet]>" & [Fet])+1) AS Rank3, [Rank1]+[Rank2]+[Rank3] AS TotalRank
FROM tblCl INNER JOIN tblVerm ON tblCl.ClNR = tblVerm.ClNR
ORDER BY Val(DCount("*","tblVerm","[Verm]>" & [Verm])+1) + Val(DCount("*","tblVerm","[Rev]>" & [Rev])+1) + Val(DCount("*","tblVerm","[Fet]>" & [Fet])+1);

Duane
Hook'D on Access
MS Access MVP

 

[RoyVidar]

Due to the order in which the clauses of an SQL statement is eveluated and excecuted, you cannot ORDER BY columns calculated in the SELECT clause by alias. You can, as demonstrated by dhookom, repeat the "whole enchilada" in the ORDER BY clause. Another method, is to ORDER BY the ordinal position of the column in question, i e

[tt]SELECT tblCl.ClNaam, tblVerm.Verm, tblVerm.Rev, tblVerm.Fet, Val(DCount("*","tblVerm","[Verm]>" & [Verm])+1) AS Rank1, Val(DCount("*","tblVerm","[Rev]>" & [Rev])+1) AS Rank2, Val(DCount("*","tblVerm","[Fet]>" & [Fet])+1) AS Rank3, [Rank1]+[Rank2]+[Rank3] AS TotalRank
FROM tblCl INNER JOIN tblVerm ON tblCl.ClNR = tblVerm.ClNR
ORDER BY 8[/tt]

Roy-Vidar

 

[JITA]

Thanks dhookom and RoyVidar.
Both solutions worked perfectly.
Saves me a lot of time.