Sum a field

[gearhead03]

I want to sum a field in a query without grouping by week, month, year, etc....

The field to sum is "amount"

When I select "sum" on the total line it's not summing all the records in the query, it gives several lines and I am not sure what criteria it's using.

what should I set the other field "total" line to?

Mark A. Kale

 

[fneily]

By your post, it sounds like you have two fields in your query design. One field probably says Group By. So your getting totals for that group. If that's not what you want, delete the field and just sum the one field.

 

[PHV]

Don't select the fields you don't want to group by.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

"I want to sum a field in a query without grouping by..."

If you do not group, then it sums ALL rows that are specified by the criteria.

For instance..
[tt]
Week Amount
1 10
1 20
2 15
2 25
[/tt]
Select Sum(Amount) will sum EVERYTHING, and return 70. Is that waht you want?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[gearhead03]

Great help!!!

Thanks everyone!

I had copied another query to make this but didn't delete the fields I wasn't going to use, so that was causing the issue!

Mark A. Kale

 

[gearhead03]

Another related question.

I would like for it to sum "amount" but less than zero.

When I enter the criteria <0 it determines that I want to give the results of the "Sum of Amount" that is less than zero.

How do I make it only sum the results that are less that zero in the "amount" field?

Mark A. Kale

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gearhead03]

SELECT Checkregister.preclear, Sum(Checkregister.AMOUNT) AS SumOfAMOUNT
FROM Checkregister
GROUP BY Checkregister.preclear
HAVING (((Checkregister.preclear)=Yes));



I would like to make separate queries for deposits and debits based on <0 or >0.

Thanks

Mark A. Kale

 

[cpmasesa]

try:

SELECT Checkregister.preclear, Sum(Checkregister.AMOUNT) AS SumOfAMOUNT
FROM Checkregister
GROUP BY Checkregister.preclear
HAVING Checkregister.preclear=Yes and Checkregister.amount < 0;

 

[PHV]

You may try this:

SELECT preclear, Sum(IIf(AMOUNT<0,AMOUNT,0)) AS Deposits, Sum(IIf(AMOUNT>0,AMOUNT,0)) AS Debits
FROM Checkregister
WHERE preclear=Yes
GROUP BY preclear

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886