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



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

Your options are limited with Jet. Shaped recordsets may be the answer,
but I've never used them and can only advise you to look them up in the
documentation.

This could be done with a single recordset, but I think I would go with
two recordsets:
1. Select FldFile,FldPage,FldItem,Count(*) As ItemCount
FROM ...
GROUP BY FldFile,FldPage,FldItem

You can loop through this one to using Filter to easily do the first
section of your report. You could increment some variables (or array
elements) during the loop to get the values for the Totals, or you could
close this recordset and open a second on the following sql:

2. Select FldItem,Count(*) As ItemCount
FROM ...
GROUP BY FldItem
You can loop through this one for your totals section


Thanks Bob,
I'll try these out
I'll have to read up on Group By
to see how that differs from Order By in terms of filtering returned rows

and Count(*) As ItemCount

and Filter...? "You can loop through this one to using Filter to easily do
the first..."
yet another keyword?


will set up a test function to see how that will work as far as reading back
the records

Thanks again,
Mark


.

Relevant Pages

  • Re: dealing with max number of fields
    ... there is one big technical hurdle to overcome - I have no idea what you ... What is a JET table? ... You could, for example, open multiple recordsets to retrieve the data ... from the JET tables, loop through them, and print the data to a text file. ...
    (microsoft.public.access.externaldata)
  • Re: When do I open my Recordsets?
    ... >the first time. ... >Briefly, I loop through one recordset, and depending on certain field ... modify/add records in other tables (i.e. recordsets). ... designed Update queries to do the work instead of looping ...
    (microsoft.public.access.formscoding)
  • For Next Syntax
    ... replacing SQL statements with 2 new dynasets though to ... >Your syntax for the For Next loop is correct, ... > ActiveItemCode = rst!ItemCode ... >additional 'dynaset' recordsets and use those to perform ...
    (microsoft.public.access.modulesdaovba)
  • Re: compare 2 recordsets
    ... This made me think that maybe a double loop could be faster as you could set the start ... I think the FindNext method is the problem and maybe this is better ... And make sure you use disconnected client-side recordsets for this so you can free up the connections. ... This email account is my spam trap so I ...
    (microsoft.public.data.ado)
  • For Next Syntax
    ... Your syntax for the For Next loop is correct, ... Set rst = CurrentDb.OpenRecordset(TempTable, _ ... ActiveItemCode = rst!ItemCode ... additional 'dynaset' recordsets and use those to perform ...
    (microsoft.public.access.modulesdaovba)