Making a simple yes count! (1 Viewer)

Krays23 · May 9, 2016

Guys

im having a moment here

I have a query which looks at various parts in production areas

I have a check box that's returning yes there is a part ther or no there isn't

all I want to do is count the yes ! surely im missing something here

PLease help

Dan

Ranman256 · May 9, 2016

since YES is actually -1, you can just sum the absolute value: Sum( ABS([field]))
or
count([field]) where [field] = true

Krays23 · May 9, 2016

Ranman256 said:
since YES is actually -1, you can just sum the absolute value: Sum( ABS([field]))
or
count([field]) where [field] = true

I have 15 different fields I want to count each field

one field is called [Part In Lay Up] where do I put it in criteria?

it says invalid syntax

Ranman256 · May 9, 2016

then your table layout is wrong. Access counts down in a field, not across table fields.

But you can still count them, I suggest a union query
select "part In LayUp" as lbl, sum(Abs([Part In Lay Up])) from table
union
select 'Is Smoker' as lbl, sum(Abs([smoker])) from table
union
etc

Krays23 · May 9, 2016

Thanks got it counting the fields but in negative any way to flip that when I make it a graph as I actually have positive parts not negative?

The_Doc_Man · May 9, 2016

If worst comes to worst, write a query.

In any field where you have one of these in the raw table, use

IIF( Nz([Y/N field name], False), 1, 0 )

Then you can do a DCOUNT of that field in a second query. The NZ protects against damaged records or nulls, and the IIF returns one of two values based on True/False state of the first argument of the IIF.

Anakardian · May 10, 2016

You can always multiply the result with -1.

Krays23 · May 10, 2016

The_Doc_Man said:
If worst comes to worst, write a query.

In any field where you have one of these in the raw table, use

IIF( Nz([Y/N field name], False), 1, 0 )

Then you can do a DCOUNT of that field in a second query. The NZ protects against damaged records or nulls, and the IIF returns one of two values based on True/False state of the first argument of the IIF.

I cant get it to work

I have 16 fields all I want to do is count the rows that say yes in each field and make a graph off it in positives not negatives

Krays23 · May 10, 2016

Krays23 said:
I cant get it to work

I have 16 fields all I want to do is count the rows that say yes in each field and make a graph off it in positives not negatives

I can build a query and see the data I want but ill be dammed if I can show it on graph

Making a simple yes count! (1 Viewer)

Krays23

Registered User.

Ranman256

Well-known member

Krays23

Registered User.

Ranman256

Well-known member

Krays23

Registered User.

The_Doc_Man

Immoderate Moderator

Anakardian

Registered User.

Krays23

Registered User.

Krays23

Registered User.

Similar threads

Users who are viewing this thread