'Order by' calculated field (Access 2000)

  • Thread starter Thread starter james brown
  • Start date Start date
J

james brown

Hi there.

I have a query with a calculated field that adds the
totals of 4 other fields, no problem so far. I now want to
order the results by the calculated values in this field
but it's not working.

I guess I need to format this field to be a number
(integer? as the number will not exceed 50).

What ever I try, it's not working - here is my calculated
field followed by the SQL for the query.

TotalPoints: ([QPoints]+[IllPoints]+[PSPoints]+[FHPoints])

If anyone could help that'd be great.

Regards, James Brown (full SQL below)

-----

SELECT tblFreelancers.ID, tblFreelancers.Alias,
tblFreelancers.Quark, tblFreelancers.Illustrator,
tblFreelancers.Photoshop, tblFreelancers.Freehand,
tblFreelancers.ArtworkDesign, tblFreelancers.Availability,
IIf([varQuark]<>0,Abs([varQuark]-[Quark]),1000) AS QDiff,
IIf([varIll]<>0,Abs([varIll]-[Illustrator]),1000) AS
IDiff, IIf([varPS]<>0,Abs([varPS]-[Photoshop]),1000) AS
PDiff, IIf([varFH]<>0,Abs([varFH]-[Freehand]),1000) AS
FDiff, IIf([QDiff]=0,15,IIf([QDiff] Between 1 And 2,10,IIf
([QDiff] Between 3 And 4,5,0))) AS QPoints, IIf([IDiff]
=0,15,IIf([IDiff] Between 1 And 2,10,IIf([IDiff] Between 3
And 4,5,0))) AS IllPoints, IIf([PDiff]=0,15,IIf([PDiff]
Between 1 And 2,10,IIf([PDiff] Between 3 And 4,5,0))) AS
PSPoints, IIf([FDiff]=0,15,IIf([FDiff] Between 1 And
2,10,IIf([FDiff] Between 3 And 4,5,0))) AS FHPoints, CInt
([QPoints]+[IllPoints]+[PSPoints]+[FHPoints]) AS
TotalPoints
FROM tblFreelancers
ORDER BY CInt([QPoints]+[IllPoints]+[PSPoints]+[FHPoints]);
 
Hi,


We can't use aliases in the ORDER BY clause, with Jet. A possible solution
is:


SELECT a.*
FROM ( yourQueryWithoutTheOrderBy ) As a
ORDER BY CInt(QPoints+IllPoints+PSPoints+FHPoints)



Hoping it may help,
Vanderghast, Access MVP
 
Thanks Michel, this worked great.

Regards

James.
-----Original Message-----
Hi,


We can't use aliases in the ORDER BY clause, with Jet. A possible solution
is:


SELECT a.*
FROM ( yourQueryWithoutTheOrderBy ) As a
ORDER BY CInt(QPoints+IllPoints+PSPoints+FHPoints)



Hoping it may help,
Vanderghast, Access MVP



Hi there.

I have a query with a calculated field that adds the
totals of 4 other fields, no problem so far. I now want to
order the results by the calculated values in this field
but it's not working.

I guess I need to format this field to be a number
(integer? as the number will not exceed 50).

What ever I try, it's not working - here is my calculated
field followed by the SQL for the query.

TotalPoints: ([QPoints]+[IllPoints]+[PSPoints]+ [FHPoints])

If anyone could help that'd be great.

Regards, James Brown (full SQL below)

-----

SELECT tblFreelancers.ID, tblFreelancers.Alias,
tblFreelancers.Quark, tblFreelancers.Illustrator,
tblFreelancers.Photoshop, tblFreelancers.Freehand,
tblFreelancers.ArtworkDesign, tblFreelancers.Availability,
IIf([varQuark]<>0,Abs([varQuark]-[Quark]),1000) AS QDiff,
IIf([varIll]<>0,Abs([varIll]-[Illustrator]),1000) AS
IDiff, IIf([varPS]<>0,Abs([varPS]-[Photoshop]),1000) AS
PDiff, IIf([varFH]<>0,Abs([varFH]-[Freehand]),1000) AS
FDiff, IIf([QDiff]=0,15,IIf([QDiff] Between 1 And 2,10,IIf
([QDiff] Between 3 And 4,5,0))) AS QPoints, IIf([IDiff]
=0,15,IIf([IDiff] Between 1 And 2,10,IIf([IDiff] Between 3
And 4,5,0))) AS IllPoints, IIf([PDiff]=0,15,IIf([PDiff]
Between 1 And 2,10,IIf([PDiff] Between 3 And 4,5,0))) AS
PSPoints, IIf([FDiff]=0,15,IIf([FDiff] Between 1 And
2,10,IIf([FDiff] Between 3 And 4,5,0))) AS FHPoints, CInt
([QPoints]+[IllPoints]+[PSPoints]+[FHPoints]) AS
TotalPoints
FROM tblFreelancers
ORDER BY CInt([QPoints]+[IllPoints]+[PSPoints]+
Click to expand...
[FHPoints]);


.
Click to expand...
 
Back
Top