SQL Problem

  • Thread starter Thread starter Lee Roper
  • Start date Start date
L

Lee Roper

I have a table called Data. The key identifying field in
the table is called Claimno (claim number). I want my
users to be able to click a button on a form and get a
count of the number of unique claim numbers in the data
table. To do this, I have tried to build a SQL statement
that will select the Claimno field and GROUP BY that
field. I assume that I will have to do a separate SQL
statement to count the grouped claim numbers. If there is
an easier way to do this, I'd appreciate the info.

I was informed that I would need to create a stored query
so I searched Help on how to create a querydef. Based on
this, I created the following code:
Dim dbsRepricer as DAO.Database
Set dbsRepricer = CurrentDb
Dim rsClaims As DAO.Recordset
Dim qdfClaimno As QueryDef

With dbsRepricer
Set qdfClaimno = .CreateQueryDef("SELECT claimno
FROM Data GROUP BY claimno")
Set rsClaims = .OpenRecordset(qdfClaimno,
dbOpenTable)
End With

It keeps giving me a Type Mismatch error and highlights
the line for OpenRecordset. What am I doing wrong?

Thanks, Lee
 
Replace this line:
Set rsClaims = .OpenRecordset(qdfClaimno, dbOpenTable)

with this line:
Set rsClaims = qdfClaimno.OpenRecordset(qdfClaimno, dbOpenTable)
 
Lee said:
I have a table called Data. The key identifying field in
the table is called Claimno (claim number). I want my
users to be able to click a button on a form and get a
count of the number of unique claim numbers in the data
table. To do this, I have tried to build a SQL statement
that will select the Claimno field and GROUP BY that
field. I assume that I will have to do a separate SQL
statement to count the grouped claim numbers. If there is
an easier way to do this, I'd appreciate the info.

I was informed that I would need to create a stored query
so I searched Help on how to create a querydef. Based on
this, I created the following code:
Dim dbsRepricer as DAO.Database
Set dbsRepricer = CurrentDb
Dim rsClaims As DAO.Recordset
Dim qdfClaimno As QueryDef

With dbsRepricer
Set qdfClaimno = .CreateQueryDef("SELECT claimno
FROM Data GROUP BY claimno")
Set rsClaims = .OpenRecordset(qdfClaimno,
dbOpenTable)
End With

It keeps giving me a Type Mismatch error and highlights
the line for OpenRecordset. What am I doing wrong?
Click to expand...


The error comes from using dbOpenTable on something that is
not a table. You could use dbOpenSnapshot since you won't
be able to update any data returned by a group by query.


In this situation, you really don't need to use a querydef,
you can just use an SQL statement in the openrecordset:

Dim dbsRepricer as DAO.Database
Dim rsClaims As DAO.Recordset
Dim strSQL As String
Set dbsRepricer = CurrentDb

strSQL = "SELECT claimno, Count(*) As ClaimCount " _
& "FROM Data GROUP BY claimno"
Set rsClaims = dbsRepricer.OpenRecordset(strSQL", _
dbOpenSnapshot)
Me.txtUniqueClaims = rsClaims.RecordCount
. . .
rsClaims.Close
Set rsClaims = Nothing

That does a lot more than what you asked for as it will also
tell you how many claims each claim number has. If you have
no need for the additional information, then this will do
just what you asked:

strSQL = "SELECT Count(*) As UniqueClaims " _
& "FROM Data GROUP BY claimno"
Set rsClaims = dbsRepricer.OpenRecordset(strSQL", _
dbOpenSnapshot)
Me.txtUniqueClaims = rsClaims!UniqueClaims
 
That code's nice, but it's really overkill. The simplest
way would be use the following code:

txtControl = DCount("[Claimno]","tblClaim")
 
Back
Top