Group by help

[vise]

Hi,
I am having trouble grouping by a certain criteria.
This is my table in a simpler format:

Description | Amount
Hot 3
Cold 4
Freeze 2
Warm 1

Now, I want to group by description, but specifying which categories I want used. ie.

Group by (hot and warm and cold)
so that they say total : 8..

Is this simple to do, I've looked for help on this matter but no place has been clear about this.

tia ~ vise

 

[mp9]

One way:

SELECT YourTableNameHere.Description, YourTableNameHere.Amount, [Description] In ('Hot','Warm','Cold') AS Composite
FROM YourTableNameHere
GROUP BY [Description] In ('Hot','Warm','Cold'), [Description], Amount;

http://www22.brinkster.com/accessory/

 

[dhookom]

I would never hard-code values like this. I would create some method of storing a value that associates Hot, Warm, and Cold. This would be like the product category table in Northwind.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[vise]

Thanks for all of your help. I have decided to parse the data using queries, so I would sum up the different categories that I need. ie. Hot, cold, warm all summed up in one query. Then I can easily do a join query to link them up again. I appreciate all the help I've been receiving from everyone here it's great to see this. I only wish I were knowledgeable enough to contribute back to the board, but most of the time I just end up reading everyone elses educated answers.

 

[mp9]

Dhookom, I would never hard-code variables like this either - it's inevitably going to need changing in the future and that would be painful. My post was just a quick and easy way of suggesting that some means of grouping Hot, Warm and Cold together is required. I entirely agree with your post.