count summary question arran1180 29.09.2006 09:37:01 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,

Re: count summary question "JK" <Nobody[ at ]Home.com> 29.09.2006 11:44:59 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 "arran1180" <arran1180[ at ]discussions.microsoft.com> wrote in message news:8A3EE747-70EA-449E-8CCB-73A9E9A4B181[ at ]microsoft.com... [Quoted Text] >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,

Re: count summary question "Larry Daugherty" <Larry.NoSpam.Daugherty[ at ]verizon.net> 30.09.2006 10:49:11 In the criteria line for the date field you want something like Between [Enter Start Date] and [Enter End Date] HTH -- -Larry- -- "arran1180" <arran1180[ at ]discussions.microsoft.com> wrote in message news:8A3EE747-70EA-449E-8CCB-73A9E9A4B181[ at ]microsoft.com... [Quoted Text] > 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,

Re: count summary question "JK" <Nobody[ at ]Home.com> 30.09.2006 12:24:23 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 "Larry Daugherty" <Larry.NoSpam.Daugherty[ at ]verizon.net> wrote in message news:uB6QkwH5GHA.4196[ at ]TK2MSFTNGP05.phx.gbl... [Quoted Text] > In the criteria line for the date field you want something like > Between [Enter Start Date] and [Enter End Date] > > HTH > -- > -Larry- > -- > > "arran1180" <arran1180[ at ]discussions.microsoft.com> wrote in message > news:8A3EE747-70EA-449E-8CCB-73A9E9A4B181[ at ]microsoft.com... >> 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, > >