Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...keep up the good work with this forum, I think this is the best one around. ...you actually try to help people learn for themselves. ...I commend you on providing a very good, open learning atmosphere, where usually egos are left behind..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft: Access Queries and JET SQL Forum

duplicate query: expression not part of statistical function

thread701-1681039
Read More Threads Like This One
easyit (Programmer)
20 Apr 12 7:13
Hi,


I have a query the results in a calculated column with percentages. If there are duplicates in this column I need to identify them. So I tried a duplicate query which results in the mentioned error. If I use the duplicate criterie query it runs fine. I don't know what is the problem. The sql:

CODE

SELECT [TW2013_2B-b_1].[100%], [TW2013_2B-b_1].[fk_praktijkinstelling], [TW2013_2B-b_1].[Naam]
FROM [TW2013_2B-b_1]
WHERE ((([TW2013_2B-b_1].[100%]) In (SELECT Tmp.[100%] FROM [TW2013_2B-b_1] AS Tmp GROUP BY Tmp.[100%] HAVING (((Count(*))>1)))))
ORDER BY [TW2013_2B-b_1].[100%];

the underlying query:

CODE

SELECT TW2013_bestaande_pi_behoefte.fk_praktijkinstelling, TW2013_bestaande_pi_behoefte.Naam, TW2013_bestaande_pi_behoefte.[%] AS behoefte, TW2013_bestaande_pi_resultaat.[%] AS historisch, TW2013_bestaande_pi_behoefte.[sector opl], IIf([historisch]<[behoefte],[historisch],[behoefte]) AS laagste, [TW2013_2B-b_1_totalen_percentages].totaal, ([laagste])/[totaal] AS [100%]
FROM [TW2013_2B-b_1_totalen_percentages] INNER JOIN (TW2013_bestaande_pi_resultaat INNER JOIN TW2013_bestaande_pi_behoefte ON (TW2013_bestaande_pi_behoefte.[sector opl] = TW2013_bestaande_pi_resultaat.[Sector-OPl]) AND (TW2013_bestaande_pi_resultaat.Naam = TW2013_bestaande_pi_behoefte.Naam)) ON [TW2013_2B-b_1_totalen_percentages].[sector opl] = TW2013_bestaande_pi_resultaat.[Sector-OPl];

 

EasyIT

"Do you think that's air you're breathing?"

easyit (Programmer)
20 Apr 12 7:55
well, sort of solved it myself;

in the query before the underlying query there was a group by that should have been a where. It eliminated 5 unwanted results AND solved the problem.

Happy to move forward - puzzled about the why....

 

EasyIT

"Do you think that's air you're breathing?"

Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Queries and JET SQL

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close