"select count(distinct IP) from Web_stat" doesn't work 1

[Rydel]

Dear Access Gurus,

I use MS SQL Server, but I recently I had to deal with an Access database. There were a few unplesant surprises, as Access didn't understand Transact-SQL . Here is one glitch:

"select count(IP) from Web_stat" works fine
"select distinct IP from Web_stat" also works
but "select count(distinct IP) from Web_stat" doesn't work, giving a syntax error.

Question: how can I fix this error? Or how can I find out the number of the unique IP's in my database?

Thanks in advance!
---

http://www.geocities.com/rydel23/

---

 

[DougP]

If memory serves me correct, Access is based on ANSI SQL ’89, if that helps.
But you need to use the “Group By" function to get a distinct list.
Then to get a count I would use the "recordcount" function after that.

This is VBA code, Hope it helps.

Dim db as database, rst as recordset, SQL as string, X as Variant
Set db = CurrentDb
' SQL string.
SQL = "SELECT Web_stat.IP FROM Web_stat GROUP BY Web_stat.IP;"
Set rst = db.OpenRecordset(SQL)
Rst.movelast
X = rst.recordcount
Debug.print X


So X would contain the number of unique IP's
DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[Rydel]

DougP, thank you. I was aware of this solution though. The situation is that I don't want to change my ASP code (in many places) just because I changed a database from SQL Server to Access. What I am looking for is purely SQL solution, so that all we change in the code is the SQL statements themselves. Thanks again!
---

http://www.geocities.com/rydel23/

---

 

[magicrat]

"select count(IP) from Web_stat" works fine
"select distinct IP from Web_stat" also works
but "select count(distinct IP) from Web_stat" doesn't work, giving a syntax error.

Try this:
1)Define and save query #1 for distinct IP:
"Select Distinct IP from Web_stat"
as qryDistinctIP. Access lets you save queries and use them like tables.
2)Then define query #2 as:
Select Count(*) from qryDistinctIP

Once query #1 is defined and saved, you can substitute only query #2 as necessary in your code.
Hope this helps.