VB Count Distinct

[Guest]

Hi,

I am having a problem with DISTINCT and COUNT using Access 2003 VB. I am
using a form to pull information and a report to display it. When I run the
query below, it will count every instance of the USER_ID, not just the
DISTINCT ones. So, I get a return of 64 instead of what it should really be
which is 7.
Any thoughts on how to properly use DISCTINCT AND COUNT in this case?
Thanks.

recordSource = "SELECT DISTINCT COUNT(table.USER_ID) as count FROM table
WHERE table.CLIENTID='" & _
Forms("form").txtClientID.Value & "' AND " & _
" TIME_STAMP BETWEEN #" & Forms("form").txtFrom.Value &
"# AND #" & _
Forms("form").txtTo.Value & "#"
DoCmd.OpenReport "Report", acViewDesign
Set obsReport = Application.Reports("Report")
obsReport.recordSource = recordSource
DoCmd.Close acReport, "Report", acSaveYes
DoCmd.OpenReport "Report", acViewPreview

 

[Tim Ferguson]

I am having a problem with DISTINCT and COUNT using Access 2003 VB. I
am using a form to pull information and a report to display it. When
I run the query below, it will count every instance of the USER_ID,
not just the DISTINCT ones. So, I get a return of 64 instead of what
it should really be which is 7.

The way you have written this query is legal in Jet but, as you have
noticed, wrong. What you should want is

SELECT COUNT DISTINCT(UserID) FROM etc.

but that is not supported in Jet. You will have to go a long way round like

PARAMETERS InputClientID NUMERIC,
TimeFrom DATETIME,
TimeTo DATETIME;

SELECT COUNT(*) AS NumOfUsers
FROM
( SELECT DISTINCT UserID
FROM table
WHERE ClientID = InputClientID
AND TimeFrom <= TimeStamp
AND TimeStamp <= TimeTo
)

Subqueries are a bit slower in Jet, but as long as the table is decently
indexed then it should not be too bad.

By the way, using formal parameters will make the thing run quicker and
safer too.

Hope that helps


Tim F

 

[Guest]

I tried the nested SELECT statement. My code now looks like the following
and I get a "Run-time error '3131': Syntax error in FROM clause"

"SELECT COUNT(*) as count FROM (SELECT table.USER_ID FROM table WHERE
table.CLIENTID='" & _
Forms("form").txtClientID.Value & "' AND " & _
" TIME_STAMP BETWEEN #" & Forms("form").txtFrom.Value &
"# AND #" & _
Forms("form").txtTo.Value & "#)"

Any Thoughts?
Thanks,
Chris

 

[onedaywhen]

Tim Ferguson wrote ...

Subqueries are a bit slower in Jet

May I ask, subqueries are slower than what exactly?
Many thanks,
Jamie.

--

 

[Douglas J. Steele]

Does the subquery return valid data?

SELECT table.USER_ID FROM table WHERE table.CLIENTID='" & _
Forms("form").txtClientID.Value & "' AND " & _
" TIME_STAMP BETWEEN #" & Forms("form").txtFrom.Value & "# AND #" & _
Forms("form").txtTo.Value & "#)"

Is CliendID a text field? The way you've structured that query, it must be.
If it's not, get rid of the single quotes you're putting around the value.

 

[Guest]

Yes, the subquery returns valid data when submitted as a stand-alone query.
The field is a text field on a form. I tried removing the single quotes. I
still get the same ""Run-time error '3131': Syntax error in FROM clause"

 

[Tim Ferguson]

I get a "Run-time error '3131': Syntax error in FROM clause"

"SELECT COUNT(*) as count FROM (

My guess would have been the illegal use of the keyword count as a column
name. Try using

... AS [count] FROM (...

or use a legal column name like

... AS CountOfRecords FROM (...

although, surprisingly, using Count does not raise an error on my machine.
Like Doug, I would next suspect the WHERE clause and the adhoc string
formation. You really are taking an awful lot on trust by just stuffing
user-typed strings into an SQL command, particularly when you don't give
yourself a chance to see what is going off to the db engine. Try something
normal like this:

strSQL = "SELECT COUNT(*) etc etc"

& " WHERE ClientID = " & Forms!form!txtClientID" _
& " AND " & format(cdate(Forms!form!txtFrom), "\#yyyy\-mm\-dd\#") _
& " <= Time_Stamp " & _
& " AND Time_Stamp <= " _
& format(cdate(Forms!form!txtFrom), "\#yyyy\-mm\-dd\#") _


' the next line is the _only_ way to debug SQL statements
MsgBox strSQL


and so on.

Hope it helps


Tim F

 

[Tim Ferguson]

May I ask, subqueries are slower than what exactly?

Joins

Tim F

 

[Guest]

Thanks for the input from both of you...
I have shortened my SQL statement to the following:

recordSource = "SELECT COUNT (*) as userid FROM (SELECT DISTINCT
table.USER_ID FROM table)"

I now get the error: "Could not find file 'C:\Documents and
Settings\username\My Documents\SELECT DISTINCT table.mdb' "

Any ideas on this one?
Thanks again

I get a "Run-time error '3131': Syntax error in FROM clause"

"SELECT COUNT(*) as count FROM (

My guess would have been the illegal use of the keyword count as a column
name. Try using

... AS [count] FROM (...

or use a legal column name like

... AS CountOfRecords FROM (...

although, surprisingly, using Count does not raise an error on my machine.
Like Doug, I would next suspect the WHERE clause and the adhoc string
formation. You really are taking an awful lot on trust by just stuffing
user-typed strings into an SQL command, particularly when you don't give
yourself a chance to see what is going off to the db engine. Try something
normal like this:

strSQL = "SELECT COUNT(*) etc etc"

& " WHERE ClientID = " & Forms!form!txtClientID" _
& " AND " & format(cdate(Forms!form!txtFrom), "\#yyyy\-mm\-dd\#") _
& " <= Time_Stamp " & _
& " AND Time_Stamp <= " _
& format(cdate(Forms!form!txtFrom), "\#yyyy\-mm\-dd\#") _


' the next line is the _only_ way to debug SQL statements
MsgBox strSQL


and so on.

Hope it helps


Tim F

 

[Jamie Collins]

Joins

Can you provide a suitable test? I recently tested and found a
subquery approach was considerable faster than an INNER JOIN approach,
both with and without indexes:

http://groups.google.com/[email protected]

Many thanks,
Jamie.

--

 

[Tim Ferguson]

recordSource = "SELECT COUNT (*) as userid FROM (SELECT DISTINCT
table.USER_ID FROM table)"

I now get the error: "Could not find file 'C:\Documents and
Settings\username\My Documents\SELECT DISTINCT table.mdb' "

Are you sure those are not square brackets: FROM [select distinct..

By the way, isn't TABLE a reserved word too? It's never crossed my mind to
use that for a table name

Tim F

 

[Tim Ferguson]

(e-mail address removed) (Jamie Collins) wrote in

Can you provide a suitable test? I recently tested and found a
subquery approach was considerable faster than an INNER JOIN approach,
both with and without indexes:

No I can't, but I am interested in this result. It probably comes from old
versions of Access, when the accepted wisdom was that nested subqueries
performed like dogs.

Generally I use a subquery when it's logically necessary and not otherwise,
so I would not have found a reason to compare them.

B Wishes


Tim F

 

[Bas Cost Budde]

By the way, isn't TABLE a reserved word too? It's never crossed my mind to
use that for a table name

Tried this immediately. Access doesn't wrinkle at a field named Field,
or a table named Table. It even allows a table to be called SELECT.
(Kids, don't do this at home)

Yeph, as long as you can have any name between brackets, why check?

Grum

 

[Jamie Collins]

It probably comes from old
versions of Access

Access? I was using a Jet 4.0 .mdb and the OLE DB provider. Do you
think the version of MS Access is relevant?

Generally I use a subquery when it's logically necessary and not otherwise,
so I would not have found a reason to compare them.

But you did compare them i.e. earlier in this thread!

Jamie.

--