Counting various value occurance with Query

[justphilip2003]

In my table, a field has 5 different possible values. I would like to know if a single Query can be coded to count each value occurrences in the table. I've tried to do so by creating "group by" count, creating five columns with field names Expr1: [Count1] through Expr5: [Count5]. Each field column has a Total field. I tried using total by group, by count and by expression. Each field has a starting criteria of "PoliticalPreference" =1 through "PoliticalPreference"=5. Each attempt failed with a too complex code statement. Also Access will ask for values to use for each column and then it wipes out all of the coded criteria and changes the total by count to expression if I input same. Am I reaching too far with Query? TIA Phil

[June7]

A GROUP BY should work.

SELECT Fieldname, Count(*) AS CountFieldname FROM tablename GROUP BY Fieldname;

Or try a CROSSTAB with the wizard.

Here is how to simulate CROSSTAB with expressions http://datapigtechnologies.com/flashfiles/crosstab.html.

[justphilip2003]

I tried to follow the datapig video to simulate a CrossTab Querry. I created five columns with field names {DEM: Count(IIf([PPref]="1",1,0,}, {IND: Count(IIf([PPref]="2",1,0},...,REP: Count(IIf([PPref]="5",1,0}. I Get the same message "Cannot have aggregate function in {Group By, Sum, or Count} clause {DEM: Count(IIf([PPref]="1",1,0,}. I am thinking my table structure doesn't lend itself to this Query form since PPref isn't a sub field for a group (like a region or segment) as shown in the video. Am I correct or is there a coding error? TIA Phil

[June7]

The expressions you show are not constructed according to the video. The aggregate functions do not go in the expressions as you show. Look at the tutorial again. Use Sum instead of Count.

The resulting SQL statement would be like:

SELECT Sum(IIf([PPref]=1,1,0)) AS DEM FROM tablename GROUP BY something;

If there are only the calculated fields and no GROUP BY clause, the result will be a single record with the 5 calculated fields.

[justphilip2003]

I did try crosstabs but my tables have no groupings, hence they do not lend themselves to crosstabs. Thanks for all of the assistance. Phil