Calculations in a query

[BB]

I'm a novice. I have about 150 fields of 5 different types. At the end of
the query I have each of the five types summed. How can I get the totals
without having to "enter" through all of the individual field "parameters?"

 

[pietlinden]

I'm a novice.  I have about 150 fields of 5 different types.  At the end of
the query I have each of the five types summed.  How can I get the totals
without having to "enter" through all of the individual field "parameters?"

150 fields in a table, 30 repeating sets of 5 columns...

Easiest way is to create an insane union query, and use SQL to do it.
Your table is in serious need of redesign. I have done this, and it's
not that hard if you know what you're doing. Do the field names have
consistent names, like
FieldA_1, FieldB_1, FieldC_1, FieldD_1, FieldE_1,
FieldA_2, FieldB_2....

you can do this by looping through the fields collection of the table
and creating a union query.... quick and easy if there are rules that
you can use to determine what goes where. Then once you have the
union query built, you just base the totals query on it.

 

[BB]

I thank you for your response and I apologize, but you're over my head. The
fields have VERY different names. Let me try to explain it some. They are
stops on a park & ride route. Each stop has a count for Adult, Senior,
Mobility Impaired, etc. We have many stops. I have to come up with a daily
total of Adults, Seniors, Mobility Impaired, etc. but could also be asked to
find out how many people are getting on at a particular stop at a particular
time (designed by the route name) for a particular time period. The report
works, but just takes several seconds to hold down the enter button while it
goes through all of the fields.

 

[KARL DEWEY]

Post the first 10 fields and sample data of several records.

 

[John W. Vinson]

I thank you for your response and I apologize, but you're over my head. The
fields have VERY different names. Let me try to explain it some. They are
stops on a park & ride route. Each stop has a count for Adult, Senior,
Mobility Impaired, etc. We have many stops. I have to come up with a daily
total of Adults, Seniors, Mobility Impaired, etc. but could also be asked to
find out how many people are getting on at a particular stop at a particular
time (designed by the route name) for a particular time period. The report
works, but just takes several seconds to hold down the enter button while it
goes through all of the fields.

Your table structure IS WRONG, which is why you're having the problem.

Relational databases are *not spreadsheets* - what you're doing might be ok
for a spreadsheet, but it's incorrect for a database. What will you do if you
add another stop to the route? Redesign your table, redesign all your queries,
redesign all your forms...!? Ouch!

You need *another table* (or perhaps two). A better structure would have
tables like:

Stops
StopNo <primary key, integer or text>
Location <text, address or human-readable, e.g. "Parma Senior Center">

Trips
TripID <primary key, autonumber>
StartDateTime <date/time, when the trip started>
BusNumber
<other info about the trip as a whole>

Categories
Category <Text, Primary Key, e.g. "Adult", "Senior", ...>

PassengerCounts
TripID <link to Trips>
StopNo <link to Stops>
Category <link to Categories>
PassengerCount <integer, how many passengers of this category were picked up
at this stop on this trip>

This gives you a "tall-thin" structure, which will let you use a Totals query
to give you complete flexibility in counts (e.g. "How many seniors were picked
up at grocery stores in July" or "what fraction of the passengers picked up
between 8 and 9 am are handicapped").

 

[BB]

Thank you. I will put some thought into changing things. I knew there was a
better way to do it, just didn't know what it was.