Need basic advice on using Count in a query

[Sam_NY]

Hello,

I'm trying to create a query that includes a count of records related to it from a child table. I did try to find answers on this, but something is eluding me. I'm pretty much a novice with Access, so hoping someone can help.



I have a parent table and a related child table (one-to-many). The child table has a text field used (with a lookup) to categorize reach child record by one of three categories (let's say CatA, CabB, and CatC for this). Let's say I have 100 records in the parent table, and I want the query to produce one row for each of the parent records, each with three separate columns corresponding to the three categories. These columns would display the count of the related records in that category.

What I did in query design view:

• Added the two tables
• Changed the join type to display all records from parent table
• Added the fields I needed from the parent table
• Added the category field from the child table
• Clicked on the Totals button
• In the grid, changed the Totals row to "Count" for the category field (and left the other fields as "Group By")
• Changed the criteria to be ="CatA"

Then I ran the query before adding columns for the two other categories. I got this error message:

"Datatype mismatch in criteria expression"

Clearly, Count doesn't work like I think it should (i.e. adding criteria to filter what each column actually counts). Am I on the wrong track here? Is there another way to go about this and get the same resulting file?

Thanks for any advice!

[June7]

What you want sounds like a CROSSTAB query. There is a wizard for that.

Or to emulate a CROSSTAB without the issues they have http://datapigtechnologies.com/flashfiles/crosstab.html

[Sam_NY]

Thanks. That video is very helpful, June7. I think a crosstab query is what I want. Having some problems with it though.

According to the video, the trick is to create a new column based on an IIF() statement. Here's what is used for my Field in the query grid:

CatA: IIF([CategoryTextField]="CatA",CategoryTextField,0)

And then I changed the "Total:" field to "Count"

Interestingly, when I save the query, Access automatically changes the above field to:

CatA: COUNT(IIF([CategoryTextField]="CatA",CategoryTextField,0))

and the Total: field to "Expression"

However, when I run the query I get some weird behavior. First off, every single Parent record that doesn't have an associated record from the child table gets a value of 1. As a test, I tried it on a child table with no records at all--still 1 for all the parent records. Why would it do this?

Secondly, for those parent table records that DO have associated records from the child table, the query counts ALL the records for that row, including CatB and CatC. When there are these records, it doesn't add them to the 1. It gives the correct number of total records, it's just not evaluating the contents of the cell the way I would expect.

Any ideas? The main difference in my case vs. the example in the video is that I'm dealing with two tables rather than one, but maybe it doesn't have anything to do with that.

Thanks again!

[June7]

Post the entire SQL statement for analysis.

If you want to provide db, follow instructions at bottom of my post.

What you are doing is emulating a CROSSTAB. If you want a real CROSSTAB then use the wizard.

[Sam_NY]

Thanks, here's the SQL:

SELECT Bills.IntroNumber, Count(IIf([ActionType]="Vote",[ActionType],0)) AS Votes
FROM Bills LEFT JOIN Bills_Policymakers ON Bills.ID = Bills_Policymakers.BillID
GROUP BY Bills.IntroNumber;

I didn't go the wizard route because at the beginning of the video, the narrator says that with the wizard you can't put in more than one field at a time. I admit I didn't experiment with it and maybe that's changed with 2010.

I'll take a look at that.

[June7]

Hasn't changed. Only one field can be specified as Value for aggregation.

Did not need the full SQL after all, should have seen the issue with expression.

Note that the expression in the example involves 2 fields and Sum. Yours has only one and you want Count.

The Count() counts all data results of the IIf() - 0 is data. What you need is Null. Nulls are not counted.

Count(IIf([ActionType]="Vote",[ActionType],Null))

or

Sum(IIf([ActionType]="Vote",1,0))

[Sam_NY]

That worked perfectly! Thanks.