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

MP schrieb:
I have a table with (lets say) 3 fields: fldFile, fldPage, fldItem
I want a summary report of Items/counts on a per file/ per page basis.
[...]
Because I like/learned a lot from the ideas/code of Bob Barrows and
Stephen Howe and think it sad that MP feels a bit discouraged:

It's beginning to seem to me that theres no less work being required on
the code side

I wrote this demo script:

Dim oFS : Set oFS = CreateObject( "Scripting.FileSystemObject" )
Dim oCN : Set oCN = CreateObject( "ADODB.Connection" )
Dim sCS : sCS = Join( Array( _
"Provider=MSDASQL.1" _
, "Driver={Microsoft Text Driver (*.txt; *.csv)}" _
, "DBQ=" & oFS.GetAbsolutePathName( ".\" ) _
), ";" )
oCN.Open sCS

Dim sSQL, oRS
sSQL = "SELECT * FROM pagesmp.txt"
Set oRS = oCN.Execute( sSQL )
showRS oRS

Dim sSQLD : sSQLD = Join( Array( _
"SELECT FldFile, FldPage, FldItem, COUNT( FldItem ) AS Count" _
, "FROM pagesmp.txt" _
, "GROUP BY FldFile, FldPage, FldItem" _
, "ORDER BY 1,2,3" _
), " " )
Set oRS = oCN.Execute( sSQLD )
showRS oRS

Dim sSQLS : sSQLS = Join( Array( _
"SELECT FldItem, COUNT( FldItem ) AS Count" _
, "FROM pagesmp.txt" _
, "GROUP BY FldItem" _
, "ORDER BY 1" _
), " " )
Set oRS = oCN.Execute( sSQLS )
showRS oRS

WScript.Echo "<Report:>"
WScript.Echo "File/Page Summary:"
Set oRS = oCN.Execute( sSQLD )
Dim nWidth : nWidth = 6 ' simple, one width for all cols
Dim nCols : nCols = oRS.Fields.Count - 3
ReDim aOlds( nCols )
ReDim aVals( nCols + 2 )
Dim nCol, nIdx
Do Until oRS.EOF
For nCol = 0 To nCols
If aOlds( nCol ) <> oRS( nCol ).Value Then
aVals( nCol ) = Left( oRS( nCol ).Value & Space( nWidth ), nWidth )
aOlds( nCol ) = oRS( nCol ).Value
For nIdx = nCol + 1 To nCols - 1
aOlds( nIdx ) = ""
Next
Else
aVals( nCol ) = Space( nWidth )
End If
Next
aVals( nCol + 0 ) = Left( oRS( nCol + 0 ).Value & Space( nWidth ), nWidth )
aVals( nCol + 1 ) = Right( Space( nWidth ) & oRS( nCol + 1 ).Value, nWidth )
WScript.Echo Join( aVals, " " )
oRS.MoveNext
Loop

WScript.Echo "Totals Summary:"
Set oRS = oCN.Execute( sSQLS )
Do Until oRS.EOF
WScript.Echo Left( oRS( 0 ).Value & Space( nWidth ), nWidth ) _
, Right( Space( nWidth ) & oRS( 1 ).Value, nWidth )
oRS.MoveNext
Loop
WScript.Echo "<End Report>"

oCN.Close

Sub showRS( oRS )
Const adClipString = 2 ' to keep ADO happy
Dim sHead : sHead = ""
Dim oFld
For Each oFld In oRS.Fields
sHead = sHead & vbTab & oFld.Name
Next
WScript.Echo Join( Array( _
String( 70, "=" ) _
, vbCrLf _
, oRS.Source _
, vbCrLf _
, Mid( sHead, 2 ) _
, vbCrLf _
, String( 70, "-" ) _
, vbCrLf _
, oRS.GetString( adClipString, , vbTab, vbCrLf, "NULL" ) _
, String( 70, "=" ) _
), "" )
End Sub

its output:

=== reportMP: report files/pages/item (mp) ====================================
======================================================================
SELECT * FROM pagesmp.txt
FldFile FldPage FldItem
----------------------------------------------------------------------
File1 Page1 Item1
File1 Page1 Item1
File1 Page1 Item1
File1 Page1 Item2
File1 Page1 Item2
File1 Page1 Item3
File1 Page2 Item1
File1 Page2 Item1
File1 Page2 Item1
File1 Page2 Item3
File1 Page2 Item4
File1 Page2 Item4
File2 Page1 Item1
File2 Page1 Item1
File2 Page1 Item1
File2 Page1 Item2
File2 Page1 Item2
File2 Page1 Item3
File2 Page2 Item1
File2 Page2 Item1
File2 Page2 Item1
File2 Page2 Item3
File2 Page2 Item4
File2 Page2 Item4
======================================================================
======================================================================
SELECT FldFile, FldPage, FldItem, COUNT( FldItem ) AS Count FROM pagesmp.txt GROUP BY FldFile, FldPage, FldItem ORDER BY 1,2,3
FldFile FldPage FldItem Count
----------------------------------------------------------------------
File1 Page1 Item1 3
File1 Page1 Item2 2
File1 Page1 Item3 1
File1 Page2 Item1 3
File1 Page2 Item3 1
File1 Page2 Item4 2
File2 Page1 Item1 3
File2 Page1 Item2 2
File2 Page1 Item3 1
File2 Page2 Item1 3
File2 Page2 Item3 1
File2 Page2 Item4 2
======================================================================
======================================================================
SELECT FldItem, COUNT( FldItem ) AS Count FROM pagesmp.txt GROUP BY FldItem ORDER BY 1
FldItem Count
----------------------------------------------------------------------
Item1 12
Item2 4
Item3 4
Item4 4
======================================================================
<Report:>
File/Page Summary:
File1 Page1 Item1 3
Item2 2
Item3 1
Page2 Item1 3
Item3 1
Item4 2
File2 Page1 Item1 3
Item2 2
Item3 1
Page2 Item1 3
Item3 1
Item4 2
Totals Summary:
Item1 12
Item2 4
Item3 4
Item4 4
<End Report>
=== reportMP: 0 done (00:00:01) ===============================================
### Xplore erfolgreich beendet ################################################

I hope it shows the general strategies - how to get the info and how to format
it.

.