Group by Help and Count or Sum function

[Shilabrow]

I have a field Named Toys; Under Toys there are 5 kinds, Cars, Trucks Barbie, Piano, Bike. I want to Retrieve Toys but display only Car (Group Car, Truck and Bike - then count them), Piano and Barbie.



I want to list only toys as CAR, Barbie, Piano. However I want the Car to include total count of cars, truck and bike AS CARS - pretty much rename the truck and bike as Cars and count them as CAR while counting the Piano and Barbie as well.

All help in querying this will be appreciated. If I get the querying guideline to set the criteria that will be helpful.


Thanks much!!

[June7]

Create a Category field in query with expression and GROUP BY on that field. Is there a unique ID field?

SELECT IIf([Toys]="Cars" Or [Toys]="Truck" Or [Toys]="Bike", "Vehicles", [Toys]) AS Category, Count([ID]) AS ToyCount FROM tablename GROUP BY IIf([Toys]="Cars" Or [Toys]="Truck" Or [Toys]="Bike", "Vehicles", [Toys]);

[Shilabrow]

Thanks June,
I don't know what am doing wrong but when I used a portion of the query to just retrieve the toys as CATEGORY IIf([Toys]="Cars" Or [Toys]="Truck" Or [Toys]="Bike", "Vehicles", [Toys]) AS Category from Table name. I get #Error in the field content and every other information I queried comes out from the table and the Toy field name properly changed name as CATEGORY just got error in the field content. This is without even doing the count. Note, I added more field queries but they all came out good except the CATEGORY field showing #Error.

Please what am I doing wrong. Thanks much!!!!

[June7]

Don't know. The query works with my test.

Make sure the field names and the values are correctly spelled. Post your exact query statement.

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