query count question

[belsha]

I have a group of 5 fields, each which are yes/no (as directed by the head of this project). They are actions that can be taken on a problem; multiple actions can be taken. I need to query and count totals on each action. I have done a query - if A or if B or if C.... but how can I do a count for each option within one query?

 

[Mile-O]

You'd be better to normalise your structure first but...since it's what you have...

To create a new field in the query:

MyCount: CountFields([Field1], [Field2], [Field3], [Field4], [Field5])


And, the public function: (to be put in a module)

Public Function CountFields(boo1, boo2, boo3, boo4, boo5) As Integer

    Dim intTotal As Integer
    If boo1 Then intTotal = intTotal + 1
    If boo2 Then intTotal = intTotal + 1
    If boo3 Then intTotal = intTotal + 1
    If boo4 Then intTotal = intTotal + 1
    If boo5 Then intTotal = intTotal + 1

    CountFields = intTotal

End Function

I still say normalise as you have a repeating group and this will cause problems, headaches, sleepless nights, etc. if you need to add these horrible checkbox fields.

 

[GordonB]

Maybe I'm reading the question wrong, but it looks like you want to count the number of times each check box is ticked.

Make a new field name in the query for each checkbox field

MyCountA:Sum(IIf([FieldNameA],1))

Press Sigma button on toolbar and change Group by to Expression.

Run query

 

[Mile-O]

Ah! I have read it wrong...


...still needs normalised, though.

 

[GordonB]

Mile O Phile,

I am a bit of a numpty really, and am learning all this as I go along, picking up lots of information from you and many others on this excellent site.

However, would you tell me, in layman's terms, what you mean by normalising, please.

Cheers,

Gordon.

 

[Mile-O]

However, would you tell me, in layman's terms, what you mean by normalising, please.

Normalisation - basically, it is the process of defining a robust object model for your database; the foundation upon which you will build queries, forms, reports, etc.

It involves looking at your structure and identifying relationships, groups, and similar "objects" and, in the long run, reducing design problems going forward.

For example: (and as a tiny example)

We could have a quick employee phonebook with in a business.

So, immediately we have Employees who will have a table.
We think about what information about the employees we'll need: name, department, phone number

But wait: Name is a reserved word so we can't use that and it's a better practive to break things down as much as possible so we can have Forename and Surname (it's easier to concatenate than to extract).

The other thing is, our employee table (for each employee) is going to have that department field and huge groups of people are going to be within the same department so we have something that's repeating itself over an over. From this we learn that the department is dependant upon the employee - or, in other words, the department is another object for our model. So we make a new table, for departments.

We now have two tables: we'll call them tblDepartments and tblEmployees.

So, I'm actually awful at explaining this - better to look up normalization on the web, Microsoft Site , and this forum, etc. Loads of info.

If you can get your database to Third Normal Form - then you should be okay. Search the web for 1st, 2nd, and 3rd Normal Form - loads of info out there.

 

[Mile-O]

Basically, take your database and ensure it grows downwards and not outwards.

Using belsha's example they have five options wide in a table. Opt1, Opt2, Opt3, Opt4, Opt5

So, they build their database with this structure but then someone comes along and says "hey! we need Opt6 and Opt7" then, because of the repeating group, belsha would have to go back into the database, fix all the queries, rebuild forms, adapt reports, etc.

Now, were a new table used to capture these groups and build them downwards, then there shouldn't be any need for maintenance headaches caused by the former method.

By using another table we can have two fields and then add each Opt into a record and it grows down.

 

[belsha]

Sometime I have no control over the way a database is set up, or a new one needs to be set up. I know many of the databases I work on should be normalized, however, I am told not to change them so I have to work with what I have within those constraints. Not the best way to do things, but... Thanks for the suggestions!

 

[GordonB]

My thanks, Mile O Phile.

I'll investigate further when I get a chance.

Gordon.

 

[belsha]

Mile,

For my own knowledge, what would you recommend, an unbound form with the different actions that could be taken vs. the checkboxes they want? The case number would have to be the PK, and then numbers for each option maybe? The way it works is multiple actions can be chosen for each case (Action Recommended), and then Action Completed, then Date Completed. I agree that needs to be moved out of the original table because of the incidence of repetition, but what would the best way be to implement entering that info? Thanks!