DISTINCT records when combined with another query.

[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.

 

[Guest]

hi,

you have the tblOrderTrans.membno field in both queries. You can create a
new query joing these two queries on tblOrderTrans.membno.

Regards,

 

[pilch74]

hi,

you have the tblOrderTrans.membno field in both queries. You can create a
new query joing these two queries on tblOrderTrans.membno.

Regards,
-----------------------------------------http://www.Designing-Systems.com
Email. (e-mail address removed)
-------------------------------------------------

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.

Richard Hellier.

Hi.

I found that the record counts went completely bonkers when joining
TWO querries so I'm closing this topic, and approaching and re-wording
from another angle.

TOPIC CLOSED...

rewriting to Google Groups.. New Subject of new post = "DISTINCT
Records / Calculated fields challenge.."