help needed with Nz() and vba coding

[Misiek]

ok, changed my mind, going to use a report to display all my Dcounts, based on 2 queries. Any suggestion on how to make it work quickly before I start, please.

 

[smig]

ok, changed my mind, going to use a report to display all my Dcounts, based on 2 queries. Any suggestion on how to make it work quickly before I start, please.

What do you mean by displaying all your DCounts ?
What is based on 2 queries ?

 

[Misiek]

Have 2 queries, but they count different records, just displayed on the same form thats why i said 2 queries, but to begin with I would like to start with the first one, create a report and then go to the second one.
query has:
1. ID
2. date
3. text field A
4.yes/no field
5. text field B(number only)

Count fields on my form, This is what I have now:
1. count how many records
=DCount("qaQAPK","Q_CompStats_qa")
2. how many records where text field A has a value
=DCount("qaQualityIncidentNo","Q_CompStats_qa")
3. how many records where yes/no field has a value = yes
=DCount("qaQAPK","Q_CompStats_qa","qaSeverity=True")

then all of those repeat for each month.
so i just added Month([qaDate]) = 1" criteria.

Does it all look ok? can I just create form with exactly same fields and calculation?
or do you have any suggestions?

thanks

 

[smig]

Each DCount is a query by itself. You have a report with 6 or more queries.
You can create an unbound report, with no data in it, and put these DCounts directly on the report

 

[namliam]

Instead just count it in the query...

Select Count(qaQAPK) as HowManyRecords
, count(qaQualityIncidentNo) as FieldAhasavalue
, sum(iif(qaSeverity=True),1,0) as SeverityTrue
, sum(iif(qaSeverity=True),0,1) as SeverityFalse
, month(qaDate) as monthofqadate
, year(qadate) as YearOfqaDate
from yourtable
group by 
 month(qaDate)
, year(qadate)

This will be x-1 times faster than doing x dcounts (10 dcounts means 9 times faster, 100 dcounts 99 times faster)

 

[Misiek]

Wooooow I'm already excited, will try it tonight. Thanks for that

 

[Misiek]

Namliam,

I'm sorry but just simply don't know.
Where exctly do I need to insert this code?

I'm thinking of text box on event and then create private sub ...

 

[namliam]

it is SQL, you put it into the recordset of the report or into a query

 

[Misiek]

oh I see,
I have created a form and pasted your SQL into the record source, but it comes with an error,

Wrong number of arguments used with function with query expression: "sum(iif(qaSeverity=True),1,0) as SeverityTrue"

unsure of what to do next.

 

[Misiek]

Hello,
I am still stuck on this one.
Can anyone advice on how to create the SQL to make it run faster, please.

Thank you kindly

 

[Frothingslosh]

Your query expression in post 29 is written incorrectly. It should be

Sum(Iif(qaSeverity=True,1,0)) as SeverityTrue

However, an even better way to do it might be:

Sum(qaSeverity * (-1)) as SeverityTrue

This is because Access actually saves boolean values as 0 if false and -1 if true. For SeverityFalse, though, you can either try my first code block with "0,1" rather than "1,0", or this:

Sum(qaSeverity + 1) as SeverityFalse

My latter ways might speed things up a bit by avoiding IIf function calls.

 

[namliam]

I missed your update for some reason, sorry

It seems I misplaced the brackets

Select Count(qaQAPK) as HowManyRecords
, count(qaQualityIncidentNo) as FieldAhasavalue
, sum(iif(qaSeverity=True,1,0)) as SeverityTrue
, sum(iif(qaSeverity=True,0,1)) as SeverityFalse
, month(qaDate) as monthofqadate
, year(qadate) as YearOfqaDate
from yourtable
group by 
 month(qaDate)
, year(qadate)

 

[Misiek]

Frothingslosh & namliam

woooooooow this is sooooo fast and so simple, left me speechless, massive THANK YOU to both of you.

I want your brains

I need to create another query, where user selects a department from combo, how to add it to this line:

, count(qaQualityIncidentNo) as FieldAhasavalue

Thank you

 

[Misiek]

and also,
query shows all data and ordered by month,
how to display data for specific month?

my report looks like this:
------------------jan---- feb---- mar
All QA [value] [value][value][value]
All QI [value] [value][value][value]
severity [value] [value][value][value]

 

[Misiek]

ok googled one of my answers, need to use WHERE

where qaDeptFK = Forms!F_CompLvl!cboDeptStats

but still unsure what to do with my months, if quewry produces resoults for all months, so to assosiate a text box on the report to show only values (calculation) for specific month.
Can I do it from one query for all months? or do I need 12 separate queries for each month?

Thank you.

 

[namliam]

research a crosstab query, that can do what you want for the months.

 

[Misiek]

thanks namliam,

what am I missing, as it doest show any data in "months"

TRANSFORM count(T_qa.qaQAPK) AS SumOfQuantity
SELECT Count(T_qa.qaQAPK) AS QAs
FROM Q_ALL_qa
GROUP BY Month(qaDate), Year(qadate)
PIVOT Year([qaDate]*12)
IN (1,2,3,4,5,6,7,8,9,10,11,12);

 

[namliam]

You are doping what? Pivoting the year * 12 and limiting that on 12 months?

Ome limit of accessoires crosstabs is you can only pivot ons column/value so if you want year and month you have to concatenate the something like
Year *100 + month

 

[Misiek]

Silly me.
Ok it's just months now. Fully working

Can I combine 2 different fields into one query?
Like I had at the begining, count qaQAPK, qaqualitysomething...
And then show results the same way as I do now?

Is it possible? Or do I need a separate query for each field name I want to have calculations done?

 

[namliam]

You can add as many fields to the ROW HEADING as you want, only 1 column can be COLUMN HEADING

Experiment a bit and see if you like the results or not...

Like I said you can combine the year and the month into one string, the "ugly" part is that columns will be sorted asc and not much you can do about it (without using the hardcoded IN statement).
So in your case you can combine year and month using the formula I already gave you, this should put things in the proper order.