MS Acess SQL Help

[cpals]

Hey,

I'm working on a database project for class and am having a problem with a statement I need to make. I first did this in SQL and it worked fine, but it doesn't work in Access.

I want to count the number of unique instructors (for example) that I have in a column. For sql server I did this:

COUNT (DISTINCT tblinstructor.id) as numberofinstructors

But in access that does not work, but I can just do the COUNT without the DISTINCT statement. Is there anyway around this?

Thanks.

 

[Descartes]

As you found out, Access doesn't allow you to count(distinct ...); however, you could do the following:

select numberofinstructors, count(*) as total_instructors
from yourtable
group by numberofinstructors

Not nearly as flexible, but you could embed it as a subquery if you needed to include more fields in a non-aggregated statement (i.e. not using group by).

 

[Argo]

Access SQL is very weak. Last I checked (I believe it was Access 2000) it dind't even support nested queries.

 

[Descartes]

Originally posted by: Argo
Access SQL is very weak. Last I checked (I believed it was Access 2000) it dind't even support nested queries.

I do nested queries all the time in Access (2k/XP, I haven't tried it in 97).

 

[BFG10K]

Last I checked (I believe it was Access 2000) it dind't even support nested queries.

Uh, even Access 97 supports nested queries just fine. If you're having problems generating SQL code in Access then build the query(ies) first using design view instead. Then when you've finished switch back to SQL view and you can see exactly what Access has built.

I'd build a query first using a group by on the instructors and then count the number of rows returned.