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

 

[tsuji]

Should you not use "group by" to get the result directly from sql? Something like this.

[tt] select count(*),[File_column],[Date_column] from tablename group by [File_column],[Date_column][/tt]

Besides, your id started by meaning of "person assessing" and ends up meaning somewhat "number of person assessing"(?). Maybe "person assessing" actually means the number to begin with rather than the identity of the person? In that case, the sql might be this.

[tt] select sum([ID_column]),[File_column],[Date_column] from tablename group by [File_column],[Date_column][/tt]

Or, I miss the point.

 

[ppetree]

I never knew you could get ODBC/Access to do the work for you!

Your first example is the correct understanding of the problem.

When I do the aFData = RS.GetRows what kind of an array will I get back? How does the query return the data? How do you dereference the count?

Thanks for the great advice!

Phil

 

[tsuji]

Okay. In that case, and taking into account where the date column is datetime value type including time component, you can do this, supposing columns are named (ID, File, Date), the concrete sql statement can be this (rather than figuratively shown previously).
[tt]
ssql="select count([ID]),[File],datevalue([Date]) from [green]tablename[/green] group by [File],datevalue([Date])" 'in msaccess flavor
[green]rs[/green].open ssql, [green]conn[/green], 3 [green]'rs your adodb recordset object and conn your connection object opened[/green]
aFData=rs.getrows()
for i =0 to ubound(a,2)
wscript.echo aFData(0,i) & vbtab & aFData(1,i) & vbtab & aFData(2,i)
next
rs.close
[/tt]
With the above, I think your question:
>When I do the aFData = RS.GetRows what kind of an array will I get back? How does the query return the data? How do you dereference the count?
should be cleared up.

 

[tsuji]

>[self]for i =0 to ubound(a,2)
should be read
[tt]for i =0 to ubound([blue]aFData[/blue],2)[/tt]

 

[ppetree]

Ok... I keep playing around with this and getting different results.

I don't need to count by ID, I need to total the files by name by date FOR one specific ID.

I think what I need is this:
strSQL = "SELECT COUNT(*), afl_name, afl_date FROM track WHERE MONTH(afl_date) = MONTH(DATE()) AND YEAR(afl_date) = YEAR(DATE()) AND ID ='" &u1 &"' GROUP BY afl_name, afl_date"

Oh... and I REALLY appreciate your help!

Phil

 

[tsuji]

count([ID]) and count(*) are the same. I change to count([ID]) for the only reason of fixing the idea. They count row in any case, unless you might have ID missing. count(*) is fine for me as well.

 

[ppetree]

Ok... not sure what happend to my last post...

I have been playing with this and getting different results.

What I need is to count the distinct filenames for each date for a specific ID.

I think this is what I need BUT I keep getting a syntax error and the DISTINCT keyword is not available in Access:

strSQL = "SELECT COUNT(*) afl_name, afl_date FROM track WHERE MONTH(afl_date) = MONTH(DATE()) AND YEAR(afl_date) = YEAR(DATE()) AND ID ='" &uid &"' GROUP BY afl_name, afl_date"

I REALLY appreciate your help with this!

Phil

 

[xwb]

That's surprising. I thought it was. What will the following do?

strSQL = "SELECT DISTINCT afl_name, afl_date FROM track"

 

[tsuji]

>strSQL = "SELECT COUNT(*) afl_name, afl_date FROM track WHERE MONTH(afl_date) = MONTH(DATE()) AND YEAR(afl_date) = YEAR(DATE()) AND ID ='" &uid &"' GROUP BY afl_name, afl_date"
[tt]strSQL = "SELECT COUNT(*) afl_name, datevalue(afl_date) FROM track WHERE datevalue(afl_date)=datevalue(date()) AND ID ='" &uid &"' GROUP BY afl_name, datevalue(afl_date)"[/tt]

Note 1: ID in this is a numeric string. Otherwise, take out the single quote.
Note 2: If you group afl_name, would it not be distinct afl_name by definition? Why do you ever need to distinct in this case?

 

[ppetree]

strSQL = "SELECT DISTINCT afl_name, afl_date FROM track"

Produced one record with no filename.

While:

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

Produces:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'COUNT(*) afl_name'. 

/affiliate/reports/mtd_ad_stat.asp, line 81

While the table contains:

1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban1a.gif	3/29/2007  
1  sasyban2a.gif	3/29/2007  
1  sasyban2a.gif	3/29/2007  
2  sasyban1a.gif	3/29/2007  
2  sasyban1a.gif	3/29/2007  
2  sasyban1a.gif	3/29/2007  
2  sasyban1a.gif	3/29/2007  
2  sasyban1a.gif	3/29/2007  
2  sasyban1a.gif	3/29/2007  
2  sasy_banner_large.gif	4/3/2007  
2  sasy_banner_large.gif	4/3/2007  
2  sasy_banner_large.gif	4/3/2007  
2  sasy_banner_large.gif	4/3/2007  
2  sasy_banner_large.gif	4/3/2007  
1  sasy_banner_large.gif	4/3/2007  
1  sasy_banner_square.gif	4/4/2007  
1  sasy_banner_square.gif	4/4/2007  
2  sasy_banner_large.gif	4/4/2007  
1  sasy_banner_square.gif	4/6/2007  
2  sasy_banner_large.gif	4/6/2007  
2  sasy_banner_large.gif	4/6/2007  
2  sasy_banner_large.gif	4/6/2007  
Grand Total (32 Detail Records)

 

[tsuji]

That is a typo inherited from your query string.
[tt]
strSQL = "SELECT COUNT(*)[red],[/red] afl_name, datevalue(afl_date) FROM track WHERE datevalue(afl_date)=datevalue(date()) AND ID ='" &uid &"' GROUP BY afl_name, datevalue(afl_date)"
[/tt]

 

[ppetree]

Yeah, I thought it might be a typo and I tried taking it out and it produces an error as well:

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

Produces:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'COUNT(*) afl_name'. 

/affiliate/reports/mtd_ad_stat.asp, line 81

 

[tsuji]

Read carefully what I posted! and otherwise the thread just drags long without good reason.

 

[ppetree]

The only type I see is the one comma highlighted in <red>red</red> and I had already tried removing that comma before and tried it again.

I'm sorry but I do not know what other typo you can be referring to or what I may have missed. I know this can be frustrating but I am really trying hard to understand what you are teaching me.

Phil

 

[tsuji]

It is to insert back. You have not much of idea what is the format of sql? That is worrisome. How are you going to debug trivial thing?

 

[ppetree]

No, not much of a SQL programmer at all... barely get by. I can do a basic query, basic insert etc. Never used (or even heard of) count until we started this. I can write some really great assembler code, some really great C/C++, some not to bad javascript and some pretty decent perl. I fully confess to being weak in vb/vbscript and SQL. (Guy who was doing this was diagnosed with prostate cancer so now I'm it!)

I have tried it with both the comma and without. Either way I get an error. Without the comma I get a 'missing operatior' error and with the comma I get a 'Data type mismatch in criteria expression' error.

 

[tsuji]

Have you considered my warning on the data type of ID (Note 1)?

 

[ppetree]

Yes, of course... it also produces a datatype mismatch error.

ID is defined as a number
afl_name is text
afl_date is general date which defaults to Now()

 

[PHV]

MS-Access you said ?
What about this ?
strSQL = "SELECT Count(*), afl_name, Int(afl_date) FROM track WHERE Int(afl_date)=Date() AND ID=" & uid & " 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