"MP" <nospam@Thanks.comwrote in
news:VkS%g.32902$IO2.29849@tornado.rdc-kc.rr.com:
Quote:
"David W. Fenton" <XXXusenet@dfenton.com.invalidwrote in message
news:Xns9867A6EE6E6BEf99a49ed1d0c49c5bbb2@127.0.0. 1...
Quote:
>>
>You'll find it very easy. The only thing that's different in the
>code you've already done is how you open your connections and how
>you initialize your recordsets. And ADO has only Find for
>navigation, whereas DAO has FindFirst and FindNext. Other than
>that in this context, they are similar enough that it wouldn't
>take long to port the code at all.
>
Ok this seems to give the counts
Now I want to read back the fields manually to make sure i'm
getting the right count.
>
to that end i hoped to be abel to get the count as well as the
fields back in a recordset
>
If the sql "sSql = "Select Count (*) From " & TABLE_NAME & " Where
....etc "
creates a one field record containing the count of records
matching the
criteria,
is there a way to get it to also return the fields themselves?
>
so i can do
While Not rs.eof
For Each Fld in ors.Flds
Debug.print fld.Name & fld.Value
Next
Ors.MoveNext
Wend
>
so I can see an actual printout of the values and convince myself
the count is correct?
Not with that SQL, which has only one field.
I still think the self-join would work better.
Quote:
my failed attempts at the syntax follow at bottom of post
><g>
>
or do I need to loop twice, once to get the count and a second
time to get the fields to read?
There's nothing to loop, because you aren't returning the data in
the SQL above.
Quote:
there's some trick about aggregate functions and what they return
that I don't get yet....based on various error codes i get when
trying different things
In Access, I'd likely use one of the domain functions, but you're
not in Access, so that's not an option.
You could open a recordset to get the count. It will be very fast.
It's faster than opening the full recordset and moving to the last
record to get it from .RecordCount (it's not guaranteed to be
accurate until you move to the last record in the recordset). So the
SELECT Count(*) is going to be the best way to get the count. And it
will be very fast. You'll want to do something like this:
SELECT f1, f2, f3, Count(*) AS RecordCount
FROM table1
WHERE f4 Like "?R"
GROUP BY f1, f2, f3
If you want to do both L and R, then add "And f4 Like "?L", and then
you can put f4 in the SELECT and GROUP BY clause.
If you had Access to write you SQL in it would be a lot easier!
And in the end, I think what would really be useful would be a
crosstab, with columns for each of the values in the 4th field. I've
posted that at the end of this message.
Quote:
fwiw heres' the dao code to get the counts of L per distinct 3
field group '(sRpt is a string var set elsewhere, FormReport is my
logging routine, other vars also set elsewhere, Option Explicit is
always on) Sub DAOOpenRecordset2()
Dim db As DAO.Database, rst As DAO.Recordset, rst2 As
DAO.Recordset,
fld As DAO.Field
Set db = DBEngine.OpenDatabase(DATABASE_NAME)
>
sSql = "SELECT DISTINCT fldTypeName, fldCondName, fldCondVar
FROM " &
TABLE_NAME & " ORDER BY fldTypeName, fldCondName, fldCondVar"
Set rst = db.OpenRecordset (sSql, dbOpenForwardOnly,
dbReadOnly) While Not rst.EOF
sType = rst.Fields("fldTypeName").Value
sCond = rst.Fields("fldCondName").Value
sCondVar = rst.Fields("fldCondVar").Value
>
sSql = "Select Count (*) From " & TABLE_NAME & " Where
fldTypeName =
'" & sType & "' AND fldCondName = '" & sCond & "' AND fldCondVar =
'" & sCondVar & "' AND fldCondLbr = '2L'"
>
Set rst2 = db.OpenRecordset(sSql, dbOpenForwardOnly,
dbReadOnly)
Forward-type recordsets are not faster. You can open a Dynaset and
it will be faster, because the forward-type recordset will populate
itself to the last record before you can do anything with it, while
the Dynaset will let you start walking through the records while the
end of the recordset is still loading.
This is counterintuitive, but it's the way it works.
Quote:
sRpt = sRpt & rst2.Fields(0).Value & " 2L hits for " & sType
& sCond &
sCondVar & vbCrLf
rst2.Close
Set rst2 = Nothing
Since you're looping and re-opening the recordset, move the Set rst2
= Nothing outside your loop.
Here's where Set rst2 = Nothing would go.
Quote:
FormReport sRpt
>
' Close the recordset
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
I'd check that each of this was Not Nothing before closing them. If
they aren't open you'll get an error.
Quote:
End Sub 'DAOOpenRecordset2
>
and here's the report
>
0 2L hits for A10
3 2L hits for A11
0 2L hits for A12
0 2L hits for A13
0 2L hits for A14
>
>
I tried this to get both count and fields but it didn't like the
string ( i show *condition* here just to eliminate clutter here -
the acutal condition is ok it's the count i cant' get right)
>
sSql = "Select fldTypeName, fldCondName, fldCondVar, fldCondLbr,
Count (*)
AS TotalCount From " & TABLE_NAME & " Where *condition*
ErrState: 3122 (You tried to execute a query that does not include
the specified expression 'fldTypeName' as part of an aggregate
function.)
Right. You need a GROUP BY clause that includes all the
non-aggregate function fields in it, i.e:
GROUP BY fldTypeName, fldCondName, fldCondVar, fldCondLbr
This goes after the WHERE clause (where ORDER BY normally goes).
Quote:
also tried count first and fields after, still no joy
sSql = "Select Count (*) AS TotalCount, fldTypeName,
fldCondName,
fldCondVar, fldCondLbr From " & TABLE_NAME & " Where *condition*
ErrState: 3122 (You tried to execute a query that does not include
the specified expression 'fldTypeName' as part of an aggregate
function.)
You haven't told it what batch of values to count.
Quote:
based on previous error message i tried to include the field name
in the count function ... wild guessing obviously :-)
sSql = "Select Count (fldTypeName) AS TotalCount,
fldTypeName,
fldCondName, fldCondVar, fldCondLbr From " & TABLE_NAME & " Where
*condition*
ErrState: 3122 (You tried to execute a query that does not include
the specified expression 'fldTypeName' as part of an aggregate
function.)
Gotta have a GROUP BY in this case. The only time Count(*) works
without it is if you have no fields returned except the Count(*).
Quote:
so i thought maybe i needed all the field names (even though it
said i didn't use the one I did use)
sSql = "Select Count (fldTypeName, fldCondName, fldCondVar,
fldCondLbr) AS TotalCount, fldTypeName, fldCondName, fldCondVar,
fldCondLbr From " & TABLE_NAME & " Where *condition*
ErrState: 3075 (Wrong number of arguments used with function in
query expression 'Count (fldTypeName, fldCondName, fldCondVar,
fldCondLbr)'.)
Count() takes a valid expression that evaluates to a single value,
and the comma-separated list of values doesn't. You could
concatenate them, but that really wouldn't get you what you want, as
it would be counting the same thing you'd get with a SELECT DISTINCT
on those fields, rather than a count of the rows with certain values
specified in your WHERE clause.
Quote:
so at this point i have to admit I don't know how to use the
Count() function to get both a count and some records at the same
time
>
now i'm looping through twice, once to get the count and a second
time to read the records...that works but seems redundant...(and
it did confirm that the count was returning correctly)
Take what you've got and add the GROUP BY clause and it should work.
To do a crosstab, it would be like this:
TRANSFORM Count(*) AS RecordCount
SELECT f1, f2, f3
FROM tblInventory
GROUP BY f1, f2, f3
PIVOT f4;
This will give you the date in the first three columns followed by a
number of columns that will be as many as there are unique values in
f4. You may want to Pivot on an expression, though, such as
Right(f4,1). Yes, just tested and you could do:
TRANSFORM Count(*) AS RecordCount
SELECT f1, f2, f3
FROM tblInventory
GROUP BY f1, f2, f3
PIVOT Right(f4,1);
Or maybe Mid(2,1).
I'm pretty sure those will work via DAO outside of Access, but
you'll have to test. I know that Left() and Right() work, as well as
IIf(), but not Nz(). This is because, apparently, Nz() is an Access
function, while the others are built into Jet at some level.
--
David W. Fenton
http://www.dfenton.com/
usenet at dfenton dot com
http://www.dfenton.com/DFA/ 
