Select Count(*) problem

[MP]

vb6,ado,mdb,win 2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(cons t) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source=" & msDbFilename
moConn.Properti es("Persist Security Info") = False
moConn.Connecti onString = msConnString
moConn.CursorLo cation = adUseClient
moConn.Mode = adModeReadWrite ' or using default...same result
moConn.Open
can't seem to get my sql right
here's a few versions of the many ways i've tried with the resulting error
codes following each

' sSql = "Select Count(*) As 'Total' From " & TABLE_NAME & _
' " WHERE fldType.Value = '" & sType & "' AND " & _
' " fldCondName.Val ue = '" & sCond & "' AND " & _
' " fldCondVar.Valu e = '" & sCondVar & "' AND " & _
' " fldCondLBR.Valu e = '2L'"
'ErrState: -2147217904 (No value given for one or more required parameters.)

sSql = "Select Count(*) As 'Total' From " & TABLE_NAME & _
" WHERE fldTypeName.Val ue = '" & sType & "' AND " & _
" fldCondName.Val ue = '" & sCond & "' AND " & _
" fldCondVar.Valu e = '" & sCondVar & "' AND " & _
" fldCondLBR.Valu e = '2L' ORDER BY fldTypeName"
'ErrState: -2147217887 <You tried to execute a query that does not include
the specified expression 'fldTypeName' as part of an aggregate function.>
' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE fldTypeName.Val ue = '" & sType & "' AND " & _
' " fldCondName.Val ue = '" & sCond & "' AND " & _
' " fldCondVar.Valu e = '" & sCondVar & "' AND " & _
' " fldCondLBR.Valu e = '2L'"
'ErrState: -2147217904 (No value given for one or more required parameters.)

' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".Fields.Item(' fldTypeName').V alue = '"
& sType & "' AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondName').V alue = '" & sCond & "'
AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondVar').Va lue = '" & sCondVar &
"' AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondLBR').Va lue = '2L'"
'ErrState: -2147217900 (Invalid use of '.', '!', or '()'. in query
expression 'tblTypeCond.Fi elds.Item('fldT ypeName').Value = 'A' AND
tblTypeCond.Fie lds.Item('fldCo ndName').Value = '1' AND
tblTypeCond.Fie lds.Item('fldCo ndVar').Value = '3' AND
tblTypeCond.Fie lds.Item('fldCo ndLBR').Value = '2L''.)

' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".Fields.Item(' fldTypeName').V alue = '"
& sType & "' AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondName').V alue = '" & sCond & "'
AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondVar').Va lue = '" & sCondVar &
"' AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondLBR').Va lue = '2L'"
'ErrState: -2147217900 (Invalid use of '.', '!', or '()'. in query
expression 'tblTypeCond.Fi elds.Item('fldT ypeName').Value = 'A' AND
tblTypeCond.Fie lds.Item('fldCo ndName').Value = '1' AND
tblTypeCond.Fie lds.Item('fldCo ndVar').Value = '4' AND
tblTypeCond.Fie lds.Item('fldCo ndLBR').Value = '2L''.)

' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".fldTypeName.V alue = '" & sType & "'
AND " & _
' TABLE_NAME & ".fldCondName.V alue = '" & sCond & "' AND " & _
' TABLE_NAME & ".fldCondVar.Va lue = '" & sCondVar & "' AND " & _
' TABLE_NAME & ".fldCondLBR.Va lue = '2L'"
'ErrState: -2147217904 (No value given for one or more required parameters.)
' sSql = "Select Count(*) From " & TABLE_NAME & _
' " WHERE " & TABLE_NAME & ".Fields.Item(' fldTypeName').V alue = '"
& sType & "' AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondName').V alue = '" & sCond & "'
AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondVar').Va lue = '" & sCondVar &
"' AND " & _
' TABLE_NAME & ".Fields.Item(' fldCondLBR').Va lue = ''2L''"
'ErrState: -2147217900 (Syntax error (missing operator) in query expression
'tblTypeCond.Fi elds.Item('fldT ypeName').Value = 'A' AND
tblTypeCond.Fie lds.Item('fldCo ndName').Value = '1' AND
tblTypeCond.Fie lds.Item('fldCo ndVar').Value = '4' AND
tblTypeCond.Fie lds.Item('fldCo ndLBR').Value = ''2L'''.)

eliminate the where clause maybe that's the problem?
'Try execute sql <Select Count(*) As 'Total' From tblTypeCond>
'ErrState: ErrNum 3265 (Item cannot be found in the collection corresponding
to the requested name or ordinal.)

yikes...what's the problem???

i'm running out of ideas
any help?
Thanks
Mark

[David W. Fenton]

"MP" <no****@Thanks. comwrote in
news:UZ******** ***********@tor nado.rdc-kc.rr.com:

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...

>"MP" <no****@Thanks. comwrote in
news:Zj******* ***********@tor nado.rdc-kc.rr.com:

so I guess I have to establish multiple connections to get
multiple recordsets???

[]

is that right?

I don't do ADO. There are none of these problems in DAO at all.

ok ok, don't rub it in :-)
you're eventually gonnna talk me into having to learn dao now eh?

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[MP]

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...

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

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

Thanks.
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 DAOOpenRecordse t2()
Dim db As DAO.Database, rst As DAO.Recordset, rst2 As DAO.Recordset,
fld As DAO.Field
Set db = DBEngine.OpenDa tabase(DATABASE _NAME)

sSql = "SELECT DISTINCT fldTypeName, fldCondName, fldCondVar FROM " &
TABLE_NAME & " ORDER BY fldTypeName, fldCondName, fldCondVar"
Set rst = db.OpenRecordse t (sSql, dbOpenForwardOn ly, dbReadOnly)
While Not rst.EOF
sType = rst.Fields("fld TypeName").Valu e
sCond = rst.Fields("fld CondName").Valu e
sCondVar = rst.Fields("fld CondVar").Value

sSql = "Select Count (*) From " & TABLE_NAME & " Where fldTypeName =
'" & sType & "' AND fldCondName = '" & sCond & "' AND fldCondVar = '" &
sCondVar & "' AND fldCondLbr = '2L'"

Set rst2 = db.OpenRecordse t(sSql, dbOpenForwardOn ly, dbReadOnly)

sRpt = sRpt & rst2.Fields(0). Value & " 2L hits for " & sType & sCond &
sCondVar & vbCrLf
rst2.Close
Set rst2 = Nothing
rst.MoveNext
Wend

FormReport sRpt

' Close the recordset
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing

End Sub 'DAOOpenRecords et2

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.)

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.)

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.)

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)'.)

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...(an d it did confirm that
the count was returning correctly)

surely there's a way to get records and also a count in one statement?

[David W. Fenton]

"MP" <no****@Thanks. comwrote in
news:Vk******** ***********@tor nado.rdc-kc.rr.com:

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...

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

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.

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.

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 DAOOpenRecordse t2()
Dim db As DAO.Database, rst As DAO.Recordset, rst2 As
DAO.Recordset,
fld As DAO.Field
Set db = DBEngine.OpenDa tabase(DATABASE _NAME)

sSql = "SELECT DISTINCT fldTypeName, fldCondName, fldCondVar
FROM " &
TABLE_NAME & " ORDER BY fldTypeName, fldCondName, fldCondVar"
Set rst = db.OpenRecordse t (sSql, dbOpenForwardOn ly,
dbReadOnly) While Not rst.EOF
sType = rst.Fields("fld TypeName").Valu e
sCond = rst.Fields("fld CondName").Valu e
sCondVar = rst.Fields("fld CondVar").Value

sSql = "Select Count (*) From " & TABLE_NAME & " Where
fldTypeName =
'" & sType & "' AND fldCondName = '" & sCond & "' AND fldCondVar =
'" & sCondVar & "' AND fldCondLbr = '2L'"

Set rst2 = db.OpenRecordse t(sSql, dbOpenForwardOn ly,
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 counterintuitiv e, but it's the way it works.

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.

rst.MoveNext
Wend

Here's where Set rst2 = Nothing would go.

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.

End Sub 'DAOOpenRecords et2

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).

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.

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(*).

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.

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...(an d
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/

[MP]

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...

snip

I still think the self-join would work better.

I'm still studying that
I dusted off all my notes I've collected on joins from various net souces to
try and learn how to use them
so far I just have a really big headache :-) but eventually I'll get it
figured out.

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.

What I mean by loop twice was ( and loop was a bad term to use)
1)
"Select Count (*) From " & TABLE_NAME & " Where
now i have the count
2)
"Select F1, F2 etc From " & TABLE_NAME & " Where
now i have the data

execute twice would have been a better term than loop...

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

yes that's what i was trying to do!!! now you have the count and the data in
one set right?
the part i was missing was the Group by which I didn't realize was what was
throwing the error..
as verbose and helpful as ado/dao error messages are I'm surprised they
didn't say 'Hey dummy, you need a group by here..."
:-)

Set rst2 = db.OpenRecordse t(sSql, dbOpenForwardOn ly,
dbReadOnly)

Forward-type recordsets are not faster.

i only used that because it's the only sample of dao code i found in my
"archives" of research from the net
not knowing anything about dao I just copied it and tweaked with my field
names and voila it worked!
amazing :-)

Since you're looping and re-opening the recordset, move the Set rst2
= Nothing outside your loop.

absolutely, that was just in there for redundant 'cleaning' in that quickie
sample

--------------------------

I'd check that each of this was Not Nothing before closing them. If
they aren't open you'll get an error.

dao does not appear to have a .State on recordset and connection objects
(I dont' find them in object browser or intellisense any way)

in ado to cleanup
If Rs.State = adStateOpen Then
Rs.Close
Set Rs = Nothing
End if

so in dao if you do Rs.Close...does that set Rs to nothing?
if not then why would "If Rs Is Nothing" ....be a way to see if it's closed?
--------------------------

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

AH HA!!!
Thats' the secret ingregient I was missing!
Thanks for that ... it was driving me crazy! (not hard)
<g>

You haven't told it what batch of values to count.

I thought the Where did that
I thought Count(*) meant count every record (thinking of * as wild card for
'match all') matching the WHERE condition

you don't happen to have a situs where Count() is explained in detail how it
works??? I find lots of simple examples but not a detailed explanation on
any of this yet, thus my inability to figure out on my own that it was the
Group By that I was missing....I would never have found that without your
explanation!
I have a thousand examples like

SELECT Count(*) AS [Number of Invoices]
FROM tblInvoices

it shows no group by (but it had no Where clause to require one...but I
didn't know that without previous knowlege)

If I read between the lines correctly you're saying if Count includes a
Where it must also include a Group By...is that correct assumption?

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.

I'm going to study those inner / outer / transform / joins and see if I can
grok them
Unfortunately at my advanced age i'm quite a slow learner (he he
he...actually I was at any age <g>)

I'm thinkingsomethi ng like
[ Select F5 Inner Join (or outer?)
[ Select Distinct F4 InnerJoin (or outer?)
[Select Distinct F1, F2, F3 as 3Field]
on 3field.somethin g = 4Field.somethin g Where 4Conditions] as 4field
On 4Field.somethin g = 5Field.somethin g Where 5conditions] As 5Field

but obviously i'm very confused at this point....need more time to
understand how the joins work

any sites with tutorials on the Transform that would go in depth would also
be appreciated if you know of any.
I have the JetSql 1,2,and 3 articles... I think from msdn... but they're
pretty thin on detail...I've read them over many times and am still
struggling to understand how to work the joins into my situation....i' m
pretty sure that's what i need to do though
Fundamental Microsoft Jet SQL for Access 2000 by Acey, James Bunch

Thanks for your unflagging perseverance!

one thing i'm seeing in reading about joins is that ON seems synonymous with
WHERE
Join x ON tbl1.F1 = tbl2.F1
Where tbl1.F2 = tbl2.F2

i don't understand the difference between the ON and the where
looks like you'd get the same from
Join x ON tbl1.F2 = tbl2.F2
Where tbl1.F1 = tbl2.F1

but as i say i'm really in the dark on this and need time to absorb it more
or find more sources with indepth explanations i can understand
Mark

[David W. Fenton]

"MP" <no****@Thanks. comwrote in
news:Nr******** ***********@tor nado.rdc-kc.rr.com:

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...

[]

>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

yes that's what i was trying to do!!! now you have the count and
the data in one set right?
the part i was missing was the Group by which I didn't realize was
what was throwing the error..
as verbose and helpful as ado/dao error messages are I'm surprised
they didn't say 'Hey dummy, you need a group by here..."

Well, it's not the job of the DAO/ADO error messages to teach you
SQL.

Set rst2 = db.OpenRecordse t(sSql, dbOpenForwardOn ly,
dbReadOnly)

Forward-type recordsets are not faster.

i only used that because it's the only sample of dao code i found
in my "archives" of research from the net
not knowing anything about dao I just copied it and tweaked with
my field names and voila it worked!
amazing :-)

You can leave of the last two arguments, which are both optional.
They default to Dynaset and Editable, if I'm not mistaken. If you're
concerned about the latter, you could keep the read-only flag and
just have nothing for the second parameter.

[]

>I'd check that each of this was Not Nothing before closing them.
If they aren't open you'll get an error.

dao does not appear to have a .State on recordset and connection
objects (I dont' find them in object browser or intellisense any
way)

Nope.

in ado to cleanup
If Rs.State = adStateOpen Then
Rs.Close
Set Rs = Nothing
End if

so in dao if you do Rs.Close...does that set Rs to nothing?

No. That only clears the structure in memory that the variable is
pointing to. Setting the variable to Nothing then clears the
variable, too. The reason you have to do both is because VB(A) uses
reference counts and it sometimes doesn't keep good track. That is,
until you set the variable to Nothing, it may not release the memory
used by the recordset, even when you explicitly close it.

Checking for Not Nothing works in all cases except a very strange
one with database variables initialized via CurrentDB().

if not then why would "If Rs Is Nothing" ....be a way to see if
it's closed?

It works. With the exception of the one circumstance where a
database variable can be Not Nothing but the corresponding data
structure not be there, I've found no problems (and that only with
the currently open db), and certainly not with recordsets.

[]

>You haven't told it what batch of values to count.

I thought the Where did that
I thought Count(*) meant count every record (thinking of * as wild
card for 'match all') matching the WHERE condition

Yes, but you've got to tell it what unique records to count. That's
what the GROUP BY does.

you don't happen to have a situs where Count() is explained in
detail how it works??? I find lots of simple examples but not a
detailed explanation on any of this yet, thus my inability to
figure out on my own that it was the Group By that I was
missing....I would never have found that without your explanation!

There's a major Jet SQL article on MSDN. It basically replicates
what's in the Access help files.

I have a thousand examples like

SELECT Count(*) AS [Number of Invoices]
FROM tblInvoices

it shows no group by (but it had no Where clause to require
one...but I didn't know that without previous knowlege)

It's not the WHERE clause that forces the GROUP BY. It's having
fields other than the COUNT() as part of the SELECT statement.

If I read between the lines correctly you're saying if Count
includes a Where it must also include a Group By...is that correct
assumption?

You trailed off there, but if you're displaying fields and you want
to count the values that match the fields, you have to have a GROUP
BY.

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

I'm going to study those inner / outer / transform / joins and see
if I can grok them
Unfortunately at my advanced age i'm quite a slow learner (he he
he...actually I was at any age <g>)

I'm thinkingsomethi ng like
[ Select F5 Inner Join (or outer?)
[ Select Distinct F4 InnerJoin (or outer?)
[Select Distinct F1, F2, F3 as 3Field]
on 3field.somethin g = 4Field.somethin g Where 4Conditions] as
4field
On 4Field.somethin g = 5Field.somethin g Where 5conditions] As
5Field

but obviously i'm very confused at this point....need more time to
understand how the joins work

any sites with tutorials on the Transform that would go in depth
would also be appreciated if you know of any.
I have the JetSql 1,2,and 3 articles... I think from msdn... but
they're pretty thin on detail...I've read them over many times and
am still struggling to understand how to work the joins into my
situation....i' m pretty sure that's what i need to do though
Fundamental Microsoft Jet SQL for Access 2000 by Acey, James Bunch

This is where having Access makes life *much* easier. You can do so
many things in the Access QBE grid and then look at the SQL it
generates to understand how it works.

Thanks for your unflagging perseverance!

one thing i'm seeing in reading about joins is that ON seems
synonymous with WHERE
Join x ON tbl1.F1 = tbl2.F1
Where tbl1.F2 = tbl2.F2

Yes, and in my testing, Jet optimizes joins and WHERE clauses
identically, so there's no performance penalty for using the WHERE
clause instead of the join.

i don't understand the difference between the ON and the where
looks like you'd get the same from
Join x ON tbl1.F2 = tbl2.F2
Where tbl1.F1 = tbl2.F1

but as i say i'm really in the dark on this and need time to
absorb it more or find more sources with indepth explanations i
can understand

The problem is that I'm not sure it's entirely possible to replicate
an outer join, where you return both values from the other table and
the Nulls for records that are not matched in the other table.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[MP]

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in message
news:Xn******** *************** ***********@127 .0.0.1...

I'm surprised

they didn't say 'Hey dummy, you need a group by here..."

Well, it's not the job of the DAO/ADO error messages to teach you
SQL.

<VBG>

There's a major Jet SQL article on MSDN. It basically replicates
what's in the Access help files.

pretty sure that's the one i have and have read many times .... still trying
to absorb it

>You haven't told it what batch of values to count.

I thought the Where did that
I thought Count(*) meant count every record (thinking of * as wild
card for 'match all') matching the WHERE condition

>Yes, but you've got to tell it what unique records to count. That's

what the GROUP BY does.

very confusing to me
I would think the Where told it what to count and the Group by told it how
to sort the displayed results

that's what I get for thinking ;-)

[Lyle Fairfield]

"MP" <no****@Thanks. comwrote in
news:Nr******** ***********@tor nado.rdc-kc.rr.com:

in ado to cleanup
If Rs.State = adStateOpen Then
Rs.Close
Set Rs = Nothing
End if

IMO, this is entirely superfluous.

Please, post an example of a situation where omitting this code caused a
problem.

--
Lyle Fairfield

[Lyle Fairfield]

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in
news:Xn******** *************** ***********@127 .0.0.1:

>dao does not appear to have a .State on recordset and connection
objects (I dont' find them in object browser or intellisense any
way)

Nope.

>in ado to cleanup
If Rs.State = adStateOpen Then
Rs.Close
Set Rs = Nothing
End if

so in dao if you do Rs.Close...does that set Rs to nothing?

No. That only clears the structure in memory that the variable is
pointing to. Setting the variable to Nothing then clears the
variable, too. The reason you have to do both is because VB(A) uses
reference counts and it sometimes doesn't keep good track. That is,
until you set the variable to Nothing, it may not release the memory
used by the recordset, even when you explicitly close it.

Checking for Not Nothing works in all cases except a very strange
one with database variables initialized via CurrentDB().

>if not then why would "If Rs Is Nothing" ....be a way to see if
it's closed?

It works. With the exception of the one circumstance where a
database variable can be Not Nothing but the corresponding data
structure not be there, I've found no problems (and that only with
the currently open db), and certainly not with recordsets.

The ADO Defense Counsel rests, your Honour.

--
Lyle Fairfield

[MP]

"Lyle Fairfield" <ly***********@ aim.comwrote in message
news:Xn******** *************** **********@216. 221.81.119...

"MP" <no****@Thanks. comwrote in
news:Nr******** ***********@tor nado.rdc-kc.rr.com:

in ado to cleanup
If Rs.State = adStateOpen Then
Rs.Close
Set Rs = Nothing
End if

IMO, this is entirely superfluous.

Please, post an example of a situation where omitting this code caused a
problem.

--
Lyle Fairfield

I don't have such an example.
by now it should be obvious I'm a rank beginner. <g>
I thought that was how it was done since that's what i've seen in my
research into learning ado

for example...copie d from some newsgroup posting
From: "Veign" <NO***********@ veign.com>
Subject: Re: ADOX Columns
Date: Thursday, August 19, 2004 9:30 AM
<snip>

'Cleanup
If (RS.State And adStateOpen) = adStateOpen Then RS.Close
Set RS = Nothing

If (CNN.State And adStateOpen) = adStateOpen Then CNN.Close
Set CNN = Nothing

coming from that source (Chris Hanscom, MVP http://www.veign.com)
i had no reason to question the viability of the method

I assumed that if a recordset isn't open rs.close would throw an
error...haven't tested that just seemed logical
so i thought what i showed was the ultra safe clean way to dispose of an
object

it may be superfluous but is it harmful? wrong? computationally expensive?
time consuming?

[Lyle Fairfield]

"MP" <no****@Thanks. comwrote in
news:lK******** ***********@tor nado.rdc-kc.rr.com:

"Lyle Fairfield" <ly***********@ aim.comwrote in message
news:Xn******** *************** **********@216. 221.81.119...

>"MP" <no****@Thanks. comwrote in
news:Nr******* ************@to rnado.rdc-kc.rr.com:

in ado to cleanup
If Rs.State = adStateOpen Then
Rs.Close
Set Rs = Nothing
End if

IMO, this is entirely superfluous.

Please, post an example of a situation where omitting this code
caused a problem.

--
Lyle Fairfield

I don't have such an example.
by now it should be obvious I'm a rank beginner. <g>
I thought that was how it was done since that's what i've seen in my
research into learning ado

for example...copie d from some newsgroup posting
From: "Veign" <NO***********@ veign.com>
Subject: Re: ADOX Columns
Date: Thursday, August 19, 2004 9:30 AM
<snip>

'Cleanup
If (RS.State And adStateOpen) = adStateOpen Then RS.Close
Set RS = Nothing

If (CNN.State And adStateOpen) = adStateOpen Then CNN.Close
Set CNN = Nothing

coming from that source (Chris Hanscom, MVP http://www.veign.com)
i had no reason to question the viability of the method

I assumed that if a recordset isn't open rs.close would throw an
error...haven't tested that just seemed logical
so i thought what i showed was the ultra safe clean way to dispose of
an object

it may be superfluous but is it harmful? wrong? computationally
expensive? time consuming?

MVPs are often not the sharpest knives in the dishwasher.

If one includes this, why not set all your numeric variables to zero amd
your strings to a zero length?

And whatcha going to do when using ADO in a language/script/technology
which does not have a "Nothing" (nor a "Set" either?)

DAO's problems are not reasons for determining how ADO is coded.

But don't think I'm anti-DAO. DAO was good enough for my father, and my
grandfather too, and it's good enough for me.

--
Lyle Fairfield