sort query by calculated field

  • Thread starter Thread starter Nelly
  • Start date Start date
N

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
 
Nelly said:
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
Click to expand...

Hi Nelly,
This probably won't make you happy, but I just tried it and it worked for me.
Maybe you could post the SQL for your query?
Mike
 
Nelly said:
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
Click to expand...

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
 
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

Nelly said:
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
Click to expand...

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
Click to expand...
 
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.
 
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.
 
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
 
Back
Top