Distinct.... and count together

[Sonnich]

Basically I have a table with:

field1, field2
a, 1
a, 2
a, 2 (this repeats!)
b, 1

and wanted result is:

a:2
b:1
where someotherfield=something.

I have this:

SELECT DISTINCT field1, Count(field2) AS field2a
FROM blah
WHERE (((field3)=Forms!....))
GROUP BY field1

so I get a=3, instead of 2.
In other SQLs I can use Count( DISTINCT field2) AS field2a, but not in
access.

Any ideas?
Can I use this in design view (for people who does not use SQL)

/S

 

[Guest]

How about:

SELECT T1.Field1, Count(T1.Field2) as Something
FROM (SELECT DISTINCT Field1, Field2 FROM yourTable) as T1
GROUP BY T1.Field1

 

[Sonnich]

How about:

SELECT T1.Field1, Count(T1.Field2) as Something
FROM (SELECT DISTINCT Field1, Field2 FROM yourTable) as T1
GROUP BY T1.Field1

Should have thouht of that....

Thanks.

But still - are there any way to do this in the graphic design view?

/S