How to Group By 1

[hawkeye71]

I understand the concept of group by and have used it successfully. However, the table at hand has a column for car names, e.g., Camry1, Camry2, chevy1, chevy2, maxima1, maxima2.
I want to get a count of toyotas, nissans, and chevys, etc. I believe I need to use group by, but since chevy1 is not equal to chevy2, and camry1 is not equal to camry2, they are not couted in same group. I would like to group by the first three letters of car name, and get a count of these cars. Those which do not fit in camry# or maxima # or chevy#, should all be grouped in another group, regardless of the first three letters of the car's name.
To add an extra column to the existing table is not an option.
All suggestions welcome.

Thanks,
Indiana

 

[glalsop]

To get all of the Camry's, Chevy's, etc. together you can set your CarName field up to group by an expression:

=Left([CarName],3)

There may be a simpler way to create grouos for 'Other' data in Access, but for the time being you could create a main report and a subreport, and filter the data accordingly:

In the main report, set the filter property to:

(CarName like camry*) or (CarName like maxima*) or (CarName like chevy*)

Then you could create a subreport that is essentially identical to the main report, but set its filter to:

not ((CarName like camry*) or (CarName like maxima*) or (CarName like chevy*))

You can then place the subreport in the main report footer.


Good luck

-Gary

 

[hawkeye71]

Thanks Gary, it did work.
One more question:
If I want to group by Foreign and Domestic, how could I combine maxima and camry in one group, and chevy and ford in another group.

Thanks,
Indiana

 

[glalsop]

Do you have a field that distinguishes between foreign and domestic cars? If so, you can just add an outer group on top of the car group for your foreign/domestic field and all should work fine.

If you don't have this field then you should add it to the table. Otherwise, you are going to find yourself continually hard-coding your reports, and this (to a large degree) defeats the purpose of using Access' reporting tool.

-Gary

 

[hawkeye71]

Gary:

Also, when I group by on carname field and set the criteria "like (camry*), yes, it only selects Camrys, but the count is divided between camry1, camry2, camry3, etc.
I should get one count for all camry*.

Thanks,
Indiana

 

[glalsop]

Where is the count field located?

If you group on the carname field then it will still group all of the camry's seperately.

You need to group on an expression instead:

=Left([CarName],3)

This will group everything that starts with "Cam" together, and count them all together.

Good luck


-Gary