Counting Yes/No Fields

[jammin140900]

G'day all,

I have a table with each "Standards" as a Column Heading eg- Std1, Std2, Std3 etc... as each one is a YES/NO field. Background being that if a Company meets certain standards, the user can tick each standard that the company meets. Each of these are associated to a job the company does.

For example, on Job Number 1, did the company meet Std1, Std 2 and then
on Job Number 2, did the company meet Std1, Std2 etc etc

The tblStandards also contains CompanyNumberID (actual details of this is in another table) and JobID.

All I want to do, is to count the number of standards a company meets (not job dependant but in total). Although I am not grouping by job, the query returns a count by company and job. Can anyone advise please?

Thanks

 

[DCrake]

If you base your results on a query and manipulate the data in your table then you can use the query to produce the results.

For example:

In a new column in a query enter Ans1:Iff(Std1=True,1,0)

Repeat for all the standards you are checking.

Click on the Sigma button on the task bar and change the group by to sum or count and group by CompanyNumberID


CodeMaster:

 

[jammin140900]

Thanks for the suggestion.

I tried that with a small number to see if it works. Under the group by, if you sum them up manually you might see say 7, but then using the Count or Sum it shows you 40. This is incorrect.. Any ideas?

Regards
Jammin

 

[DCrake]

Can you post an extract of your data which can be tested at this end?

CodeMaster:

 

[jammin140900]

G'day mate,

Spent some time with it and figured it out. Turns out you have to use the sum function (count just counts the number of 1's, not sum them) and I changed a join type and it worked. (Initially it was returning all those where the Company had a name. Changing that to return an entry whether or not a company name correctly matched the number of table entries.)

Thank you for your advise with this.

Regards
Jammin

 

[Uncle Gizmo]

.
I would also suggest that if you intend spending a lot of time developing this into a major application that you read this series of threads starting with excel in access part one