[acess2003][yupyupxav] Grouping low values in a sum

[yupyupxav]

Hello all,

I have a table with a field
designator : text
total : date (where I store a duration, always smaller than 24h)

I want to do a sum by designator, so far no problems but I want to
group small values (here designators with sums less than 24h=1)

i therefore use this syntax for my query:

SELECT DISTINCTROW IIf(Sum(total)>1,designator,"other") AS D,
Sum([QFlights.total]) AS sumofTotal
FROM qflights
GROUP BY qflights.Designator;


My problem is that I get several results with "Other" as designator,
and I'd like access to add all small values (other thus with a
Sumoftotal<1) in a single designator "Other"

i have thus

D ! SumOfTotal
------------------------------
S61 ! 2,3
Other! 0,65
F260 ! 19,45
E121 ! 20,46
Other ! 0,23
Other! 0,96
AJET ! 40,34


I want thus access to group all values "Other" and give the result
(0.65+0.23+0.96)


Is this feasible?

Thanks!

Xav

 

[Douglas J. Steele]

Try:

SELECT IIf(S.TotalAmt <=1, "other", S.Designator) AS D,
Sum(S.TotalAmt) AS sumofTotal
FROM
(
SELECT [designator],
Sum([QFlights.total]) AS sumofTotal
FROM qflights
GROUP BY qflights.Designator) AS S
GROUP BY IIf(S.TotalAmt <=1, "other", S.Designator)

 

[yupyupxav]

Access keeps asking me what is S.totalAmt

 

[Douglas J. Steele]

Sorry, my typo. Forgot to change the field name.

SELECT IIf(S.TotalAmt <=1, "other", S.Designator) AS D,
Sum(S.TotalAmt) AS sumofTotal
FROM
(
SELECT [designator],
Sum([QFlights.total]) AS TotalAmt
FROM qflights
GROUP BY qflights.Designator) AS S
GROUP BY IIf(S.TotalAmt <=1, "other", S.Designator)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Access keeps asking me what is S.totalAmt

Try:

SELECT IIf(S.TotalAmt <=1, "other", S.Designator) AS D,
Sum(S.TotalAmt) AS sumofTotal
FROM
(
SELECT [designator],
Sum([QFlights.total]) AS sumofTotal
FROM qflights
GROUP BY qflights.Designator) AS S
GROUP BY IIf(S.TotalAmt <=1, "other", S.Designator)

 

[yupyupxav]

Thank you very much douglas. This makes the Job. You're a genius.

Xav