Trouble w/ sum function

[Guest]

Ok, here's the problem.

I am trying to sum up in a query the total square footage of a building, by
floor. I have selected the sum function for the "space size" menu, and am
also sorting it by "Room Number" and "Building". It is specified to one
particular building, and the room number is currently sorting the results by
"F1*" to designate only the 1st floor of said room.

What is happening when I run the query is it will not sum up the square
footage into one line, instead keeping it listed as individual entries. (the
space size" is set as a number).

Is there any logical or easy explaination for why this is not working that I
am missing? I can sum this field in a different query I have run, but it will
not work in this instance.

Thanks for any help.

 

[Guest]

to designate only the 1st floor of said room.
Typically how many floors do your rooms have?

The reason you are getting that for results is that you are asking for each
listed as individual entries. Only ask for the level you want to sum at like
building level then Building and sum "space size."

If you want a more granular detail then add the lower levels in the query.

 

[Guest]

If you want the total area for each floor, e.g. if the building has 6 floors
the query will return 6 rows, then you’ll need a way to group it by floor.
If you have a Floor Number column in the table then you’d group by that, but
It sound like the floor numbers might be contained in the room numbers, so if
numbers beginning F1 denote first floor rooms, F2 second floor rooms and so
on then you can group on the second character of the room number. If the
floor numbers exceed 9, however, you’d have a problem and would need some way
of parsing out the floor number from the room numbers. Assuming that’s not
the case a query would go something like this:

SELECT Building, MID([Room Number], 2,1) As Floor,
SUM([Space Size]] AS SquareFootage
FROM YourTable
GROUP BY Building, MID([Room Number], 2,1);

The MID function in the above SQL returns 1 character from Room Number,
starting at the second character.

Ken Sheridan
Stafford, England