Count specific criteria in access query (1 Viewer)

Tor_Fey

Registered User.
Local time
Today, 15:14
Joined
Feb 8, 2013
Messages
121
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
Local time
Today, 15:14
Joined
Feb 19, 2013
Messages
16,553
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

Immoderate Moderator
Staff member
Local time
Today, 10:14
Joined
Feb 28, 2001
Messages
26,999
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

Top Bottom