Re: How to summarize recordset...Select Distinct alternative?

MP wrote:
"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:Ot8dKQmJIHA.4880@xxxxxxxxxxxxxxxxxxxxxxx
Bob Barrows [MVP] wrote:

darn, can't figure out the filter looping method

this works perfect for the first file and first page...but how to get
the next page, and next, and next ... to the end of first file,
.... then next file (all pages)...next file(all pages).. etc to end
of all files....?

OK, the idea was to clear the filter after the loop, then create and apply a
new filter to exclude the already processed file and page values, read the
first record of the newly-filtered data to get the new filter values, clear
the filter again, and create and apply a new filter with the new filter and
page values.

However .... this can become quite cumbersome and error-prone. Do this
instead:

create variables newfile,curfile, newpage, curpage
Upon opening the recordset (it's already pointing at the first record at
this point - MoveFirst is not necessary) read the first file value into
curfile
Write the value of curfile
Start the loop (use Do Until oRsPcmkCountAll.EOF ... Loop; get out of the
habit of using the obsolete While ... Wend) Inside the loop:
- read the file value of the current record into newfile

- compare curfile to newfile. If they are different, set
curfile to newfile,write the value of curfile, and set curpage to ""

- read the current page value into newpage

- compare curpage to newpage; if they are different, set
curpage to newpage and write its value

- Write the item and itemcount values
- optionally you could put the item and count values into a
Dictionary object and increment the counts - this would
avoid the need to open a second recordset for the
Totals section

- MoveNext

Tip: use a client-side recordset
oRsPcmkCountAll.cursorlocation = adUseClient

and disconnect it before processing it
Set oRsPcmkCountAll.ActiveConnection = Nothing
'close the connection at this point - it's no longer needed

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.