devil microsoft access....

  • Cae
  • Expert
  • Expert
  • User avatar
  • Posts: 734

Post 3+ Months Ago

ok, i need to know how to select the number of distinct entries in a colomn out of a MS Access database...

at first i tried using COUNT(DISTINCT blah) but that wouldnt work, so then i searched on google and found out that it doesnt work of access because microsoft is stupid, incopotent, power hungry, worthless...

back to the topic at hand... anyone know how to do a distinct count for MS Access??? ty
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23456
  • Loc: Woodbridge VA

Post 3+ Months Ago

A combination of the information in this link:

http://www.exceltip.com/st/Count_unique ... l/520.html

And this discussion on it:

http://www.excelforum.com/t120966-s

Those appear close to what you are trying to do, except that they were written for Excel. You may only have to modify it slightly for Access.
  • Troubadour
  • Graduate
  • Graduate
  • User avatar
  • Posts: 137
  • Loc: Melbourne, Australia

Post 3+ Months Ago

Hey there....


"SAMS teach yourself SQL in ten minutes" by "Ben Forta"
Its about 15 dollars or so to buy.

It is a little pocket book that it is written for propgrammers who use databases at the back end, but don't need to know all the "crap" that say a DBA would need to know.

The book is arranged to give examples for
Oracle
MS SQL
Postgre
Sybase
MYSQL

and a couple of others whos names escapes me....

Anyway, getting back to you question, the SQL command you want to try is...

Damn... I can't find it... I have a little cheat sheet here that I use all the time for things just like this... and cannot find it....

I will edit this post when I find it.... it is here honest...
I was using just a couple of days ago...

BBS.

You know... I can't find it still...
What version of MS-Office are you using?
This Knowledge base articles gives the impression you can use the DISTINCT reserved word with Access 2003.

http://office.microsoft.com/assistance/preview.aspx?AssetID=HP010322051033&CTT=1&Origin=EC790000701033&QueryID=9gL5tWM2n&Query=distinct+rows+in+access&Scope=TC%2cHP%2cHA%2cRC%2cFX%2cES%2cEP%2cDC%2cXT
  • Carnix
  • Guru
  • Guru
  • User avatar
  • Posts: 1098

Post 3+ Months Ago

Give this a try, it's worked for me in in the past for MS Access. You're milage may vary. I was connecting to an MS Access 2000 mdb file. I really dislike Access with a passion though. You might consider using MSDE (MS SQL Server 2000 Desktop Engine) instead, it's not a full SQL Server (for what that's worth), but it's better than Access, and it's free to use in any way you want, including resale if that's your game.

Code: [ Select ]

set dbconnection = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")
conn.open "CONNECT_STRING"
get_count ="SELECT COUNT(*) as count FROM [YOURTABLE]"
set rs = dbconnection .execute(get_count)
if((rs("count"))*1 = 0) then
 Response.Write("Nothing to see here... move along.")
 rs.close
 set rs = nothing
else
 Response.Write("Found " & rs("count") & " Records")
 rs.close
 set rs = nothing
end if
dbconnection.close
set dbconnection = nothing 
  1. set dbconnection = Server.CreateObject("ADODB.Connection")
  2. set rs = Server.CreateObject("ADODB.Recordset")
  3. conn.open "CONNECT_STRING"
  4. get_count ="SELECT COUNT(*) as count FROM [YOURTABLE]"
  5. set rs = dbconnection .execute(get_count)
  6. if((rs("count"))*1 = 0) then
  7.  Response.Write("Nothing to see here... move along.")
  8.  rs.close
  9.  set rs = nothing
  10. else
  11.  Response.Write("Found " & rs("count") & " Records")
  12.  rs.close
  13.  set rs = nothing
  14. end if
  15. dbconnection.close
  16. set dbconnection = nothing 


*EDIT: Sorry, one comment I forgot to add: This is ASP. If you're using .NET or VBA, you'll need to adjust the syntax a bit, however, the SQL should still be valid.

.c

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: No registered users and 89 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.