Count specific criteria in access query (1 Viewer)

Tor_Fey

Registered User
Joined
Feb 8, 2013
Messages
109
Good Afternoon.

I have a table called enquiries, and within this table I have a field called person, date, year & complex (complex field just contains the numbers 1,2,3,4,5,6,7 & 8)


What I need to do is count all records in a query; against a person’s name for types 1,2,3,4,5,6,7 & 8, and return just the counts against that persons name.

An example would be:

So bob has: 9 1s for the year 2017
4 2s for the year 2017
5 3s for the year 2017

Any help would greatly be appreciated.

Kind Regards
Tor Fey
 

CJ_London

Super Moderator
Staff member
Joined
Feb 19, 2013
Messages
11,564
use a totals or group by query

group by person, date, year & complex and also include a count for complex

Note that date and year are reserved words and should not be used for field names
 

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,545
Technically, your "complex" field is a denormalized data field. To get counts, you have to set up complex queries.

Ideally, you would have TWO tables. One holds your person info including a person ID, their name and other info you track about people.

The "complex" data would be in a child table where you have ONE of those numbers for each record, plus the info showing the person ID and date for which that number applied. The child table would be joined to the parent table by person ID.

If you do THAT, you can build summation queries to determine the counts for each complex as a single query.

Having the data jumbled together in a single field makes it difficult. Splitting it so that you have multiple (short) entries for each type code allows you to use normal COUNT or DCOUNT options to obtain counts in simple SQL. This method, by the way, addresses the normalization flaw called "repeating groups".

I respectfully suggest you might benefit by reading up on database normalization and in particular on why a repeating group is not a good thing.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom