count summary question

[Guest]

I have a query that returns a list of transactions and dates and currencies
(plus various other fields) . (amount, ccy, sdate etc)
I am trying to buid query that tells me the count of all transactions that
are within the last 30 days, split by ccy.

I'm using the count(*), and date()-30 to get an overall result, but i cant
seem to get one query that will give me this seeming simple thing!!!]

any help greatly appreciated,

 

[JK]

There maybe a shorter way

Creat a qurey say q Trans List Witha dummy field say, ccyCount="Count"

SELECT tblTrans.sDate, tblTrans.ccy, "Count" AS ccyCount, tblTrans.Amount
FROM tblTrans
WHERE (((tblTrans.sDate)>=Date()-30 And (tblTrans.sDate)<=Date()))
ORDER BY tblTrans.sDate;

Then Creat a CrossTab query

TRANSFORM Count([q Trans List].Amount) AS CountOfAmount
SELECT [q Trans List].ccy
FROM [q Trans List]
GROUP BY [q Trans List].ccy
PIVOT [q Trans List].ccyCount;

Regards/JK

 

[Larry Daugherty]

In the criteria line for the date field you want something like
Between [Enter Start Date] and [Enter End Date]

HTH

 

[JK]

Larry,

Arran already said that he want to see the last 30 days, therefore the
prompts are not required, The WHERE clause above covers this

Regards/JK

In the criteria line for the date field you want something like
Between [Enter Start Date] and [Enter End Date]

HTH
--
-Larry-
--

I have a query that returns a list of transactions and dates and currencies
(plus various other fields) . (amount, ccy, sdate etc)
I am trying to buid query that tells me the count of all transactions that
are within the last 30 days, split by ccy.

I'm using the count(*), and date()-30 to get an overall result, but i cant
seem to get one query that will give me this seeming simple thing!!!]

any help greatly appreciated,