SUM FIELDS TOGETHER IN A TOTAL FIELD

[Sinner]

Hi,

I want to total the sum of 3 fields as mentioned below but I'm unable
to do so.

SELECT DAT.Activity, DAT.[A Amount], DAT.[D Amount], DAT.[W Amount],
[A AMOUNT]+[D AMOUNT]+[W AMOUNT] AS TOTAL
FROM DAT
GROUP BY DAT.Activity, DAT.[A Amount], DAT.[D Amount], DAT.[W Amount],
[A AMOUNT]+[D AMOUNT]+[W AMOUNT]
HAVING (((DAT.Activity) Like "E-L*"))

Is there something missing.

 

[Allen Browne]

Does it give errors? Or a blank result? Or ...?

Perhaps:

SELECT DAT.Activity,
DAT.[A Amount],
DAT.[D Amount],
DAT.[W Amount],
Nz([A AMOUNT],0) + Nz([D AMOUNT],0) + Nz([W AMOUNT],0) AS TOTAL
FROM DAT
GROUP BY DAT.Activity,
DAT.[A Amount],
DAT.[D Amount],
DAT.[W Amount]
HAVING (DAT.Activity Like "E-L*")

 

[Sinner]

Does it give errors? Or a blank result? Or ...?

Perhaps:

SELECT DAT.Activity,
DAT.[A Amount],
DAT.[D Amount],
DAT.[W Amount],
Nz([A AMOUNT],0) + Nz([D AMOUNT],0) + Nz([W AMOUNT],0) AS TOTAL
FROM DAT
GROUP BY DAT.Activity,
DAT.[A Amount],
DAT.[D Amount],
DAT.[W Amount]
HAVING (DAT.Activity Like "E-L*")

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I want to total the sum of 3 fields as mentioned below but I'm unable
to do so.

SELECT DAT.Activity, DAT.[A Amount], DAT.[D Amount], DAT.[W Amount],
[A AMOUNT]+[D AMOUNT]+[W AMOUNT] AS TOTAL
FROM DAT
GROUP BY DAT.Activity, DAT.[A Amount], DAT.[D Amount], DAT.[W Amount],
[A  AMOUNT]+[D AMOUNT]+[W AMOUNT]
HAVING (((DAT.Activity) Like "E-L*"))

Is there something missing.- Hide quoted text -

- Show quoted text -

Hi,

It showed blank but this worked.
Some how I have 792 lines actually but this code is getting only 152.
Why is it ignoring lines?
I'm not getting the actual total.

Thx.

 

[Allen Browne]

Could be several things.

Any row where Activity is null will be eliminated by your criteria.

If Activity contains a hypen, Access may not process it correctly.

Activity would need to start with E-L.

If DAT is a query, the problem could be further down.

Or is it due to the total being calculated after grouping?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Does it give errors? Or a blank result? Or ...?

Perhaps:

SELECT DAT.Activity,
DAT.[A Amount],
DAT.[D Amount],
DAT.[W Amount],
Nz([A AMOUNT],0) + Nz([D AMOUNT],0) + Nz([W AMOUNT],0) AS TOTAL
FROM DAT
GROUP BY DAT.Activity,
DAT.[A Amount],
DAT.[D Amount],
DAT.[W Amount]
HAVING (DAT.Activity Like "E-L*")

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I want to total the sum of 3 fields as mentioned below but I'm unable
to do so.

SELECT DAT.Activity, DAT.[A Amount], DAT.[D Amount], DAT.[W Amount],
[A AMOUNT]+[D AMOUNT]+[W AMOUNT] AS TOTAL
FROM DAT
GROUP BY DAT.Activity, DAT.[A Amount], DAT.[D Amount], DAT.[W Amount],
[A AMOUNT]+[D AMOUNT]+[W AMOUNT]
HAVING (((DAT.Activity) Like "E-L*"))

Is there something missing.- Hide quoted text -

- Show quoted text -

Hi,

It showed blank but this worked.
Some how I have 792 lines actually but this code is getting only 152.
Why is it ignoring lines?
I'm not getting the actual total.

Thx.

 

[Sinner]

Could be several things.

Any row where Activity is null will be eliminated by your criteria.

If Activity contains a hypen, Access may not process it correctly.

Activity would need to start with E-L.

If DAT is a query, the problem could be further down.

Or is it due to the total being calculated after grouping?

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html

Does it give errors? Or a blank result? Or ...?

Perhaps:

SELECT DAT.Activity,
DAT.[A Amount],
DAT.[D Amount],
DAT.[W Amount],
Nz([A AMOUNT],0) + Nz([D AMOUNT],0) + Nz([W AMOUNT],0) AS TOTAL
FROM DAT
GROUP BY DAT.Activity,
DAT.[A Amount],
DAT.[D Amount],
DAT.[W Amount]
HAVING (DAT.Activity Like "E-L*")

news:[email protected]...

I want to total the sum of 3 fields as mentioned below but I'm unable
to do so.
SELECT DAT.Activity, DAT.[A Amount], DAT.[D Amount], DAT.[W Amount],
[A AMOUNT]+[D AMOUNT]+[W AMOUNT] AS TOTAL
FROM DAT
GROUP BY DAT.Activity, DAT.[A Amount], DAT.[D Amount], DAT.[W Amount],
[A AMOUNT]+[D AMOUNT]+[W AMOUNT]
HAVING (((DAT.Activity) Like "E-L*"))
Is there something missing.- Hide quoted text -

- Show quoted text -

Hi,

It showed blank but this worked.
Some how I have 792 lines actually but this code is getting only 152.
Why is it ignoring lines?
I'm not getting the actual total.

Thx.- Hide quoted text -

- Show quoted text -

Yes I checked.
It is due to the total being calculated after grouping. Prior to this
it works fine.
I have used pivot to get what i was looking for but I would appreciate
if we can do this in this query.

Thx for the replies.

 

[Allen Browne]

Try putting:
SUM
in the Total row under the TOTAL field.

If you still can't get it, create a query without grouping, that gives the
TOTAL. Then use that query as an input 'table' for another query that does
the grouping, and use SUM in the Total row under this one.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Could be several things.

Any row where Activity is null will be eliminated by your criteria.

If Activity contains a hypen, Access may not process it correctly.

Activity would need to start with E-L.

If DAT is a query, the problem could be further down.

Or is it due to the total being calculated after grouping?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org."Sinner"

Does it give errors? Or a blank result? Or ...?

Perhaps:

SELECT DAT.Activity,
DAT.[A Amount],
DAT.[D Amount],
DAT.[W Amount],
Nz([A AMOUNT],0) + Nz([D AMOUNT],0) + Nz([W AMOUNT],0) AS TOTAL
FROM DAT
GROUP BY DAT.Activity,
DAT.[A Amount],
DAT.[D Amount],
DAT.[W Amount]
HAVING (DAT.Activity Like "E-L*")

news:[email protected]...

I want to total the sum of 3 fields as mentioned below but I'm unable
to do so.
SELECT DAT.Activity, DAT.[A Amount], DAT.[D Amount], DAT.[W Amount],
[A AMOUNT]+[D AMOUNT]+[W AMOUNT] AS TOTAL
FROM DAT
GROUP BY DAT.Activity, DAT.[A Amount], DAT.[D Amount], DAT.[W Amount],
[A AMOUNT]+[D AMOUNT]+[W AMOUNT]
HAVING (((DAT.Activity) Like "E-L*"))
Is there something missing.- Hide quoted text -

- Show quoted text -

Hi,

It showed blank but this worked.
Some how I have 792 lines actually but this code is getting only 152.
Why is it ignoring lines?
I'm not getting the actual total.

Thx.- Hide quoted text -

- Show quoted text -

Yes I checked.
It is due to the total being calculated after grouping. Prior to this
it works fine.
I have used pivot to get what i was looking for but I would appreciate
if we can do this in this query.

Thx for the replies.

 

[Sinner]

Try putting:
    SUM
in the Total row under the TOTAL field.

If you still can't get it, create a query without grouping, that gives the
TOTAL. Then use that query as an input 'table' for another query that does
the grouping, and use SUM in the Total row under this one.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Could be several things.

Any row where Activity is null will be eliminated by your criteria.

If Activity contains a hypen, Access may not process it correctly.

Activity would need to start with E-L.

If DAT is a query, the problem could be further down.

Or is it due to the total being calculated after grouping?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org."Sinner"
<[email protected]> wrote in message

On Nov 27, 12:30 pm, "Allen Browne" <[email protected]>
wrote:

Does it give errors? Or a blank result? Or ...?
Perhaps:
SELECT DAT.Activity,
DAT.[A Amount],
DAT.[D Amount],
DAT.[W Amount],
Nz([A AMOUNT],0) + Nz([D AMOUNT],0) + Nz([W AMOUNT],0) AS TOTAL
FROM DAT
GROUP BY DAT.Activity,
DAT.[A Amount],
DAT.[D Amount],
DAT.[W Amount]
HAVING (DAT.Activity Like "E-L*")
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I want to total the sum of 3 fields as mentioned below but I'm unable
to do so.
SELECT DAT.Activity, DAT.[A Amount], DAT.[D Amount], DAT.[W Amount],
[A AMOUNT]+[D AMOUNT]+[W AMOUNT] AS TOTAL
FROM DAT
GROUP BY DAT.Activity, DAT.[A Amount], DAT.[D Amount], DAT.[W Amount],
[A AMOUNT]+[D AMOUNT]+[W AMOUNT]
HAVING (((DAT.Activity) Like "E-L*"))
Is there something missing.- Hide quoted text -
- Show quoted text -

Hi,

It showed blank but this worked.
Some how I have 792 lines actually but this code is getting only 152.
Why is it ignoring lines?
I'm not getting the actual total.

Thx.- Hide quoted text -

- Show quoted text -

Yes I checked.
It is due to the total being calculated after grouping. Prior to this
it works fine.
I have used pivot to get what i was looking for but I would appreciate
if we can do this in this query.

Thx for the replies.- Hide quoted text -

- Show quoted text -

Thx.

Worked : )