Link to home
Start Free TrialLog in
Avatar of avgplusguy
avgplusguyFlag for United States of America

asked on

MS Access 2007 Sort Descending Difference between YTD numbers

Surgery Table Fields contain current and previous year data
query is looking for current month to be entered ie 11
This limits both years to january to october
Specialty, Counter Month, Year

The gives me the information I need but not in the order I need
SELECT [Surg CYPY Table].Specialty AS SVC, Sum(IIf([year]=2012,[counter],0)) AS Cases2012, Sum(IIf([year]=2011,[counter],0)) AS Cases2011, [Cases2012]-[Cases2011] AS CaseDif
FROM [Surg CYPY Table]
WHERE ((([type current month ##])>[month]))
GROUP BY [Surg CYPY Table].Specialty;

I need to group by descending CaseDif
I click descending in the sort category and it asks me for [cases2012] and [cases2011]
when I run the query.

If I change the SQL Group by [Surg CYPY Table].Specialty to CaseDif it says
You tried to execute a query that does not include the specified expression 'SVC'
as part of an aggregate function

After I ran the query I clicked on CaseDif and said sort descending.
I save the query and rerun nd it gives me what I want in the order I want it but the SQL code did not change. What happened????
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of avgplusguy

ASKER

This makes more sense now. It works so you will get the points.
hopefully you can answer a slight variation.
I add procedure as a field to see which procedures in the Specialties went up or down.

The last two lines are
GROUP BY IIf([Specialty]="ORTHOPEDIC WITH SURGERY","ORTHOPEDICS",[Specialty]), [Surg CYPY Table].Oper1_Name
ORDER BY IIf([Specialty]="ORTHOPEDIC WITH SURGERY","ORTHOPEDICS",[Specialty]), ([cases2012]-[cases2011]) DESC;

but it does not work.
Avatar of Sean Stuber
Sean Stuber

anything not aggregated in the select must be in the group by

you select
     [Specialty]

but you group by
      IIF([Specialty]....)
Simple quick polite explanations that work. THANK YOU