Summing Values from a table

[jtabb]

I have a table that gives detailed information about what is located in certain rooms of a large building. It includes columns of positive and negative values for each entry. For each room number, the values, when summed, should equal zero.

How can I create a query to sum the values in each specific room number and check that they equal zero?

 

[dhookom]

Could you please take the time to type in a few sample records with the proper field names and table name? We only need to see some of the fields that are relevant.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jtabb]

I'm sorry for the confusion, I wrote a wrong piece of information in my question, the sums should equal eachother, meaning the difference would be zero. Here are sample values in the table named Terminal Units:

Rm# L/S(max) L/S(min)
1.16 -445 -240
1.16 350 145
1.20 -560 -150
1.20 695 285
etc.

for room 1.16: -445+350=-95 and -240+145=-95
-95-(-95)= 0 so it is correct

for room 1.20: -560+695=135 and -150+285=135
135-135= 0 so it is correct

So I would like a query to do this checking process for me (I have over 100 rooms). I hope this clears it up for you. Thanks a lot.

 

[lespaul]

SELECT [RM#], (SUM([L/S(max)]) + SUM([L/S(min)])) As TotalSum FROM TerminalUnits
GROUP BY [RM#]

Leslie

 

[jtabb]

SELECT [RM#], (SUM([L/S(max)]) + SUM([L/S(min)])) As TotalSum FROM TerminalUnits
GROUP BY [RM#]

sums the min and max, I need to sum 2 max's together and 2 min's together. (See my above example and how I added them together for rooms 1.16 and 1.20)

 

[lespaul]

How can I create a query to sum the values

Ok, this query should give you the sum of the max, the sum of the min and the difference between the two (not the sum of the two):

SELECT [RM#], SUM([L/S(max)]) As SumOfMax, SUM([L/S(min)]) As SumOfMin, (SUM([L/S(max)]) - SUM([L/S(min)])) As DifferenceOfTotals FROM TerminalUnits
GROUP BY [RM#]



Leslie

 

[PHV]

How can I create a query to sum the values in each specific room number and check that they equal zero?
To get the rooms in error:
SELECT [RM#], SUM([L/S(max)]) As LS_max, SUM([L/S(min)]) As LS_min FROM TerminalUnits
GROUP BY [RM#]
HAVING SUM([L/S(max)]) <> SUM([L/S(min)]);


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244