Combining Records and Summing Data

[tsmvengy via AccessMonster.com]

I have a table with data like this:

Agency Type Oct Nov Dec
New York BUS1 5 4 6
New York BUS2 4 9 10
New York FERRY 15 18 20
New York SUBWAY1 10 20 30
New York SUBWAY2 15 18 90


I want to sum the categories that go together so I get something like:

Agency Type Oct Nov Dec
New York TOTALBUS 9 13 16
New York FERRY 15 18 20
New York TOTALSUBWAY 25 38 120

So I can put this into a report. The table also has records like this for
other cities (chicago, LA, etc.)
Is this possible, and how can I do it if so?

Thanks!

 

[John W. Vinson]

I have a table with data like this:

Agency Type Oct Nov Dec
New York BUS1 5 4 6
New York BUS2 4 9 10
New York FERRY 15 18 20
New York SUBWAY1 10 20 30
New York SUBWAY2 15 18 90

Then you have a spreadsheet, not a properly designed relational table! Storing
data in fieldnames is ALWAYS a bad idea; storing a month name as a fieldname
is even worse (is Dec December 2008? 2009? Both? 1992?)

A properly normalized design would have one *row* per month, or per date:

Agency
Type

I want to sum the categories that go together so I get something like:

Agency Type Oct Nov Dec
New York TOTALBUS 9 13 16
New York FERRY 15 18 20
New York TOTALSUBWAY 25 38 120

So I can put this into a report. The table also has records like this for
other cities (chicago, LA, etc.)
Is this possible, and how can I do it if so?

Access won't be able to automagically parse your TYPE field, which appears to
be freeform and not from a controlled vocabulary. What you could do is create
a Types table with two fields, TYPE and SUPERTYPE:

TYPE SUPERTYPE
BUS1 TOTALBUS
BUS2 TOTALBUS
FERRY TOTALFERRY
SUBWAY1 TOTALSUBWAY
SUBWAY2 TOTALSUBWAY

Join this table to your table by TYPE and group on the SUPERTYPE field.

 

[KARL DEWEY]

First off you have a spreadsheet. It should look like this --
Agency Type Trips TripDate
New York BUS1 5 10/1/08
New York BUS1 4 11/1/08
New York BUS1 6 12/1/08
New York BUS2 4 10/1/08
New York BUS2 9 11/1/08
New York BUS2 10 12/1/08

SELECT Agency, Replace(Replace(Replace([Type], "1", ""),"2",""),"3","") AS
Trip_Type, Sum([Oct]) AS Oct_Trips, Sum([Nov]) AS Nov_Trips, Sum([Dec]) As
Dec_Trips
FROM YourTable
BROUP BY Agency, Replace([Type], "[1-0]", "");

 

[tsmvengy via AccessMonster.com]

Perfect explanation of what I needed to do. Thanks!

I have a table with data like this:

[quoted text clipped - 4 lines]

New York SUBWAY1 10 20 30
New York SUBWAY2 15 18 90

Then you have a spreadsheet, not a properly designed relational table! Storing
data in fieldnames is ALWAYS a bad idea; storing a month name as a fieldname
is even worse (is Dec December 2008? 2009? Both? 1992?)

A properly normalized design would have one *row* per month, or per date:

Agency
Type

I want to sum the categories that go together so I get something like:

[quoted text clipped - 6 lines]

other cities (chicago, LA, etc.)
Is this possible, and how can I do it if so?

Access won't be able to automagically parse your TYPE field, which appears to
be freeform and not from a controlled vocabulary. What you could do is create
a Types table with two fields, TYPE and SUPERTYPE:

TYPE SUPERTYPE
BUS1 TOTALBUS
BUS2 TOTALBUS
FERRY TOTALFERRY
SUBWAY1 TOTALSUBWAY
SUBWAY2 TOTALSUBWAY

Join this table to your table by TYPE and group on the SUPERTYPE field.