Dear Access Gurus, I use MS SQL 1

[uladzik]

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

---

 

[link9]

"SELECT count(IP) FROM Web_stat GROUP BY IP"

I think that will work --

Paul Prewett

 

[uladzik]

Dear link9,

"SELECT count(IP) FROM Web_stat GROUP BY IP"
will return counts for each unique ip, i.e. if you have:

ID IP
1 1.1.1.1
2 1.1.1.1
3 2.2.2.2
4 2.2.2.2
5 2.2.2.2

Your query will return:
2
3
---

http://www.geocities.com/uladzik/

---

 

[link9]

Then I guess it would be:

"SELECT count(IP), IP FROM Web_stat GROUP BY IP"

2 1.1.1.1
3 2.2.2.2

yes?

 

[uladzik]

You are correct. But unfortunately that's not what I was looking for. Your SQL statement returns the number of hits from each IP. I'd like to know how many unique IP's I have altogether. Actually I saw a good suggestion on another forum:
create a query
with "select * from Web_stat group by IP"
and then call "select count(*) from myQuery"
In case you have any other suggestions to improve this solution (to make a single SQL statement, not two separate ones), I'd be happy to hear. Thanks a bunch.
---

http://www.geocities.com/uladzik/

---