Query using to Countif function for multiple fields from a specific record

[dougdrex]

Using Access 2010



I'm an expert user in Excel and am trying to learn more about Access, so I relate many of the functions to what they are called in Excel. And what I'm trying to do right now is count the number of fields in a specific record where the value is a specific result (CountIf).

For example, I need to return the number of fields in a specific record where the value is "Meets Requirements". The specific record will be determined by the Name, Contact, and Email fields (which together comprise the Primary Key).

Is there a way to count the values in multiple fields that equal "Meets Requirements"?

I hope my question makes sense. If not, please let me know and I can clarify any questions.


Many Thanks!
Doug

[ranman256]

Here in Access land, use queries.
In a query , add your table, from the table, bring down your field you wish to count , twice.
like:
[name], [name]

turn on TOTALS. (the summation icon)
under the 2nd [name], on the TOTAL line , change GROUP BY, to COUNT.

run the query
you will get counts of the items
BOB 5
SAM 12

[dougdrex]

When I tried the above suggestion for one of the fields using "Meets Requirements" in the Criteria line of the query, it gave me an error message stating "Data type mismatch in criteria expression". I checked the table where the underlying data resides and all the records (5 or 6) contain "Meets Requirements" in that field.

I'm puzzled why it won't work.

Also, I would like to create a field in the query to checks across multiple fields for a specific value (such as Meets Requirements), not just one specific field. Is that possible?

[June7]

What data type is the field(s)? Is it a number type and you have Lookup set or is it text and stores the description.

If you want to search multiple fields for the same parameter, sounds like non-normalized data structure. Apply the same parameter but on different criteria lines so the OR operator is invoked

[dougdrex]

The data type for all the fields is Text. The data is uploaded from a Questionnaire that is originally stored in Excel. There are five different responses that can be populated in the fields that I want to search. In fact, I'm trying to set up five fields in this query to count the number of the various responses (one for each possible response).

I'm wondering if I should set up five different queries, then do another one to incorporate all the results.

I hope this makes sense. If I can provide anything further to help clarify, please let me know.


Thanks!
Doug

[June7]

Provide the SQL statement of query or post the db. Follow instructions at bottom of my post.

Sounds like non-normalized structure is complicating efforts, otherwise a CROSSTAB query should be able to do the count. Might need to first do a UNION query to normalize the structure then use that query to do CROSSTAB.

[dougdrex]

I figured posting the SQL view of the query might be easier to start with. If necessary, I'll upload the db.

SELECT HRMP_Supplier.Supplier_Name, HRMP_Supplier.Country, RegionMapping.Region, HRMP_Analysis.Date_Complete, HRMP_Analysis.SOEID FROM (HRMP_Supplier INNER JOIN HRMP_Analysis ON (HRMP_Supplier.Email = HRMP_Analysis.Email) AND (HRMP_Supplier.Supplier_Contact = HRMP_Analysis.Supplier_Contact) AND (HRMP_Supplier.Supplier_Name = HRMP_Analysis.Supplier_Name)) INNER JOIN RegionMapping ON (HRMP_Supplier.Country = RegionMapping.Country) AND (HRMP_Analysis.Country = RegionMapping.Country)
GROUP BY HRMP_Supplier.Supplier_Name, HRMP_Supplier.Country, RegionMapping.Region, HRMP_Analysis.Date_Complete, HRMP_Analysis.SOEID, [HRMP_Supplier]![Supplier_Name] & [HRMP_Supplier]![Supplier_Contact] & [HRMP_Supplier]![Email], HRMP_Analysis.[1_Analysis], HRMP_Analysis.[2_Analysis], HRMP_Analysis.[2A_Analysis], HRMP_Analysis.[2B_Analysis], HRMP_Analysis.[2C_Analysis], HRMP_Analysis.[2D_Analysis], HRMP_Analysis.[2E_Analysis], HRMP_Analysis.[3_Analysis], HRMP_Analysis.[3A_Analysis], HRMP_Analysis.[3B_Analysis], HRMP_Analysis.[4_Analysis], HRMP_Analysis.[4A_Analysis], HRMP_Analysis.[4B_Analysis], HRMP_Analysis.[4C_Analysis], HRMP_Analysis.[4D_Analysis], HRMP_Analysis.[5_Analysis], HRMP_Analysis.[6_Analysis], HRMP_Analysis.[7_Analysis], HRMP_Analysis.[7A_Analysis], HRMP_Analysis.[7B_Analysis], HRMP_Analysis.[7C_Analysis], HRMP_Analysis.[7D_Analysis], HRMP_Analysis.[7E_Analysis], HRMP_Analysis.[8_Analysis], HRMP_Analysis.[8A_Analysis], HRMP_Analysis.[8B_Analysis], HRMP_Analysis.[8C_Analysis], HRMP_Analysis.[8D_Analysis], HRMP_Analysis.[8E_Analysis], HRMP_Analysis.[9_Analysis], HRMP_Analysis.[9A_Analysis], HRMP_Analysis.[10_Analysis], HRMP_Analysis.[10A_Analysis], HRMP_Analysis.[11_Analysis], HRMP_Analysis.[11A_Analysis], HRMP_Analysis.[11B_Analysis], HRMP_Analysis.[11C_Analysis], HRMP_Analysis.[11D_Analysis], HRMP_Analysis.[12_Analysis], HRMP_Analysis.[12A_Analysis], HRMP_Analysis.[13_Analysis], HRMP_Analysis.[13A_Analysis], HRMP_Analysis.[14_Analysis], HRMP_Analysis.[14A_Analysis], HRMP_Analysis.[14B_Analysis], HRMP_Analysis.[14C_Analysis], HRMP_Analysis.[14D_Analysis], HRMP_Analysis.[14E_Analysis], HRMP_Analysis.[14F_Analysis], HRMP_Analysis.[15_Analysis], HRMP_Analysis.[15A_Analysis], HRMP_Analysis.[15B_Analysis], HRMP_Analysis.[15C_Analysis], HRMP_Analysis.[15D_Analysis], HRMP_Analysis.[15E_Analysis], HRMP_Analysis.[15F_Analysis], HRMP_Analysis.[16_Analysis], HRMP_Analysis.[16A_Analysis], HRMP_Analysis.[17_Analysis], HRMP_Analysis.[17A_Analysis], HRMP_Analysis.[17B_Analysis], HRMP_Analysis.[17C_Analysis], HRMP_Analysis.[17D_Analysis], HRMP_Analysis.[17E_Analysis], HRMP_Analysis.[17F_Analysis], HRMP_Analysis.[18_Analysis], HRMP_Analysis.[19_Analysis], HRMP_Analysis.[20_Analysis], HRMP_Analysis.[21_Analysis], HRMP_Analysis.[21A_Analysis], HRMP_Analysis.[22_Analysis], HRMP_Analysis.[22A_Analysis], HRMP_Analysis.[22B_Analysis], HRMP_Analysis.[22C_Analysis], HRMP_Analysis.[22D_Analysis], HRMP_Analysis.[22E_Analysis], HRMP_Analysis.[23_Analysis], HRMP_Analysis.[23A_Analysis], HRMP_Analysis.[23B_Analysis], HRMP_Analysis.[23C_Analysis], HRMP_Analysis.[23D_Analysis], HRMP_Analysis.[23E_Analysis], HRMP_Analysis.[23F_Analysis], HRMP_Analysis.[23G_Analysis];

All the fields ending in "_Analysis" are the fields that contain the five possible values:
1. Meets Requirements
2. Does Not Meet Requirements
3. Partially Meets Requirements
4. Unable to Determine
5. Not Applicable

I am trying to add five fields (one for each possible value) that will count the number of times each value is contained in the fields ending in "_Analysis" above.

Let me know if you're able to work with the info provided in the SQL view of the query. If the db is needed, I can upload it.

Thanks for your help!
Doug

[June7]

Not seeing any aggregate expressions.

More than 50 "_Analysis" fields (I stopped counting). A UNION query has a limit of 50 lines so a single UNION query is not going to accommodate.

Need to normalize data structure and get rid of the spreadsheet 'flat file' design.