Calculated fields: Display Sum() of field X where FIELD Y is blah - please help

[pilch74]

Hello.

I've had to retire beaten for today. I've been trawling GG's
endlessly but I'm not getting anywhere with this so I'm resorting to
posting here. Please help me if you can, I'd really appreciate it

Basically, what I am looking to do is to show by club and where
product group <> Z

Order counts per activity which is [ordmembact] 1,2 and 7 being the
values I want to report on.
Then I want to show SUMs of the ordervalues [ordvalue] per
[ordmembact]. Again 1,2 and 7 being the values.

I already have a collection of calculated fields and I'm showing this
across clubs.

Below is my query so far but I just can't sort the SUM out.

SELECT tblOrderTrans.clubno AS Club, Count(IIf([tblOrderTrans]!
[ordmembact] Like 1,[tblOrderTrans]![ordmembact],Null)) AS [Act 1
Orders], Count(IIf([tblOrderTrans]![ordmembact] Like 2,[tblOrderTrans]!
[ordmembact],Null)) AS [Act 2 Orders], Count(IIf([tblOrderTrans]!
[ordmembact] Like 7,[tblOrderTrans]![ordmembact],Null)) AS [Act 7
Orders]
FROM tblOrderTrans
WHERE (((tblOrderTrans.ordprodgrp)<>"Z"))
GROUP BY tblOrderTrans.clubno;


Many thanks in advance and I hope this makes sense.

Regards,

Richard Hellier.

 

[KARL DEWEY]

Does this do it for you ---
SELECT tblOrderTrans.clubno AS Club,
Count(IIf([tblOrderTrans]![ordmembact]=1,[tblOrderTrans]![ordmembact],Null))
& " - " &
Sum(IIf([tblOrderTrans]![ordmembact]=1,[tblOrderTrans]![ordvalue],0)) AS [Act
1 Orders],
Count(IIf([tblOrderTrans]![ordmembact]=2,[tblOrderTrans]![ordmembact],Null))
& " - " &
Sum(IIf([tblOrderTrans]![ordmembact]=2,[tblOrderTrans]![ordvalue],0)) AS [Act
2 Orders], Count(IIf([tblOrderTrans]![ordmembact] Like
7,[tblOrderTrans]![ordmembact],Null)) & " - " &
Sum(IIf([tblOrderTrans]![ordmembact]=7,[tblOrderTrans]![ordvalue],0)) AS [Act
7 Orders]
FROM tblOrderTrans
WHERE (((tblOrderTrans.ordprodgrp)<>"Z"))
GROUP BY tblOrderTrans.clubno;

 

[pilch74]

Does this do it for you ---
SELECT tblOrderTrans.clubno AS Club,
Count(IIf([tblOrderTrans]![ordmembact]=1,[tblOrderTrans]![ordmembact],Null))
& " - " &
Sum(IIf([tblOrderTrans]![ordmembact]=1,[tblOrderTrans]![ordvalue],0)) AS [Act
1 Orders],
Count(IIf([tblOrderTrans]![ordmembact]=2,[tblOrderTrans]![ordmembact],Null))
& " - " &
Sum(IIf([tblOrderTrans]![ordmembact]=2,[tblOrderTrans]![ordvalue],0)) AS [Act
2 Orders], Count(IIf([tblOrderTrans]![ordmembact] Like
7,[tblOrderTrans]![ordmembact],Null)) & " - " &
Sum(IIf([tblOrderTrans]![ordmembact]=7,[tblOrderTrans]![ordvalue],0)) AS [Act
7 Orders]
FROM tblOrderTrans
WHERE (((tblOrderTrans.ordprodgrp)<>"Z"))
GROUP BY tblOrderTrans.clubno;

--
KARL DEWEY
Build a little - Test a little

Hello.

I've had to retire beaten for today. I've been trawling GG's
endlessly but I'm not getting anywhere with this so I'm resorting to
posting here. Please help me if you can, I'd really appreciate it

Basically, what I am looking to do is to show by club and where
product group <> Z

Order counts per activity which is [ordmembact] 1,2 and 7 being the
values I want to report on.
Then I want to show SUMs of the ordervalues [ordvalue] per
[ordmembact]. Again 1,2 and 7 being the values.

I already have a collection of calculated fields and I'm showing this
across clubs.

Below is my query so far but I just can't sort the SUM out.

SELECT tblOrderTrans.clubno AS Club, Count(IIf([tblOrderTrans]!
[ordmembact] Like 1,[tblOrderTrans]![ordmembact],Null)) AS [Act 1
Orders], Count(IIf([tblOrderTrans]![ordmembact] Like 2,[tblOrderTrans]!
[ordmembact],Null)) AS [Act 2 Orders], Count(IIf([tblOrderTrans]!
[ordmembact] Like 7,[tblOrderTrans]![ordmembact],Null)) AS [Act 7
Orders]
FROM tblOrderTrans
WHERE (((tblOrderTrans.ordprodgrp)<>"Z"))
GROUP BY tblOrderTrans.clubno;

Many thanks in advance and I hope this makes sense.

RichardHellier.

Hey Karl.

Thanks for your help with this.

I've replaced my SQL with yours and I get the message

"Invalid bracketing of name 'Act 1 Orders'



Regards.

Richard

 

[John Spencer]

I would just use a query like the following.

SELECT tblOrderTrans.clubno AS Club
, OrdMembAct as ActNumber
, Count([ordmembact]) as MemCount
, Sum(ordvalue) as OrderValue
FROM tblOrderTrans
WHERE (((tblOrderTrans.ordprodgrp)<>"Z")) and OrdMembAct in (1,2,7)
GROUP BY tblOrderTrans.clubno, OrdMembAct;

You can use that query as the source for a report or form.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Does this do it for you ---
SELECT tblOrderTrans.clubno AS Club,
Count(IIf([tblOrderTrans]![ordmembact]=1,[tblOrderTrans]![ordmembact],Null))
& " - " &
Sum(IIf([tblOrderTrans]![ordmembact]=1,[tblOrderTrans]![ordvalue],0)) AS
[Act
1 Orders],
Count(IIf([tblOrderTrans]![ordmembact]=2,[tblOrderTrans]![ordmembact],Null))
& " - " &
Sum(IIf([tblOrderTrans]![ordmembact]=2,[tblOrderTrans]![ordvalue],0)) AS
[Act
2 Orders], Count(IIf([tblOrderTrans]![ordmembact] Like
7,[tblOrderTrans]![ordmembact],Null)) & " - " &
Sum(IIf([tblOrderTrans]![ordmembact]=7,[tblOrderTrans]![ordvalue],0)) AS
[Act
7 Orders]
FROM tblOrderTrans
WHERE (((tblOrderTrans.ordprodgrp)<>"Z"))
GROUP BY tblOrderTrans.clubno;

--
KARL DEWEY
Build a little - Test a little

Hello.

I've had to retire beaten for today. I've been trawling GG's
endlessly but I'm not getting anywhere with this so I'm resorting to
posting here. Please help me if you can, I'd really appreciate it

Basically, what I am looking to do is to show by club and where
product group <> Z

Order counts per activity which is [ordmembact] 1,2 and 7 being the
values I want to report on.
Then I want to show SUMs of the ordervalues [ordvalue] per
[ordmembact]. Again 1,2 and 7 being the values.

I already have a collection of calculated fields and I'm showing this
across clubs.

Below is my query so far but I just can't sort the SUM out.

SELECT tblOrderTrans.clubno AS Club, Count(IIf([tblOrderTrans]!
[ordmembact] Like 1,[tblOrderTrans]![ordmembact],Null)) AS [Act 1
Orders], Count(IIf([tblOrderTrans]![ordmembact] Like 2,[tblOrderTrans]!
[ordmembact],Null)) AS [Act 2 Orders], Count(IIf([tblOrderTrans]!
[ordmembact] Like 7,[tblOrderTrans]![ordmembact],Null)) AS [Act 7
Orders]
FROM tblOrderTrans
WHERE (((tblOrderTrans.ordprodgrp)<>"Z"))
GROUP BY tblOrderTrans.clubno;

Many thanks in advance and I hope this makes sense.

RichardHellier.

Hey Karl.

Thanks for your help with this.

I've replaced my SQL with yours and I get the message

"Invalid bracketing of name 'Act 1 Orders'



Regards.

Richard

 

[pilch74]

Does this do it for you ---
SELECT tblOrderTrans.clubno AS Club,
Count(IIf([tblOrderTrans]![ordmembact]=1,[tblOrderTrans]![ordmembact],Null))
& " - " &
Sum(IIf([tblOrderTrans]![ordmembact]=1,[tblOrderTrans]![ordvalue],0)) AS [Act
1 Orders],
Count(IIf([tblOrderTrans]![ordmembact]=2,[tblOrderTrans]![ordmembact],Null))
& " - " &
Sum(IIf([tblOrderTrans]![ordmembact]=2,[tblOrderTrans]![ordvalue],0)) AS [Act
2 Orders], Count(IIf([tblOrderTrans]![ordmembact] Like
7,[tblOrderTrans]![ordmembact],Null)) & " - " &
Sum(IIf([tblOrderTrans]![ordmembact]=7,[tblOrderTrans]![ordvalue],0)) AS [Act
7 Orders]
FROM tblOrderTrans
WHERE (((tblOrderTrans.ordprodgrp)<>"Z"))
GROUP BY tblOrderTrans.clubno;

--
KARL DEWEY
Build a little - Test a little

Hello.

I've had to retire beaten for today. I've been trawling GG's
endlessly but I'm not getting anywhere with this so I'm resorting to
posting here. Please help me if you can, I'd really appreciate it

Basically, what I am looking to do is to show by club and where
product group <> Z

Order counts per activity which is [ordmembact] 1,2 and 7 being the
values I want to report on.
Then I want to show SUMs of the ordervalues [ordvalue] per
[ordmembact]. Again 1,2 and 7 being the values.

I already have a collection of calculated fields and I'm showing this
across clubs.

Below is my query so far but I just can't sort the SUM out.

SELECT tblOrderTrans.clubno AS Club, Count(IIf([tblOrderTrans]!
[ordmembact] Like 1,[tblOrderTrans]![ordmembact],Null)) AS [Act 1
Orders], Count(IIf([tblOrderTrans]![ordmembact] Like 2,[tblOrderTrans]!
[ordmembact],Null)) AS [Act 2 Orders], Count(IIf([tblOrderTrans]!
[ordmembact] Like 7,[tblOrderTrans]![ordmembact],Null)) AS [Act 7
Orders]
FROM tblOrderTrans
WHERE (((tblOrderTrans.ordprodgrp)<>"Z"))
GROUP BY tblOrderTrans.clubno;

Many thanks in advance and I hope this makes sense.

RichardHellier.

I am replying to this again (even though my initial response to Karl
was posted over 4 hours ago and is still not showing up for some
reason). So for the benefit of that I am writing initially to say
that after running your suggested SQL I received a msg about invalid
bracketing. I replaced spaces with underscores of the elements within
the HAVING clauses and it ran through a treat.

I also was AMAZED that you could create a two-part calculated field by
seperating out the elements the way you did by concatenation. This
was cool, however not what I really after for this project.

Anyway, after meddling about all morning here's how the query looks
now:

SELECT tblOrderTrans.clubno AS Club, Count(tblOrderTrans.orderid) AS
Quantity, Sum(tblOrderTrans.ordvalue) AS [Order value],
Count(IIf(tblOrderTrans!ordmembact Like 1,tblOrderTrans!
ordmembact,Null)) AS [Act 1], Sum(IIf(tblOrderTrans!
ordmembact=1,tblOrderTrans!ordvalue,0)) AS [Act 1 Orders Summary],
Count(IIf(tblOrderTrans!ordmembact Like 2,tblOrderTrans!
ordmembact,Null)) AS [Act 2], Sum(IIf(tblOrderTrans!
ordmembact=2,tblOrderTrans!ordvalue,0)) AS [Act 2 Orders Summary],
Count(IIf(tblOrderTrans!ordmembact Like 7,tblOrderTrans!
ordmembact,Null)) AS [Act 7], Sum(IIf(tblOrderTrans!
ordmembact=7,tblOrderTrans!ordvalue,0)) AS [Act 7 Orders Summary]
FROM tblOrderTrans
GROUP BY tblOrderTrans.clubno;

This shows me club, total quantity count, total order value, act 1
order count, act 1 orders summary, act 2 order count, act 2 orders
summary, and act 7 order count & act 7 orders summary.

It works like a charm.

Many, many thanks for your help KARL, you made it happen..

I was a little gutted that I was soooo close to the correct syntax!

Regards,

Richard Hellier.