How to Sort on a calculated field in a Select Query

[Cottonshirt]

noob alert.



I have an athletics database and I am trying to calculate the age (in decimal years, ex 23.8665) that an athlete won their first medal in international championship.

I have a query that calculates this for me, with the answer in the field [diff(yd)].

when I run the query, if I right-click the [diff(yd)] column I can select <sort Smallest to Largest> from the drop-down list and it does sort correctly. however, if I want to add sort to the query Access prompts me for a parameter [delta], which is itself a calculated field.

the SQL looks like this:
SELECT [tbl_400m.dat] AS dat, [tbl_athlete.fname]+" "+[tbl_athlete.sname]+" ("+[tbl_athlete.natcode]+")" AS name, tbl_athlete.birthdate, DateSerial(Year([tbl_400m.dat]),Month([tbl_athlete.birthdate]),Day([tbl_athlete.birthdate])) AS [ran(b)],
IIf([tbl_400m.dat]-[ran(b)]>0,Year([tbl_400m.dat])-Year([tbl_athlete.birthdate]),Year([tbl_400m.dat])-Year([tbl_athlete.birthdate])-1) AS delta, IIf([tbl_400m.dat]-[ran(b)]>0,[tbl_400m.dat]-[ran(b)],365+[tbl_400m.dat]-[ran(b)]) AS days, ([delta]+([days]/365)) AS [diff(yd)]
FROM tbl_400m INNER JOIN tbl_athlete ON (tbl_400m.sname = tbl_athlete.sname) AND (tbl_400m.fname = tbl_athlete.fname)
WHERE (((tbl_athlete.birthdate) Is Not Null))
ORDER BY ([delta]+([days]/365));


I tried changing the ORDER BY command to read ORDER BY [diff(yd)]; but when I ran this from SQL View Access still prompted me for the parameter diff(yd).

although a noob I have looked online long enough to have learned that if you are being prompted for an inappropriate parameter then there is usually something wrong with the syntax, but I just can't see what I've done wrong.

appreciate any help you can offer.


thank you.

[ranman256]

diff must be a function in Access.
And unless you built it , it does not exist in Access.

[June7]

[diff(yd)] appears to be a fieldname using punctuation/special characters. Advise not to do that with exception of underscore. Better would be diff_yd or DiffYd.

[delta] and [days] are calculated fields. You will have to repeat those calculations in the ORDER BY expression as well.

Alternately, build another query that uses the first one as data source and apply sort in the second query.

Or build a report and use its Sorting & Grouping functionality.

[davegri]

Order By (Year([tbl_400m.dat])-Year([tbl_athlete.birthdate])-1))+(IIf([tbl_400m.dat]-[ran(b)]>0,[tbl_400m.dat]-[ran(b)],365+[tbl_400m.dat]-[ran(b)])  / 365)

As June7 said, you will have to repeat the calculations in the order by clause.
I've never repeated one so complex as this, so good luck!

[Cottonshirt]

thank you very much.

I used the advice from June7 to change field names to include only the underscore (diff(yd) becomes diff_yd, etc) and also the suggestion to build a second query that uses the first as its data source.

for the benefit of anyone else with the same problem, the SQL now looks like this:

SELECT [tbl_400m.dat] AS dat, [tbl_athlete.fname]+" "+[tbl_athlete.sname]+" ("+[tbl_athlete.natcode]+") " AS name, tbl_athlete.birthdate, DateSerial(Year([tbl_400m.dat]),Month([tbl_athlete.birthdate]),Day([tbl_athlete.birthdate])) AS ran_b, IIf([tbl_400m.dat]-[ran_b]>0,Year([tbl_400m.dat])-Year([tbl_athlete.birthdate]),Year([tbl_400m.dat])-Year([tbl_athlete.birthdate])-1) AS delta, IIf([tbl_400m.dat]-[ran_b]>0,[tbl_400m.dat]-[ran_b],365+[tbl_400m.dat]-[ran_b]) AS days, (IIf([tbl_400m.dat]-[ran_b]>0,Year([tbl_400m.dat])-Year([tbl_athlete.birthdate]),Year([tbl_400m.dat])-Year([tbl_athlete.birthdate])-1))+((IIf([tbl_400m.dat]-[ran_b]>0,[tbl_400m.dat]-[ran_b],365+[tbl_400m.dat]-[ran_b]))/365) AS diff_yd, ([perf]*86400) AS [time], Format([delta],"00")+" years, "+Format([days],"0")+" days" AS age_m
FROM (tbl_400m INNER JOIN tbl_athlete ON (tbl_400m.fname = tbl_athlete.fname) AND (tbl_400m.sname = tbl_athlete.sname)) INNER JOIN tbl_series ON tbl_400m.cha = tbl_series.cha
WHERE (((tbl_athlete.birthdate) Is Not Null) AND ((tbl_400m.medal) Is Not Null))
ORDER BY [tbl_athlete.fname]+" "+[tbl_athlete.sname]+" ("+[tbl_athlete.natcode]+") ";

since the second query does the sorting there isn't any need to have this sorted at all, which resolves the complexity suggested by davegri.

and, in case anyone is interested, the youngest medallist in the 400m is Nikolay Chernyetskiy (URS), who took a bronze medal at the European Indoor Championships in Milan, 1978 (46.72) at the age of 18 years 113 days.

thank you all very much for your help.

[Bh89]

Hi All,

I have very minimal experience on Access; and I would like to know how to sort a calculated field in a report.
Coloum name: Weeks since Ref Recvd (which is a calculated field using =DateDiff("ww",[Ref Recvd],Now()).
This is a waiting list I am building, an I need to be able to sort this coloum, which has the arrows greyed out when i try to do this.

I have also tried moving the calculated field to the query I am referencing however it does not seem to update the report when new data is added to the report.

If there is any solution to this I would appreciated advice.

Please let me know if there is any more information you need.

Any help would be appreciated.

Thanks

BH89

[davegri]

BH, instead of tacking on your request to an existing thread, you should start a new thread for your problem.
This thread has been tagged as SOLVED a couple of months ago.

Have you looked at "sorting and grouping" in the report design view on the ribbon?

[June7]

Report in PrintPreview is not dynamic. Have to close and reopen to refresh. Report in ReportView can be refreshed by clicking RefreshAll on ribbon.