record count help

[tmongy]

Hi,
I have a table with the following form:

Cntr#|Location|SIZE|TYPE
ae343|germany|20 |DRY
bn983|italy |40 |DRY
: : : :
: : : :

Now I need to do a count on the number of records according to location, size and type. So for example I might need to know the count for containers located in italy of size=20 and type=dry. I need to be able to perform these counts for a large number of loc/size/type combinations. After getting the numbers for the counts, I would then need to display them in a form.
I'm kind of an access newbie so pls try to explain any suggestions as thoroughly as you can. Thanks in advance.

 

[WayneRyan]

tmongy,

Make a new query:

Select Location, Size, Type, Count(*)
From   YourTable
Group By Location, Size, Type
Order By Location, Size, Type

Then, base your form on the above query.

Wayne

 

[tmongy]

Thanks Wayne, that worked nicely!
A couple more questions, is there a way to group some of the counts together? Like if I have 2 locations, one named Italy1 and another Italy2, is it possible to count(*) them as one location?
Can the results of the query be copied into a table?
Thanks for you help, much appreciated.

 

[Pat Hartman]

Instead of making the location field do double duty, you should separate the suffix numbers into a separate field. That will allow you to group on only the country name. It is poor practice to make a single field contain multiple attributes. Set the suffix field to default to 1 so that unless there is a France2, all records will be for France1. You will need to run an update query to populate the new field for all existing rows.

If the suffix is ALWAYS only a single numeric value, you can extract it by using the Right() and IsNumeric() functions;

Select IIf(IsNumeric(Right(Location,1)),Left(Location, Len(Location) -1, Location) As TrimmedLocation, Size, Type, Count(*) as BoxCount
From YourTable
Group By IIf(IsNumeric(Right(Location,1)),Left(Location, Len(Location) -1, Location) , Size, Type;

The group by will automatically order the recordset. You only need an order by if you don't want the records returned in their group by order.