sort query by calculated field

[Nelly]

Please help!

I am trying to sort a query by a field calculated as e.g.
[hoursabsent]/[hoursworked]


I keep getting an error about subqueries not allowed for this type of
expression, but only when I set to sort.


I can sort by another calculated field of, for example just
sum([hoursworked]), but not [hoursabsent]/[hoursworked] and not
[hoursworked] + [hoursabsent]


is there something in SUM() that allows me to sort? Can I enclose
[hoursabsent]/[hoursworked] in a similar function to allow it to sort?


any ideas?


thank you

 

[aaron.kempf]

if you used a real database-- like SQL Server and Access Data
Projects-- then you could sort _ANY_ datasheet with a simple
right-click sort.

Access MDB gets too confused intermittently and it won't let you sort.

Lose the training wheels; MDB is obsolete.. it isn't supported in the
next version of Access; and that's going to be here in the next couple
of weeks

-Aaron

Please help!

I am trying to sort a query by a field calculated as e.g.
[hoursabsent]/[hoursworked]


I keep getting an error about subqueries not allowed for this type of
expression, but only when I set to sort.


I can sort by another calculated field of, for example just
sum([hoursworked]), but not [hoursabsent]/[hoursworked] and not
[hoursworked] + [hoursabsent]


is there something in SUM() that allows me to sort? Can I enclose
[hoursabsent]/[hoursworked] in a similar function to allow it to sort?


any ideas?


thank you

When you want to sort on a calculated value you can usually just put the
calculation you want to sort by in the ORDER BY clause:

SELECT Most_Absent_Employees
FROM TheTable
ORDER BY [hoursabsent]/[hoursworked] DESC

 

[Nelly]

Morning,

Thanks all for your replies. Unfortunately I am stuck with the tools I
am provided with Aaron, so I must solve my problem.

Anyway, here is the SQL:

SELECT tblRegister.Ops AS Operation, Sum(tblRegister.hoursWorked) AS
[Actual Hours Worked],
(Sum(tblssp.hourssick)+Sum(tblssp.hoursel)+Sum(tblssp.hoursawol)+Sum(tblssp.hourscompassionate)+Sum(tblssp.hoursmat)+Sum(tblssp.hourspat)+Sum(tblssp.hourshols)+Sum(tblssp.hourslate)+Sum(tblssp.hoursapproved))+Sum(tblregister.hoursworked)
AS [Possible Hours Worked],
Sum(tblssp.hourssick)+Sum(tblssp.hoursel)+Sum(tblssp.hoursawol) AS
[Hours Absent], [hours absent]/[possible hours worked] AS [Absence
Ratio]
FROM LeagueTable INNER JOIN (tblSupervisor INNER JOIN ((tblEmployee
INNER JOIN tblRegister ON tblEmployee.employeeNo =
tblRegister.employeeNo) LEFT JOIN tblSSP ON tblRegister.registerID =
tblSSP.registerID) ON tblSupervisor.supervisorID =
tblEmployee.supervisorID) ON LeagueTable.ID = tblSupervisor.ID
GROUP BY [hours absent]/[possible hours worked];

I also tried:

GROUP BY
(Sum([tblssp].[hourssick])+Sum([tblssp].[hoursel])+Sum([tblssp].[hoursawol]))/(Sum([tblssp].[hourssick])+Sum([tblssp].[hoursel])+Sum([tblssp].[hoursawol])+Sum([tblssp].[hourscompassionate])+Sum([tblssp].[hoursmat])+Sum([tblssp].[hourspat])+Sum([tblssp].[hourshols])+Sum([tblssp].[hourslate])+Sum([tblssp].[hoursapproved]))+Sum([tblregister].[hoursworked])

in this attempt [absence ratio] was also calculated as above.

many thanks.

 

[Nelly]

Hi all,

I have just managed a workaround. The result of the query was placed
onto a form, where I specifid [absence ratio] in the 'order by'
property of the form, which worked fine.

Still don't know why SQL didn't work, though.

Appreciate all the comments.

thanks.

 

[aaron.kempf]

dude

you're friggin crazy

you HAVE TO USE AN OBSOLETE DATABASE?

I dont believe you.. Access Data Project is _FREE_ and it allows you to
reuse your existing forms and reports.

Most of the simple apps out there I could upgrade in an hour.

a WORKAROUND?

I just find it humorous; that you guys PUT UP with a flaky ass engine
like this.
oh.. is it OK for your database engine to backfire EVERY OTHER QUERY?

with ADP and other enterprise-level databases-- queries don't just crap
out like this
I mean seriously here

a blind and drunk 2nd grader could make better technology decisions
than you and your bosses.

Spit on anyone; anywhere-- that still uses MDB.
MDB isn't supported in Office 2007 and it hasn't EVER been stable
enough for realworld use