uh..yeah sorry I have the date field in my where clause but what I meant was I do not have a certain parameter to look for..
you had put " where searchtext = 'dave' " in your response..sorry about the confusion. Dave Seddon (p) 997-1803 Internet Software Support Specialist Workplace Information Directorate, HRDC-DRHC e-mail: [EMAIL PROTECTED] Web: http://labour.hrdc-drhc.gc.ca ---------- Original Text ---------- From: <[EMAIL PROTECTED]>, on 2002-05-31 3:29 PM: You don't have a Where clause? That is a problem but, it is not the end of the world. You can create a variable and set it to 0. Then loop through your query results and increment your counter everytime you see the string 'dave' in the record. Just out of curiousity, your original question showed a query with a Where clause. Can't you modify it? **************** Hi Dan, yes very similar.. except my problem is I do not have a WHERE clause... so say my table has: rec1: dave rec2: dave rec3: "dave stinks" rec4: daves I would get 2 searches for dave 1 search for "dave stinks" 1 search for daves I'm only concerning myself with exact 1 word matches Dave Seddon (p) 997-1803 Internet Software Support Specialist Workplace Information Directorate, HRDC-DRHC e-mail: [EMAIL PROTECTED] Web: http://labour.hrdc-drhc.gc.ca ---------- Original Text ---------- From: <[EMAIL PROTECTED]>, on 2002-05-31 1:29 PM: Like this? select count(searchtext) as daily_total, searchdate, cume(daily_total) as running_total from theTable where searchtext = 'dave' group by searchdate The syntax for your database may be different. ************ Hi folks, In my database I have stored some search parameters named searchtext, what I want to do is to be able to count how many times that a particular text (record) has been entered. So rather than display the searchtext result of "dave" 10 times it would display once with the count variable of 10. my current query to get my data is: SELECT searchtext,searchdate FROM table1 WHERE searchdate > #CreateODBCDateTime(DateAdd("d", -7, Now()))# AND searchdate <= #today# Order By searchtext Is there an efficient (easy) way to go about this using sql? I was thinking of maybe using a trigger to check if the text exists and then add to a counter but have never used triggers before. Any thoughts on how this could be handled.. - You are subscribed to the CFUGToronto CFTALK ListSRV. This message has been posted by: [EMAIL PROTECTED] To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ This System has been donated by Infopreneur, Inc. (http://www.infopreneur.net) - You are subscribed to the CFUGToronto CFTALK ListSRV. This message has been posted by: [EMAIL PROTECTED] To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ This System has been donated by Infopreneur, Inc. (http://www.infopreneur.net)
