Using one calculation in another

[BarbaraP]

I have only one table that I am working with. I have space for entry of make
and model for up to four vehicles (Vehicle1, Vehicle2, Vehicle3, and
Vehicle4). In a report, I need a total number of vehicles entered. I did a
count of each vehicle:

TotalV1: Vehicle1 (count), TotalV2: Vehicle2 (count), TotalV3: Vehicle3
(count), and TotalV4: Vehicle4 (count).

I then created a TOTALVEHICLES: sum([TotalV1]+[TotalV2]+[TotalV3]+[TotalV4].

If I leave the "Show" checked for the individual "totals," I am told that
there are no fields that can be added to my report. When I return to the
quiry and uncheck the "Show" I get the field TOTALVEHICLES, but when I add it
to the report, save, and run the report, it asks me for TotalV1, TotalV2,
etc. When I go back to the quiry, I see that the fields TotalV1, TotalV2,
etc. are no longer there.

This may be very basic, but I'm lost as to how to get it to count the four
fields through all records and then sum the counts.

Thanks for any help.

 

[KARL DEWEY]

TOTALVEHICLES: sum([TotalV1]+[TotalV2]+[TotalV3]+[TotalV4].
You can not use alaises in the same query that created them. Use the raw
data (actual field names from the table) in the calculation.

Your table structure is wrong as it is like a spreadsheet. Use a separate
record for each vehicle.

 

[KenSheridan via AccessMonster.com]

With your current table structure, you'd add the counts together rather than
summing them. Addition and summation are not the same thing. So in the
'field' row of a blank column in query design view put:

TOTALVEHICLES:Count([Vehicle1])+Count([Vehicle2])+Count([Vehicle3])+Count(
[Vehicle4])

However, as Karl has pointed out, the table design is flawed. Your table is
more like a spreadsheet that a relational database table. A correct design
would be to have the vehicles in a separate related table with 2 columns such
as MyID and Vehicle, where MyID is a foreign key referencing the primary key
of the main table. You can then join them in a query on MyID, group the
query on the columns from the main table and the Vehicle column from the
related table. Then you just have to COUNT(*) to get the count per vehicle
per group, e.g.

SELECT [Maintable].[SomeField], [Maintable].[SomeOtherField],
[Vehicles].[Vehicle], COUNT(*) As [Vehicle Count]
FROM [MainTable] INNER JOIN [Vehicles]
ON [MainTable].[MyID] = [Vehicles].[MyID]
GROUP BY [Maintable].[SomeField], [Maintable].[SomeOtherField],
[Vehicles].[Vehicle];

That will give you the count per vehicle. To also return a total count you
can add a subquery:

SELECT [Maintable].[SomeField], [Maintable].[SomeOtherField],
[Vehicles].[Vehicle], COUNT(*) As [Vehicle Count],
(SELECT COUNT(*)
FROM [Vehicles]) AS [TOTALVEHICLES]
FROM [MainTable] INNER JOIN [Vehicles]
ON [MainTable].[MyID] = [Vehicles].[MyID]
GROUP BY [Maintable].[SomeField], [Maintable].[SomeOtherField],
[Vehicles].[Vehicle];

Even then you still have a flaw in the design as you are storing both the
make and model of vehicle in one column. You really should have only a Model
column in Vehicles and separate tables for Makes and Models, the latter
including a foreign key column which references the primary key of the Makes
table. The Model column in your Vehicles table will then be a foreign key
referencing the key of the Models table. You can then join the all the
tables in the query and return the make and model in separate columns.

If storing data in related tables in this way is new to you, you might think
about getting familiar with the basics of how relational databases are put
together. A good start would be Crystal's tutorial at:

http://www.accessmvp.com/Strive4Peace

Ken Sheridan
Stafford, England

I have only one table that I am working with. I have space for entry of make
and model for up to four vehicles (Vehicle1, Vehicle2, Vehicle3, and
Vehicle4). In a report, I need a total number of vehicles entered. I did a
count of each vehicle:

TotalV1: Vehicle1 (count), TotalV2: Vehicle2 (count), TotalV3: Vehicle3
(count), and TotalV4: Vehicle4 (count).

I then created a TOTALVEHICLES: sum([TotalV1]+[TotalV2]+[TotalV3]+[TotalV4].

If I leave the "Show" checked for the individual "totals," I am told that
there are no fields that can be added to my report. When I return to the
quiry and uncheck the "Show" I get the field TOTALVEHICLES, but when I add it
to the report, save, and run the report, it asks me for TotalV1, TotalV2,
etc. When I go back to the quiry, I see that the fields TotalV1, TotalV2,
etc. are no longer there.

This may be very basic, but I'm lost as to how to get it to count the four
fields through all records and then sum the counts.

Thanks for any help.