help needed with Nz() and vba coding (1 Viewer)

[Misiek]

ok,
thats my latest code:

TRANSFORM nz(count(T_qa.qaQAPK),0) AS SumOfQuantity
SELECT Count(T_qa.qaQAPK) AS QAs, count(T_qa.qaQualityIncidentNo) AS QIs, Sum(qaSeverity * (-1)) AS Sev10, Sum(qaFrozen * (-1)) AS Frzn
FROM Q_ALL_qa
GROUP BY Month(qaDate)
PIVOT month([qaDate])
IN (1,2,3,4,5,6,7,8,9,10,11,12);

Please see attached photo, this is my output from the query where I transform "QAs" results into months,
the question is:

Is it possible to add another 12 columns after those I already have with different results? Or do I need a separate query for this.

Reason behind it: I want it to be fast, so If 1 query does all calculation then my report would load quickly

Thank you

 

[namliam]

Be carefull with your query you are counting all january records this will cause issues over the years....

What 12 columns do you want to add? and Why, functionally?

 

[Misiek]

it won't be a problem, as I will always have records for 1 year only, so its ok to sort it by months excluding years.

In one of the first post you have explained to me, I can have all this data from 1 query and it would be x-1 times faster

Thats why If possible i would like to have all calculation in 1 query, spread over months.
The more I think about it and search on the web, the less possible it becomes...(in my head)

If you look at the attached file, its my report layout, and there are QAs total and then spreads into months, then there are QIs total and spreads into months etc...

What do you think? Am I causing too much trouble, shall I just create a separate query for each one of them?

Thank you

 

[Misiek]

And also,

how can I make calculation inside the query statement?
as it gives me an error if i try divide one field by another.

SELECT month(qaDate) as month, Count(tTaskPK) as sumOFtask, count(tTaskDueDate) as dueD, count(tTaskComplDate) as ComplD, sum(iif(tTaskDueDate>=tTaskComplDate,1,0)) as DueOFComp
FROM Q_ALL_task
GROUP BY month(qaDate)

trying to sumOFtask / DueOFComp

Thank you

 

[Frothingslosh]

If you're trying to run a calculation based on the results of two calculated fields, you'll need to derive those calculated fields in a subquery. (I normally refer to them as 'stacked' queries, myself, to differentiate them from normal subqueries, which are full queries written as an integral part of ANOTHER query, using words like IN.)

Basically, start with the query you already have. Then make another query, using the first as the record source, that includes all the included fields plus the one with sumOFTask/DueOFComp.

As a rule, you cannot calculate fields and then use those fields in another calculation in the same query.

 

[Misiek]

Been trying to find any hints on the web, but all examples include JOIN, in my situation, all data comes form one table/query, therefore i couldn't understand how to do it properly.

Would you mind to create a sample query for me, and then I will try to take if from there. I am not sure in what order I need to build this statement with IN, and what fields to put into each of them.

Thank you

 

[Frothingslosh]

No, you don't need a join for what I'm talking about.

Create a new query in the query builder, but instead of using a table as your record source, select 'queries' and then use the query you listed in post 44 as your record source. Drag in all the fields from the source query, then create the one you are trying to calculate - "sumOFtask / DueOFComp".

 

[namliam]

In one of the first post you have explained to me, I can have all this data from 1 query and it would be x-1 times faster

vs many Dsomethings, yes, and yes vs many queries as well but there are some limitations that just cannot work...

Looking at this it looks to me like you want 3 records of 12 columns, not 1 record of 36 columns...
This is why I ask you again, what is it you are trying to do/achieve.
Perhaps post some mockup data in a database or excel spreadsheet?

SELECT month(qaDate) as month, Count(tTaskPK) as sumOFtask, count(tTaskDueDate) as dueD, count(tTaskComplDate) as ComplD, sum(iif(tTaskDueDate>=tTaskComplDate,1,0)) as DueOFComp
FROM Q_ALL_task
GROUP BY month(qaDate)

trying to sumOFtask / DueOFComp

The issue here is that sumoftask (which is actually countoftask but details, though details do matter) and dueofcomp dont really exist at the time you run the query. So if you try to use them in a calculation while running the query access wont find the columns. ONLY after completing the query do those columns (and therefor) values exist and can you use them to calculate with.

You can do one of two things.
query stacking
As per Slosh, lets store above query as qryStart
Now you make a new query

Select qryStart.*, sumOFtask / DueOFComp
from qryStart

Save this as qryStacked, now you can ignore qryStart and simply run qryStacked, it will execute both the queries in one go.

recalculate
You can do the division but you have to do it with the source of your columns, i.e. not the columname but the count/sum or whatever statement you are using at the time.

SELECT month(qaDate) as month, Count(tTaskPK) as sumOFtask, count(tTaskDueDate) as dueD, count(tTaskComplDate) as ComplD, sum(iif(tTaskDueDate>=tTaskComplDate,1,0)) as DueOFComp
[U], Count(tTaskPK) / sum(iif(tTaskDueDate>=tTaskComplDate,1,0)) CalculatedField[/U]FROM Q_ALL_task
GROUP BY month(qaDate)

This offcourse requires a little overhead, but is not noticable really and keeps your database clean of too many stacked queries.

 

[Misiek]

the stacking query seems to be easier to do, but i will end up with lots of "little" queries..

I tried the second option, to recalculate,

SELECT month(qaDate) AS [month], Count(tTaskPK) AS totalTASKS, count(tTaskDueDate) AS haveDUEdate, count(tTaskComplDate) AS haveCOMPLdate, sum(iif(tTaskDueDate>=tTaskComplDate,1,0)) AS taskONtime, Count((sum(iif(tTaskDueDate>=tTaskComplDate,1,0))) / (tTaskPK)) AS percentage
FROM Q_ALL_task
GROUP BY month(qaDate);

and that gives me results, but then the field I'm looking for (which you called: calculated field) gives a fault value then i noticed the fields have to be other way around. but then it gives me this error:

cannot have aggregate function in expression Count((sum(iif(tTaskDueDate>=tTaskComplDate,1,0))) / (tTaskPK))

I dont understand what this mean. Can you advice please.

 

[namliam]

Count(sum()) what??

You can only use one aggregate function

It is quite easy, simply think you have the two fields already and stick in the appropriat functions instead

Select count(a) as ca, count(B) as cb

So instead of ca / cb, you use count(a) / count(B)

 

[Misiek]

This works like a dream Thank you

Im going to have to create probably 6 queries that calculate all my fields. I would like to have all this final calculation shown in one report, Is it possible to have a multiple record source for one report (based on all those queries?
Or do I need to create a report for each of them and then have 6 reports showing on the screen.

Thank you

 

[namliam]

Like you can nest querys and forms , you can also nest report if required

 

[Misiek]

Can I nest a transformed query inside another transformed query?

Q1:

TRANSFORM nz(count(T_qa.qaQAPK),0) AS SumOfQAs
SELECT month(qaDate) AS QAmonth, Count(T_qa.qaQAPK) AS QAs
FROM Q_ALL_qa
GROUP BY Month(qaDate)
PIVOT month([qaDate])
IN (1,2,3,4,5,6,7,8,9,10,11,12);

Q2:

TRANSFORM nz(count(T_qa.qaQualityIncidentNo),0) AS SumOfQIs
SELECT month(qaDate) AS QAmonth, Count(T_qa.qaQualityIncidentNo) AS QIs
FROM Q_ALL_qa
GROUP BY Month(qaDate)
PIVOT month([qaDate])
IN (1,2,3,4,5,6,7,8,9,10,11,12);

Thank you

 

[Misiek]

Also, Can I have a calculated field in WHERE ?

...
WHERE count(IIf(Forms!F_CompLvl!cboDeptStats=0,Count(fieldA),Count(fieldB))
...

Basically, if a combo has selected department, count data for this department, otherwise count for all departments.

Thank you

 

[namliam]

Can I nest a transformed query inside another transformed query?

You can only have ONE column header and only have ONE value, unfortunately

Since you are enforcing the columns, you can UNION them if you prefer....

TRANSFORM nz(count(T_qa.qaQAPK),0) AS SumOfQAs
SELECT "QA" as X, month(qaDate) AS QAmonth, Count(T_qa.qaQAPK) AS QAs
FROM Q_ALL_qa
GROUP BY "QA",Month(qaDate)
PIVOT month([qaDate])
IN (1,2,3,4,5,6,7,8,9,10,11,12);Q2:
Union
TRANSFORM nz(count(T_qa.qaQualityIncidentNo),0) AS SumOfQIs
SELECT "QI" as X, month(qaDate) AS QAmonth, Count(T_qa.qaQualityIncidentNo) AS QIs
FROM Q_ALL_qa
GROUP BY "QA", Month(qaDate)
PIVOT month([qaDate])
IN (1,2,3,4,5,6,7,8,9,10,11,12);

Also, Can I have a calculated field in WHERE ?

You have calculated fields with Count, sum in the HAVING clause, not in the WHERE, but basicaly it is the same... However what you are trying to do is not possible in the way you are trying to do it.

The best way to deal with form input like this is to deal with it in a form, not in a query.
In this case you want some VBA that depending on the form input will adjust your query to suite the form's current needs... This avoids a lot of mess having to deal with a potential mountain of IIFs and dealing with NULL values and all such hazards.
And makes sure you only filter and count on that what is actually required/desired

 

[Misiek]

Ok I see. I will skip the nesting then and will try to research more about vba.
What are the keywords I need to look for?

Thank you.

 

[namliam]

To write your VBA adjusted SQL you need to work with
Currentdb.Querydefs("YourQueryName").sql = "Select ..."

 

[Misiek]

Thank you namliam for all your help. I will look into this and probably create a new thread.