Access 2007 Queries

tsmvengy

OK, I can't figure this out for the life of me. I know I have done it before.

I have the following query result, gathering data from three tables:

City         Lodging Type   Expr1: Sum(June+July+Aug)
New York    Hotel               6
New York    Hostel               8
London       Hotel               10
London       Hostel               4
Paris       Hotel                 6
Paris       Hostel              14

What I want to do is sum the two numbers for guests together, so I end up with a query result like:

City         Expr1: Sum(June+July+Aug)
New York    14
London      14
Paris        20

So I bring up the totals option in query design, the "Expr1" field has to be set as expression, I assume I set City as "group by," but what the hell do I do to make it add Hotels and Hostels together?

tsmvengy

Lock this, I figured it out by editing the SQL - I had to change how the query was selecting out certain records from "SELECT" to "WHILE"

Spherick

Alternatively, unless my SQL is rusty as hell. Adding a GROUP BY clause based on City should group them, in fact - you should have been doing that in the first place with an aggregate function in the SELECT clause.

So it should be like this

SELECT City, Sum(Months)
FROM Table_Name
GROUP BY City

tralev

Right the Group By option is typically hidden in the query designer, you have to hit (I think...) the little sum icon to drop that field in. Probably to keep it from confusing beginner users.

tsmvengy

Yep, I used group by. I had that uncovered in design view but for some reason it wasn't working properly until I messed with it in SQL

Also my initial explanation wasn't complete. The problem was that I also had to select out the Hotel and Hostel numbers from a table that included other numbers, so I had to add:

WHERE Lodging = "Hotel" OR "Hostel"

Anyway, at least now I know for future reference!