Combining Data

[MediaDoc]

This is killing me! It should be so simple, but I am having no luck.

I have a table (for example called "Snacks")with 6 records (always these 6).
1 popcorn
2 chocolate
3 soda pop
4 orange juice
5 water
6 hot dogs

I do queries and reports on these records seperately. But now I want to create a new field called "Drinks" and combine all the values for any record that has either "soda pop, orange juice or water" into a new one called "Drinks"

For example
Query Generates a report / query
Snack ---- Number Sold
Soda Pop 5
Water 11
Orange Juice 2

Total Drinks 18

I just cannot seem to find a way to do this!

Help! (feel free to email me direclty).

Thanks very much,

Steve

 

[Jack Cowley]

In a new column in your query add this to the top line:

Drinks: [SodaPop]+[OrangeJuice]+[Water]

 

[MediaDoc]

Thanks for the reply, I guess I am not explaining this correctly. (This is an example, my actual records / fields are much more relevant!)

I have a table called "Snacks", two fields.
SnackID (primary key)
Snack
(the records contain the actual data "popcorn, chocolate, etc.. etc.."

This table is linked one-many to a larger table that has a drop down box where you can choose one of these 6 types of snacks. There are 10 locations you can buy these snacks, that are fields in a table (North, East, West etc..)

So my query results (use the totals in this query, group by Snack, Sum on Location) in a table that looks like:

Snack...North...East...West...Total..
Soda....3.......2......5......
Water...5.......3......4......

Drinks...8......5......9.....
I need this final line. (whether its in the query or report does not matter to me)

Trying the new field technique you mentioned, resulted in a popup box saying "Enter Paramater Value"... for Soda Pop..

Thanks!

 

[MediaDoc]

Perhaps a fix. How often is it the underlying table structure!

I have created a new field in my snacks table. Called "Snack Type", for OJ, Water and Soda Pop, it has a value of Drinks.

Problem solved. Thanks all who have given their time.