Report totals and grouping

  • Thread starter Thread starter Therm
  • Start date Start date
T

Therm

I have an access 2003 file that looks like this:
Land Vehicle Water vehicle Air vehicle
Car Boat Plane
Bike Yacht Jet
Truck Raft Blimp
Car Yacht Plane
Bike Raft Jet
Bike Boat Plane

etc.

I'd like the report to look like:
Land Vehicle
Bike 3
Car 2
Truck 1

Water Vehicle
Boat 2
Yacht 2
Raft 3

Air Vehicle
Plane 3
Jet 2
Blimp 1

I cannot figure out how to do this. Any suggestions are appreciated.
 
Therm,
Your best bet is to redesign your table. Your data should look like
this...
Vehicle Type
Car Land
Bike Land
Plane Air
Jet Air
Boat Water
Car Land

Now it's a simple matter of Grouping on Type... then Vehicle.
Air
Jet 1
Plane 1
Land
Bike 1
Car 2
Water
Boat 1

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
Therm,
Your best bet is to redesign your table. Your data should look like
this...
Vehicle Type
Car Land
Bike Land
Plane Air
Jet Air
Boat Water
Car Land

Now it's a simple matter of Grouping on Type... then Vehicle.
Air
Jet 1
Plane 1
Land
Bike 1
Car 2
Water
Boat 1

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
Click to expand...

Al, thanks for the suggestion. I am now getting something like this:
Air
Jet
Jet
Jet
Plane
Plane
Land
Bike
Car
Car

How can I get the totals instead like:
Air
Plane 3
Jet 2
Blimp 1

Therm
 
Therm,
Please TOP post, so we won't be scrolling up and down to follow this
thread. It's best to follow the responder's lead in posting style.

Looks as though you're Grouping on the Vehicle [Type] OK.

Now add a [Vehicle] Group Ascending (within your TYPE Grouping), and say
Yes to the Vehicle Group Footer. You don't want to see 3 car entries, you
just want the Count of each.

In your Vehicle Group Footer, place these two text controls...
txtVehicle with a "bound" ControlSource of [Vehicle]
txtVehicleCount with a ControlSource of =Count(Vehicle)
Given this raw data
Air
Jet
Jet
Jet
Plane
Plane
Land
Bike
Car
Car
Click to expand...

You should get...
[Type] Group Header > Air
[Vehicle] Group Footer > Jet 3
Plane 2
Land
Bike 1
Car 2
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
Back
Top