Filter report based on SUM value in box

[sashless]

Hello guys,

Can you please help me with one problem, i'm bothering with it whole day... I have a report based query tblData, each person have more than 1 project and in the end i SUM value in box SUM(porabljen_čas) that mean used_time in english, so what i need to see is, when i open the REPORT i want to see only records/rows where a person didn't complieted 100% (less or more), every record with value different than 100%.

Here is the pic:



Kind regards, Saso

[June7]

Yes, can filter on calculated field. Put on the criteria row under that field: <> 100

[sashless]

Hello,


It writes me:

Cannot have aggregate function in WHERE clause <clause>. (Error 3096)

You cannot use an aggregate function in the WHERE clause of an SQL statement. Is it any other option?

Kind regards, Saso

[June7]

Should be HAVING clause. Did you use design grid of query builder? Works for me.

[sashless]

I have like this and it's not working :/

[June7]

What language is that? What does Vsota mean?

[sashless]

Vsota means SUM, that's Slovenian languege I'm sorry for that...

And that is SQL code:

SELECT tblPodatki.podatkiID, tblPodatki.Oddelek, tblPodatki.[Ime in priimekID], tblPodatki.AktivnostID, tblPodatki.[Porabljen čas], tblPodatki.ZvrstProjekta, tblPodatki.Nalog, Sum([Porabljen čas]) AS Izr1
FROM tblPodatki
WHERE (((tblPodatki.Oddelek) Is Not Null) AND ((tblPodatki.AktivnostID) Is Not Null) AND ((Sum([Porabljen čas]))<>100));

Any idea? ://

[June7]

That query should not work even without the <>100. Aggregate functions (Sum, Avg, Count, etc) must be used in a Totals (GROUP BY) query. I don't understand why you are using Sum. Why not just:

AND [Porabljen čas]<>100

[sashless]

Because every person have like this: work project 1 = 10%, project 2= 40%, project 3= 50%... then this is sum 100%, and in my report i have to show who didn't completed 100% (less or more)

[June7]

Saso has 3 items listed that total 100% so Saso should not be on the report?

Sabina shows 275% (very busy!!) and should also not be listed?

That leaves only Marta with less than 100%?

Try:

SELECT *
FROM tblPodatki
WHERE Oddelek Is Not Null AND AktivnostID Is Not Null AND PodatkiID IN (SELECT PodatkiID GROUP BY PodatkiID HAVING Sum([Porabljen čas])<>100);

[sashless]

Just Saso should not be listed cause only he completed the 100% demanded result, what's more or less must be on report.

It won't save, it just colors this: (SELECT PodatkiID GROUP BY PodatkiID HAVING Sum([Porabljen čas])<>100);

[June7]

I forgot FROM tblPodatki in the subquery:

SELECT *
FROM tblPodatki
WHERE Oddelek Is Not Null AND AktivnostID Is Not Null AND PodatkiID IN (SELECT PodatkiID FROM tblPodatki GROUP BY PodatkiID HAVING Sum([Porabljen čas])<>100);

[sashless]

Nothing... Still shows all 3 records...

[June7]

The technique works, I tested it. Have to get the correct field referenced.

Is PodatkiID an autonumber field? Which field is the ID for people?

[sashless]

I UPLOADED IT HERE:

http://www.sendspace.com/file/kl6u2r

REPORT name is NEPOPOLNI_VPIS, query for this report is nepopolni_VPIS.

You can check, i cound't manage it work