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

MP wrote:
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

GROUP BY groups, it doesn't filter. Records are grouped according to the
columns in the GROUP BY clause, and aggregations (count, sum, min, max, avg)
are done on the grouped records in the SELECT clause.

WHERE filters records.

In a query with a GROUP BY clause, filtering can also be done on the
post-grouped results via a HAVING clause. Typically, you will use WHERE to
minimize the records going into the grouping mechanism, and you will use
HAVING to perform further filtering perhaps based on aggregated results.

For example, using your sample data, say you only wanted to retrieve results
where FldPage contained "Page2". Since this is a pre-aggregation value, you
should use WHERE to exclude those records from being grouped:

Select FldFile,FldPage,FldItem,Count(*) As ItemCount
FROM ...
WHERE FldPage <> 'Page2'
GROUP BY FldFile,FldPage,FldItem
ORDER BY FldFile,FldPage,FldItem
This statement will result in this resultset:

file1 Page1 Item1 3
file1 Page1 Item1 2
file1 Page1 Item1 1
file2 Page1 Item1 3
file2 Page1 Item1 2
file2 Page1 Item1 1

Now say you only want to include results where the item count is greater
than 1. Since this filter depends on the result of an aggregation, it has to
be done after grouping. This is where the HAVING clause comes into play.
Like this:

Select FldFile,FldPage,FldItem,Count(*) As ItemCount
FROM ...
WHERE FldPage <> 'Page2'
GROUP BY FldFile,FldPage,FldItem
HAVING count(*) > 1
ORDER BY FldFile,FldPage,FldItem
This statement will result in this resultset:

file1 Page1 Item1 3
file1 Page1 Item1 2
file2 Page1 Item1 3
file2 Page1 Item1 2


Note how the WHERE clause comes before the GROUP BY clause, and the HAVING
clause comes after it:: the order of operations does follow the order of
clauses in the sql statement. First, the records are assembled from the
tables included in the FROM clause. Next, the records failing the tests in
the WHERE clause are excluded. Then the reords are grouped according to the
columns listed in the GROUP BY clause, with any aggregations listed in the
SELECT clause being performed. Then records failing the HAVING test are
excluded. Finally results are sorted via the ORDER BY clause. NOTE: GROUP BY
does not guarantee the order of the results - the only way to guaranter sort
order is to use ORDER BY (I goofed by not including one in my original
suggested solutions).

The best way to learn about SQL (especially JetSQL) is to use the Query
Builder in Access. I know you said you're not using it in this project, but
I maintain it would be a mistake not to fire up Access and use it as one of
your development tools. For example, the query builder is topnotch.

and Count(*) As ItemCount

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

Filter is a recordset property. The explanation in the VB online help is
pretty straightforward ...

To expand on it, I would assign the values of FldFile and FldPage in the
first record to variables, and then set the Filter property to:
rs,Filter = "FldFile='" & filval & "' and FldPage='" & pgval & "'"

Then loop through the filtered recrdset to get the Item names and counts.
Then set the filter to:

rs,Filter = "FldFile<>'" & filval & "' and FldPage<>'" & pgval & "'"

Get the file and page values from the first record of the newly filtered
results and repeat



--
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"


.

Relevant Pages

  • Re: Aggregate Functions and Null
    ... > preserved table using WHERE clause, ... adding the outer rows, while the WHERE is applied after. ... If you filter in the ON clause, you'll end up with ALL rows from the ... e.g., when you join Customers LOJ Orders, and want to filter customers from ...
    (microsoft.public.sqlserver.programming)
  • RE: Passing Qry as part of Rpt DoCmd.OpenReport
    ... You are, by your description, trying to filter the output of the report. ... If you create a string that is a Where Clause without the word Where, ... And yes my data query is plain vanillia with no where clause only a single ...
    (microsoft.public.access.reports)
  • Re: Filter By Calculated Field
    ... If you mean the prompt string in the Having clause, ... You can filter the total in many different ways. ... FROM [UnionSeasonCount Query] ... SELECT DISTINCT Season, MailingListID ...
    (microsoft.public.access.queries)
  • Re: Pre-filtering datasets
    ... your case file a dataset for every customer using the Where clause in the ... The filter property only filters after I have ... private void Customer_Load ... Basically if I have a 1 million record customer ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Form record filtering not working
    ... This combination of the OpenForm Where clause and the combo ... This kind of thing patches the form's Filter in between the stub of the SQL ... Allen Browne - Microsoft MVP. ... the account being filtered out actually still appears in the drop ...
    (microsoft.public.access.forms)