If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Distinct.... and count together
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 |
#2
|
|||
|
|||
Distinct.... and count together
How about:
SELECT T1.Field1, Count(T1.Field2) as Something FROM (SELECT DISTINCT Field1, Field2 FROM yourTable) as T1 GROUP BY T1.Field1 -- Email address is not valid. Please reply to newsgroup only. "Sonnich" wrote: 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 |
#3
|
|||
|
|||
Distinct.... and count together
On Feb 28, 6:48 pm, Dale Fye wrote:
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 |
Thread Tools | |
Display Modes | |
|
|