Making a simple yes count!

K

Krays23

Registered User.

Local time

Today, 05:01

Joined

Jun 18, 2015

Messages

46

• May 9, 2016

• #1

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

Well-known member

Local time

Yesterday, 21:01

Joined

Apr 9, 2015

Messages

4,354

• May 9, 2016

• #2

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

 

K

Krays23

Registered User.

Local time

Today, 05:01

Joined

Jun 18, 2015

Messages

46

• May 9, 2016

• #3

Ranman256 said:

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

Click to expand...

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

Well-known member

Local time

Yesterday, 21:01

Joined

Apr 9, 2015

Messages

4,354

• May 9, 2016

• #4

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

 

K

Krays23

Registered User.

Local time

Today, 05:01

Joined

Jun 18, 2015

Messages

46

• May 9, 2016

• #5

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

Immoderate Moderator

Staff member

Local time

Yesterday, 20:01

Joined

Feb 28, 2001

Messages

30,398

• May 9, 2016

• #6

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.

 

A

Anakardian

Registered User.

Local time

Today, 03:01

Joined

Mar 14, 2010

Messages

173

• May 10, 2016

• #7

You can always multiply the result with -1.

 

K

Krays23

Registered User.

Local time

Today, 05:01

Joined

Jun 18, 2015

Messages

46

• May 10, 2016

• #8

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.

Click to expand...

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

 

K

Krays23

Registered User.

Local time

Today, 05:01

Joined

Jun 18, 2015

Messages

46

• May 10, 2016

• #9

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

Click to expand...

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