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


"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:uR$kMOrJIHA.1188@xxxxxxxxxxxxxxxxxxxxxxx
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
<snip>

------------------- in line responses -------------------
First of all, thank you very much for the time you've put into this.
I appreciate it.
I don't want the responses inline to give the impression I don't
appreciate your ideas and offerings, I do.
However, I'm not sure some of the ideas are simplifying what I'm doing
now.

It's beginning to seem to me that theres no less work being required on
the code side (in your suggestions) as compared to what I was doing in the
first place
(collecting distinct sets for each file / page - then looping through each)

I look at the "problem" like this.
I have a recordset object I want a report on.
my options as I "imagined them to be" (given my total ignorance of sql) fell
into two categories (sql versus code)
(a) write code to parse the record set
- whether it's in the form of ThisVal <> LastVal version
- or in the form of ForEach SelectDistinct () version
or
(b) come up with an sql statement that "formats/groups/filters/ whatever"
the recordset in such a way that it eliminates <or greatly reduces> (a)
such that (a) becomes (something like)
Do Until oRs.Eof
Call ReportPrint (ors.Fields(1).Value, ors.Fields(2).Value,
ors.Fields(3).Value ) -<whatever fields are of interest of course>
oRs.MoveNext
Loop
because of the "magic" sql statement grouping everything in such a way
that there are no duplicates of the fields which i only want to print once
(file name and Page name) and therefore I can simply step through each
record and print it's fields

that was the fantasy sql trick that I was wondering if it existed...:-)
-------------------


OK, the idea was to clear the filter after the loop, <snip> 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:

-------------------
More importantly (it seems to me) it would mean i'd have to hard code the
function to the number of pages and number of files with a loop for each.
Since that 's a variable unknown, I don't think it's even possible in this
case.
I feel better now, I did understand the idea, just didn't see how to apply
it...so at least it wasn't that i was just too dumb to figure it out
:-)
-------------------


create variables newfile,curfile, newpage, curpage

-------------------
ouch
this is the method I mentioned in my original post that i wanted to avoid if
possible...
I did try it and it got (for some unknown reason <g>)...quite confusing to
me
<snip from op>
I also tried making one recordset/ordered by File-Page-Item
then a bunch of code..If ThisFile <> LastFile
If ThisPage<>LastPage
If ThisItem <> LastItem....and it was even worse....worked on it for
a week or so and never could get it right, and gave up on it in favor of the
selectdistincts, which I could at least get working....

<end snip from op>
-------------------


get out of the
habit of using the obsolete While ... Wend) Inside the loop:

-------------------
ok, thanks, didn't know that was obsolete...
-------------------



- 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
-------------------
Is creating a Dictionary object more efficient than creating a Recordset
object?
In what ways? In my case speed or resources is probably not an issue but I
do like to learn the "Right" way to do things.

actually for this program I *used* to use dictionaries(inside dictionaries
inside dictionaries) to store and sort all this data
That was before i decided to try to learn how to use databases.
I thought databases were the "cool" way to do stuff like this <vbg>
Sounds like I'm going back in time! :-)
-------------------


Tip: use a client-side recordset
oRsPcmkCountAll.cursorlocation = adUseClient
-------------------
yep, thats what i'm using
-------------------


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

-------------------
now that's something I didn't know.
So if you use adUseClient, then the recordset is copied into local memory?

all my database actions create / connect / execute etc are wrapped in a
class i made to "encapsulate" (read compensate for my poor memory <g> ) all
the connection string stuff etc...all of which was new to me and therefore
not easy to remember all the syntax etc.

so now I could add disconnecting the connection...if the operation is read
only that is....as in this example it happens to be....

Thanks for the tip.
-------------------

Thanks again for your time and patience.
Much appreciated.
any thoughts on the above responses also welcome :-)
Mark


.

Relevant Pages

  • Re: OLEDB, stacked views, and efficiency
    ... > Since Access is not really a client-server thing (I believe, ... Access ships with two database engines: Jet and MS SQL Server. ... >> size of the returned recordset BEFORE I start to iterate it? ... >> size of the returned recordset when I filter it, ...
    (microsoft.public.data.oledb)
  • Re: DAO to ADO conversion of seek
    ... Filter is good, but it is good in a case if recordset opened on a client ... SQL is the best way to go!!! ... > and unuseable DAO methods I am going to do ...
    (microsoft.public.vb.database.ado)
  • Re: Help with Microsoft help on linking Access to Outlook
    ... in the criteria cell in the graphical query builder. ... use as the source in the routine. ... the same filtered recordset every time. ... choose themselves what fields to filter on. ...
    (microsoft.public.access.formscoding)
  • Re: Report the current filtered records from a Form
    ... It's still Access applying the filter, that's why it's in that format. ... Access 2002 front-end using SQL Server 2000 (MSDE ... The form has a <print report> button which should trigger a report ... based on that filtered recordset -- this does not work. ...
    (comp.databases.ms-access)
  • Re: Help with Microsoft help on linking Access to Outlook
    ... should build the SQL, using a query, then paste it into your code. ... the same filtered recordset every time. ... choose themselves what fields to filter on. ... Set MyRS = MyDB.OpenRecordset" ...
    (microsoft.public.access.formscoding)