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:
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:
Thanks,
Phil
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:
Code:
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:
Code:
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