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

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
Sumoftotal1) 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

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)


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"yupyupxav" wrote in message
...
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
Sumoftotal1) 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

Access keeps asking me what is S.totalAmt



On Sun, 14 Dec 2008 10:09:16 -0500, "Douglas J. Steele"
wrote:

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)

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
http://I.Am/DougSteele
(no private e-mails, please)


"yupyupxav" wrote in message
...
Access keeps asking me what is S.totalAmt



On Sun, 14 Dec 2008 10:09:16 -0500, "Douglas J. Steele"
wrote:

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)

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

Xav

On Sun, 14 Dec 2008 13:24:56 -0500, "Douglas J. Steele"
wrote:

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)