Counting records based on criteria

[miacino]

I have a report in which one of the fields is [cars].
The field could yield results such as:

Honda
Jeep
Chevy, Jeep
Honda, Jeep, Chevy

In the footer, I'm trying to count how many where the word Honda shows up.

=Count(IIf([car]='Honda',True,Null))

This gives me only 1. How can I do this to count all that contain the word "Honda". (In this case looking for a result of 2).

I tried this as well....
=Count(IIf([car] like 'Honda',True,Null)) - but that didn't work.

Thoughts?

 

[plog]

My thought is you need to normalize your data. Each discrete piece of information should be stored in its own field/record. When you jam data together seperated by commas you are no longer using a database properly.

So instead of:

Honda, Jeep, Chevy

You should have:

Honda
Jeep
Chevy

That way your counting function will work. Normalize your data.

 

[miacino]

I was trying to do a work-around. There are actually three fields [car1] [car2] [car3]. In the underlying query of the report, I created a field [car] which concatenates these three.

I guess then my question would be how do I count records that would have Honda in either [car1] [car2] or [car3] fields?

 

[Uncle Gizmo]

What if you need car4? What if someone enters Honda in Car1 and Car2...?

The solution is as plog suggested, as it avoids the problems I mentioned, and queries are easy...

Sent from my SM-G925F using Tapatalk

 

[plog]

I agree with Gizmo, who agreed with me: You need to properly structure your data. When you start numerating field names, its time for a new table. Data should grow vertically, with more records; and not horizontally with more rows.

Read up on normalization: https://en.wikipedia.org/wiki/Database_normalization

 

[miacino]

Thank you both for your sound advice. I agree in this case I did it unconventionally. For this simple database, we are only tracking 3 cars per person. But you are right, there is room for error (and duplication) doing it this way. Thanks for reminding me that no matter how 'simple' a database's use is, it should be done properly.

 

[Uncle Gizmo]

I agree with Gizmo, who agreed with me:

No, that's not right I agree with you...

 

[Uncle Gizmo]

Thank you both for your sound advice. I agree in this case I did it unconventionally. For this simple database, we are only tracking 3 cars per person. But you are right, there is room for error (and duplication) doing it this way. Thanks for reminding me that no matter how 'simple' a database's use is, it should be done properly.

If you have a reasonable amount of data in your database then you might like to have a look at my website here:-

Excel in Access

where I explain in detail the process of converting something similar to an MS Excel spreadsheet into an MS Access database. I also provide a free tool for converting the Excel type tables into MS Access type tables....