P
pilch74
I have a complex query coming from just one table of order
transactions (tblTransOrder).
It shows by club and then by Year/Cycle (Yr/Cy) order lines, order
values and the same detail on orders again but split using calculated
fields by 3 different activity levels.
Here's the SQL
SELECT tblOrderTrans.clubno AS Club, Format([curryear],"00")
+"/"+Format([currcycle],"00") AS [Yr/Cy], Count(tblOrderTrans.orderid)
AS [Order Lines], Sum(tblOrderTrans.ordvalue) AS [Order Value],
Count(IIf(tblOrderTrans!ordmembact Like 1,tblOrderTrans!
ordmembact,Null)) AS [Act 1 Order Lines], Sum(IIf(tblOrderTrans!
ordmembact=1,tblOrderTrans!ordvalue,0)) AS [Act 1 Order Value],
Count(IIf(tblOrderTrans!ordmembact Like 2,tblOrderTrans!
ordmembact,Null)) AS [Act 2 Order Lines], Sum(IIf(tblOrderTrans!
ordmembact=2,tblOrderTrans!ordvalue,0)) AS [Act 2 Order Value],
Count(IIf(tblOrderTrans!ordmembact Like 7,tblOrderTrans!
ordmembact,Null)) AS [Act 7 Order Lines], Sum(IIf(tblOrderTrans!
ordmembact=7,tblOrderTrans!ordvalue,0)) AS [Act 7 Order Value],
tblOrderTrans.membno
FROM tblOrderTrans
WHERE (((tblOrderTrans.ordprodgrp)<>"Z"))
GROUP BY tblOrderTrans.clubno, Format([curryear],"00")
+"/"+Format([currcycle],"00"), tblOrderTrans.membno;
That one was comlpex enough (and with some help from GG!) but now onto
the challenge, the second part of this project.
I have been asked for further analysis which will hopefully show
UNIQUE ordering level patterns. So I issue a DISTINCT SQL query (see
below) initially which shows me unique occurrences of membership
numbers within cycles:
SELECT DISTINCT tblOrderTrans.membno,
Format([tblOrderTrans.curryear],"00")
+"/"+Format([tblOrderTrans.currcycle],"00") AS ["Yr/Cy"]
FROM tblOrderTrans
ORDER BY tblOrderTrans.membno;
If you are wondering by now why all the formatting on the curryear &
currcycle it's purely because I didn't realise that Access removes
trailing zeros on Number fields. These fields are actually output
WITH the leading zero's.
What I need to somehow do, and I've been try/failing miserably all
afternoon, is to somehow combine and use the data from both queries.
Please help me - I hope I've made sense.
Regards,
Richard Hellier.
transactions (tblTransOrder).
It shows by club and then by Year/Cycle (Yr/Cy) order lines, order
values and the same detail on orders again but split using calculated
fields by 3 different activity levels.
Here's the SQL
SELECT tblOrderTrans.clubno AS Club, Format([curryear],"00")
+"/"+Format([currcycle],"00") AS [Yr/Cy], Count(tblOrderTrans.orderid)
AS [Order Lines], Sum(tblOrderTrans.ordvalue) AS [Order Value],
Count(IIf(tblOrderTrans!ordmembact Like 1,tblOrderTrans!
ordmembact,Null)) AS [Act 1 Order Lines], Sum(IIf(tblOrderTrans!
ordmembact=1,tblOrderTrans!ordvalue,0)) AS [Act 1 Order Value],
Count(IIf(tblOrderTrans!ordmembact Like 2,tblOrderTrans!
ordmembact,Null)) AS [Act 2 Order Lines], Sum(IIf(tblOrderTrans!
ordmembact=2,tblOrderTrans!ordvalue,0)) AS [Act 2 Order Value],
Count(IIf(tblOrderTrans!ordmembact Like 7,tblOrderTrans!
ordmembact,Null)) AS [Act 7 Order Lines], Sum(IIf(tblOrderTrans!
ordmembact=7,tblOrderTrans!ordvalue,0)) AS [Act 7 Order Value],
tblOrderTrans.membno
FROM tblOrderTrans
WHERE (((tblOrderTrans.ordprodgrp)<>"Z"))
GROUP BY tblOrderTrans.clubno, Format([curryear],"00")
+"/"+Format([currcycle],"00"), tblOrderTrans.membno;
That one was comlpex enough (and with some help from GG!) but now onto
the challenge, the second part of this project.
I have been asked for further analysis which will hopefully show
UNIQUE ordering level patterns. So I issue a DISTINCT SQL query (see
below) initially which shows me unique occurrences of membership
numbers within cycles:
SELECT DISTINCT tblOrderTrans.membno,
Format([tblOrderTrans.curryear],"00")
+"/"+Format([tblOrderTrans.currcycle],"00") AS ["Yr/Cy"]
FROM tblOrderTrans
ORDER BY tblOrderTrans.membno;
If you are wondering by now why all the formatting on the curryear &
currcycle it's purely because I didn't realise that Access removes
trailing zeros on Number fields. These fields are actually output
WITH the leading zero's.
What I need to somehow do, and I've been try/failing miserably all
afternoon, is to somehow combine and use the data from both queries.
Please help me - I hope I've made sense.
Regards,
Richard Hellier.