Sum expression over two records

[tsmvengy]

I have a query result datasheet with the following columns

State City Company Type Expr1:Sum([Jun]+[Jul]+[Aug])
CA Bakersfield XYZ Product1 5
CA Bakersfield XYZ Product2 10


The first four columns are text, the last is a number. What I want to do is
combine the values in column 5 for Product1 and Product 2, so I can figure
out how much of both together were produced in June, July, and August
combined.

 

[sahafi]

One way is to create a report out of your query, then add a box to total the
values in that field.

Thanks.

 

[tsmvengy]

Thanks, but the table has multiple cities (Bakersfield, LA, etc.) so I'd need
more thank just a total, I need a whole new table with the combined value for
each city.

One way is to create a report out of your query, then add a box to total the
values in that field.

Thanks.
--
when u change the way u look @ things, the things u look at change.

I have a query result datasheet with the following columns

State City Company Type Expr1:Sum([Jun]+[Jul]+[Aug])
CA Bakersfield XYZ Product1 5
CA Bakersfield XYZ Product2 10


The first four columns are text, the last is a number. What I want to do is
combine the values in column 5 for Product1 and Product 2, so I can figure
out how much of both together were produced in June, July, and August
combined.

 

[sahafi]

Not sure if I understood your request correctly, but if you only interested
in showing total per city/state regardless of what product/company, you could
just run a simple select query that shows: State, City, ProductSum(last
field), group by state/city and sum the last field. Again, i'm not sure if
that what you needed.

Thanks.
--
when u change the way u look @ things, the things u look at change.

Thanks, but the table has multiple cities (Bakersfield, LA, etc.) so I'd need
more thank just a total, I need a whole new table with the combined value for
each city.

One way is to create a report out of your query, then add a box to total the
values in that field.

Thanks.
--
when u change the way u look @ things, the things u look at change.

I have a query result datasheet with the following columns

State City Company Type Expr1:Sum([Jun]+[Jul]+[Aug])
CA Bakersfield XYZ Product1 5
CA Bakersfield XYZ Product2 10


The first four columns are text, the last is a number. What I want to do is
combine the values in column 5 for Product1 and Product 2, so I can figure
out how much of both together were produced in June, July, and August
combined.

 

[tsmvengy]

Yep, you're getting it right, sorry I wasn't clear on one more thing:

A more detailed look at the table:

State City Company Type Expr1:Sum([Jun]+[Jul]+[Aug])
CA Bakersfield XYZ Product1 5
CA Bakersfield XYZ Product2 10
CA Bakersfield XYZ Product3 15
CA Los Angeles XYZ Product1 12
CA Los Angeles XYZ Product2 3
CA Los Angeles XYZ Product3 14

I want to select out product 1 and 2 and add those together to get a table
like:

State City Company Type Expr1:Sum([Jun]+[Jul]+[Aug])
CA Bakersfield XYZ Product1&2 15
CA Los Angeles XYZ Product1&2 15

So I would use your method BUT I have to exclude product3 from the sum.

Not sure if I understood your request correctly, but if you only interested
in showing total per city/state regardless of what product/company, you could
just run a simple select query that shows: State, City, ProductSum(last
field), group by state/city and sum the last field. Again, i'm not sure if
that what you needed.

Thanks.
--
when u change the way u look @ things, the things u look at change.

Thanks, but the table has multiple cities (Bakersfield, LA, etc.) so I'd need
more thank just a total, I need a whole new table with the combined value for
each city.

One way is to create a report out of your query, then add a box to total the
values in that field.

Thanks.
--
when u change the way u look @ things, the things u look at change.


:

I have a query result datasheet with the following columns

State City Company Type Expr1:Sum([Jun]+[Jul]+[Aug])
CA Bakersfield XYZ Product1 5
CA Bakersfield XYZ Product2 10


The first four columns are text, the last is a number. What I want to do is
combine the values in column 5 for Product1 and Product 2, so I can figure
out how much of both together were produced in June, July, and August
combined.

 

[John Spencer]

THen you add a where clause to specify which types you want

Add Type to the field list of your query\
Select WHERE as the TOTAL
Enter the following in the WHERE BLOCK under Type
in ("Product1", "Product2")

Or if you want to exclude use
Not In ("Product3","product454")


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Yep, you're getting it right, sorry I wasn't clear on one more thing:

A more detailed look at the table:

State City Company Type Expr1:Sum([Jun]+[Jul]+[Aug])
CA Bakersfield XYZ Product1 5
CA Bakersfield XYZ Product2 10
CA Bakersfield XYZ Product3 15
CA Los Angeles XYZ Product1 12
CA Los Angeles XYZ Product2 3
CA Los Angeles XYZ Product3 14

I want to select out product 1 and 2 and add those together to get a table
like:

State City Company Type Expr1:Sum([Jun]+[Jul]+[Aug])
CA Bakersfield XYZ Product1&2 15
CA Los Angeles XYZ Product1&2 15

So I would use your method BUT I have to exclude product3 from the sum.

Not sure if I understood your request correctly, but if you only interested
in showing total per city/state regardless of what product/company, you could
just run a simple select query that shows: State, City, ProductSum(last
field), group by state/city and sum the last field. Again, i'm not sure if
that what you needed.

Thanks.
--
when u change the way u look @ things, the things u look at change.

Thanks, but the table has multiple cities (Bakersfield, LA, etc.) so I'd need
more thank just a total, I need a whole new table with the combined value for
each city.

:

One way is to create a report out of your query, then add a box to total the
values in that field.

Thanks.
--
when u change the way u look @ things, the things u look at change.


:

I have a query result datasheet with the following columns

State City Company Type Expr1:Sum([Jun]+[Jul]+[Aug])
CA Bakersfield XYZ Product1 5
CA Bakersfield XYZ Product2 10


The first four columns are text, the last is a number. What I want to do is
combine the values in column 5 for Product1 and Product 2, so I can figure
out how much of both together were produced in June, July, and August
combined.

 

[John W. Vinson]

Yep, you're getting it right, sorry I wasn't clear on one more thing:

A more detailed look at the table:

State City Company Type Expr1:Sum([Jun]+[Jul]+[Aug])
CA Bakersfield XYZ Product1 5
CA Bakersfield XYZ Product2 10
CA Bakersfield XYZ Product3 15
CA Los Angeles XYZ Product1 12
CA Los Angeles XYZ Product2 3
CA Los Angeles XYZ Product3 14

I want to select out product 1 and 2 and add those together to get a table
like:

State City Company Type Expr1:Sum([Jun]+[Jul]+[Aug])
CA Bakersfield XYZ Product1&2 15
CA Los Angeles XYZ Product1&2 15

So I would use your method BUT I have to exclude product3 from the sum.

Simply use a criterion on the query

<> "Product2"

and group by product as well.

Note that you do NOT need "a new table" - any of these totals should be
calculated dynamically in a query, not stored in a table. You can use that
Query as the basis of a report or form.