Counting/sorting problem 1

[ppetree]

Hey All!

I'm working on a report and I can't seem to get my head around this (not being a true vb/vbs coder makes it a little harder).

I have a table which contains 3 fields (id, fname, fdate)
id = person accessing
fname = file name accessed
fdate = date of file access

I need to create a MTD and YTD report for a specific id which totals the number of times a file accessed on each date within the reporting period.

I could have tens of 1000's of fdates with a few hundred file names (currently only 5 filenames but it will grow exponentially in the next year).

I have done my SQL query and I'm getting all the data back as I am expecting (order by fdate, fname) and I've put the data into an array as aFData = RS.GetRows

I've used UBound to get the number of rows and colums.

I'm able to output it in simple table format, one line per record) and it looks something like this:

ID File       Date
1  large.gif  4/3/2007 04:23:07 PM
1  large.gif  4/3/2007 04:25:09 PM 
1  link.gif   4/3/2007 04:25:11 PM
1  small.gif  4/3/2007 06:14:01 PM
1  square.gif 4/3/2007 07:11:09 PM
1  square.gif 4/3/2007 07:15:13 PM
1  small.gif  4/7/2007 11:01:00 AM
.
.
.

My question is this, what is the best technique for comparing the dates from one record/element to the next and totaling the entries with matching file names on matching days?

I want to end up with this:

ID File       Date
2  large.gif  4/3/2007
1  link.gif   4/3/2007
1  small.gif  4/3/2007
2  square.gif 4/3/2007
1  small.gif  4/7/2007

Thanks,

Phil

 

[ppetree]

Here's what I want to get from the query:

for ID count all unique file_names for each date in date_range

So if I ran this report for ID=1, date range 04/01/2007 thru 04/15/2007 (based on the table in my 9:27a post) I should get a RS containing 4 entries:

afl_name                 afl_date  total_displays
sasy_banner_large.gif    4/3/2007       1
sasy_banner_square.gif   4/4/2007       2 
sasy_banner_square.gif   4/6/2007       1

 

[PHV]

report for ID=1, date range 04/01/2007 thru 04/15/2007
strSQL = "SELECT afl_name, Int(afl_date), Count(*) AS total_displays FROM track WHERE Int(afl_date) Between #2007-04-01# And #2007-04-15# AND ID=1 GROUP BY afl_name, Int(afl_date)"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ppetree]

tsuji,

Here's what I was finally able to get from your last query suggestion:

Report Range 4/1/2007 - 4/8/2007 
Filename                Date                 Total 
sasy_banner_large.gif 4/3/2007 8:02:34 AM  
sasy_banner_large.gif 4/3/2007 10:44:11 AM  
sasy_banner_large.gif 4/3/2007 10:52:30 AM  
sasy_banner_large.gif 4/3/2007 2:51:37 PM  
sasy_banner_large.gif 4/3/2007 2:52:43 PM  
sasy_banner_large.gif 4/3/2007 2:52:51 PM  
sasy_banner_square.gif 4/4/2007 3:30:48 AM  
sasy_banner_square.gif 4/4/2007 5:54:51 AM  
sasy_banner_large.gif 4/4/2007 6:23:43 AM  
sasy_banner_square.gif 4/6/2007 5:33:23 AM  
sasy_banner_large.gif 4/6/2007 12:23:43 PM  
sasy_banner_large.gif 4/6/2007 12:23:47 PM  
sasy_banner_large.gif 4/6/2007 12:23:48 PM  
Total Records = 13

If we compare this against my table in my 9:27am post I should have gotten the results from my last post. Correct?

 

[ppetree]

Actually... my bad...

The results are from:

strSQL = "SELECT COUNT(ID), afl_name, datevalue(afl_date) FROM track WHERE datevalue(afl_date)=datevalue(date()) AND ID =" &u1 &" GROUP BY afl_name, datevalue(afl_date)"

Are:

No Records Found

 

[ppetree]

That was stunningly PERFECT!!!

And here I was trying to figure out how to do the count in vbscript and you clever fellows know how to do it in SQL!

So VERY awesome!

Thanks a bunch for that!

What a Happy Easter this makes!

Phil

 

[PHV]

That was stunningly PERFECT
Which suggestion ?

 

[ppetree]

strSQL = "SELECT afl_name, Int(afl_date), Count(*) AS total_displays FROM track WHERE Int(afl_date) Between #2007-04-01# And #2007-04-15# AND ID=1 GROUP BY afl_name, Int(afl_date)"

 

[tsuji]

If the use of int() as proper as PHV use it, I don't see how the use of datevalue() in the way I use it would fail. But, if the op keeps shifting the need (now becoming between an interval of dates), I am absolutely not unhappy I didn't help.

 

[jfdabiri]

you can also use the concatenation of "file& date" and group by "file& date". this will give you a count per distinct file+date conbination.
e.g.
select file & date, count(*) as kount
from sometable
where id = some_id
group by file & date;

 

[ppetree]

I don't profess to even understand how the difference between int() and datevalue() would effect the outcome of the search.

All I know is that the results are that I get the total number of each filename for each date for the specified user.

I really figured I was going to have to write this in vbs by counting the number of filename on date using a loop. Didn't really relish that idea so allowing SQL to do all the work was exceptionally cool!

Tsuji, thank you for all your help in starting me down a path I didn't know existed and for all your patience as I stumbled through this exercise!

Thanks all!

Phil