Multiple counts in group query

[TomBP]

[SOLVED] Multiple counts in group query

Hi,

I am trying to add a second and third count field in the group query ("Automation Ratio - Potential Per Supplier" ) but it either results in:

Attempt 1: Data type mismatch
Attempt 2: Wrong count

Can someone point out what I'm doing wrong here? Thanks in advance.

I've attached a visual overview (JPEG) of both attempts. The queries mentioned in the overview are also available in Access to fiddle around with.

 

[TomBP]

I've rearranged the attachments so that the JPEGs are directly visible. Hopefully this will result in more people looking at the problem.

 

[Brianwarnock]

The way to do multiple counts based on a fields value is to use IIF statements with SM selected in the Totals dropdown

CountOfNVR: IIF(Fieldname="NVR,"1,0)

Totals Select Sum not Count

Brian

Edit a couple of typos here SM instead of SUM
and ="NVR,"1,0)

should be ="NVR",1,0)
but Bob has put things right later in the thread

 

[TomBP]

Hi Brian,

I tried using your expression but I keep running into a syntax error (operand without an operator). I presume this has something to do with our difference in regional settings (we use semicolon instead of comma). As a result I created the following expression:

CountOfNVR: IIf([NVR]/[VR]="NVR";"1";"0")

Yet this runs into another syntax error.

 

[boblarson]

Hi Brian,

I tried using your expression but I keep running into a syntax error (operand without an operator). I presume this has something to do with our difference in regional settings (we use semicolon instead of comma). As a result I created the following expression:

CountOfNVR: IIf([NVR]/[VR]="NVR";"1";"0")

Yet this runs into another syntax error.

This is wrong:

CountOfNVR: IIf([NVR]/[VR]="NVR";"1";"0")

This would be right:

CountOfNVR: IIf([NVR/VR]="NVR";1;0)

Notice the bracket change on NVR/VR and also the quotes removed from your numbers (you don't use quotes on numbers if you want them to be numbers - quotes make them text).

 

[boblarson]

Check out my query included in your Attempt 2 file here.

 

[Brianwarnock]

I think that you should avoid using / in control names NVR_VR would have been better and the incorrect [] would not have occurred.

Brian

 

[TomBP]

Thanks both Brianwarnock and boblarson. Your help was much appreciated.

 

[boblarson]

and the incorrect [] would not have occurred.

most likely

However, in this instance you still need the square brackets so putting them in the right place would have been more logical but I've still seen it done incorrectly even on a short field name because some will mistakenly include the table name and then do something wrong like:

IIF([TableName.FieldName] = "ZZZ", 1, 0)

Which then the missing inside brackets would cause a problem.

 

[Brianwarnock]

Hi Bob,

I'm reluctant to argue with a current practitioner but I'm sure that I never typed the [] and if I did quote the table name ie table1.field1 ACCESS would put the [] correctly [table1].[field1]
but this all assumes sensible naming conventions.

Brian

 

[Brianwarnock]

yep, just tried it, use sensible naming , no spaces or special character and Access sorts it out.

Brian

Actually reading your post again I don't think that we are disagreeing on anything, you are just saying that people should learn to do the right thing with their [], me I don't use them preferring to get my names right and letting Access do the work.

 

[boblarson]

yep, just tried it, use sensible naming , no spaces or special character and Access sorts it out.

But it can't if you use SQL View to type it in manually. Some items will work okay without the brackets but some things won't and it won't add them for you if you do it that way. (I don't use it that way, I usually use the QBE grid if at all possible).

And in Access 2010 you actually get Intellisense which, if you use the QBE grid will pop up things for you to select and it will have the brackets there when you see the item dropped down.

 

[Brianwarnock]

I only use SQL view if forced to. I use drag and drop and every other aid possible, I think that having entered programs into an osciloscope in my early days and then progressed througth BAL I have an aversion to doing things the hard way.

Brian