ADODB select distinct query 1

[brownfox]

I have an access table linked to a datagrid and I would like the datagrid to display only the DISTINCT values in each column. The table looks like this:

[tt]
[u]Age | County | School[/u]
U8  | Surrey | ST TERESA'S
U8  | Sussex | CHEAM
U9  | Sussex | PUTNEY HIGH
U10 | Surrey | DANES HILL
U11 | BERKS  | KNAPHILL
U11 | BERKS  | OLD PALACE
U12 | SURREY | ST TERESA'S
[/tt]

And I'd like the datagrid to display this:

[tt]
[u]Age | County | School[/u]
U8  | Surrey | ST TERESA'S
U9  | Sussex | PUTNEY HIGH
U10 | BERKS  | DANES HILL
U11 |        | KNAPHILL
U12 |        | OLD PALACE
    |        | CHEAM
[/tt]

"SELECT DISTINCT AGE,COUNTY,SCHOOL FROM mytable" doesn't work. Any help greatly appreciated.

 

[Xaplytz]

You need to add “ORDER BY” to your SQL-Select

 

[Alleyopp]

Well the 3rd party data grid that I use will allow you to add an item manually. In that case you would check if the previous value of the County is the same as the current if so the Add a "" else add the rs!County value.

Otherwise you could try grouping with Access and see what that does

Hope that helps
Alley

 

[brownfox]

Thanks 4 the replys. Adding "order by" doesn't help e.g if the sql is: "SELECT DISTINCT AGE,COUNTY,SCHOOL FROM mytable order by age"
I get multiple counties and schools. Alleyopp, this is all done at runtime based on info uploaded from excel so I cant change my ADODB control like that. If I could use 3 different select statements and bind each recordset to a different column in the datagrid - that might work. Is this possible I wonder?:
"SELECT DISTINCT AGE FROM mytable order by age" to datagrid col 1
"SELECT DISTINCT school FROM mytable order by school" to datagrid col 2
"SELECT DISTINCT county FROM mytable order by county" to datagrid col 3

 

[Xaplytz]

Try adding GROUP BY to the SQL-Select

 

[brownfox]

No luck, that simply orders my recordset better but still allows duplicates...

 

[MakeItSo]

Hi brownfox,

I'm afraid, it doesn't work like that:

In your table, you have
U10 | Surrey | DANES HILL

but you want
U10 | BERKS | DANES HILL

i.e. you want to treat the three columns independently DISTINCT.
No go PLUS: You get the wrong combination of data!
-->Better re-think your data entry.
Is it correct? Then your desired output is not!

Else you would need three queries, on efor each column, but that agian does not make sense!

Cheers,
Andy

[blue]The last voice we will hear before the world explodes will be that of an expert saying:
"This is technically impossible!" - Sir Peter Ustinov[/blue]
HP:

http://andygalambos.de

 

[MakeItSo]

P.S: Forget about the typos in last line...

 

[brownfox]

I think you are right MakeItSo what I'm trying to do is impossible that way, I am going to have to make a new table with distinct columns and simply select * from that table. Thanks 4 your advice