DISTINCT Records / Calculated fields challenge..

[pilch74]

I have a query which uses some calculated fields.

Here is a data sample after running the query ::

-----------------------------------------------------------------------------------
| Club | Yr/Cy | Order Lines | Order Value | Act 1 Order Lines | Act
1Order Value |
-----------------------------------------------------------------------------------
| 43 | 07/02 | 1563 | £11,933.39 | 1084
| £6,097.03 |
-----------------------------------------------------------------------------------
| 60 | 07/03 | 5423 | £48,713.86 | 3889 |
£29,664.38 |
-----------------------------------------------------------------------------------
| 64 | 07/03 | 17468 | £164,285.85 | 14089 |
£131,195.94 |
-----------------------------------------------------------------------------------
**cont.** to avoid wordwrap
-------------------------------------------------------------------------------------------------
| Act 2 Order Lines | Act 2 Order Value | Act 7 Order
Lines | Act 7 Order Value |
-------------------------------------------------------------------------------------------------
| 62 | £716.89 |
417 | £5,119.47 |
-------------------------------------------------------------------------------------------------
| 223 | £2,631.47 |
1310 | £16,405.27 |
-------------------------------------------------------------------------------------------------
| 2527 | £29,436.01 |
849 | £3,606.98 |
-------------------------------------------------------------------------------------------------

Here is the SQL responsible.

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]
FROM tblOrderTrans
WHERE (((tblOrderTrans.ordprodgrp)<>"Z"))
GROUP BY tblOrderTrans.clubno, Format([curryear],"00")
+"/"+Format([currcycle],"00");

--------------------------------------------------------------------------------------------------

How do I retain all the info. that's there already but to somehow find
the unique (DISTINCT) orders than simply a count.

Here's the field description of tblOrderTrans:

Name Type Size
orderid Long Integer 4
rectype Text 10
orddate Date/Time 8
membno Text 8
clubno Long Integer 4
currcycle Long Integer 4
curryear Long Integer 4
orditem Text 4
ordtitle Text 32
ordcategory Text 3
ordvalue Double 8
ordprodgrp Text 1
ordsrc Text 1
orderno Long Integer 4
ordpremprodflag Text 2
ordrescode Long Integer 4
ordmembstatus Long Integer 4
ordmembact Long Integer 4
ordcountcommit Long Integer 4
ordvatcode Long Integer 4

I appreciate any assistance in advance and I hope that I have spent
enough time formatting and providing as much info as possible in this
post..

Regards,

Richard Hellier

p.s This post surpasses
http://groups.google.com/group/micr...55ae9efdb7546439/?hl=en&#doc_92ef2b80ec194395