discluding in group by query

[spacemonkey]

hi, i am a webdesigner and have just started using Dreamweaver Ultra-dev but it is quite limited. i dont know much SQL terminology so bare with my ignorance. I am using Microsoft Access 2000

i need to select three fields from a table, one has to be the id. but i want to combine any identical entrys in the other two fields so they do not repeet themselves.

so far i have managed to use the GROUP BY statment to do this but i cannot include the id in the query without putting it in the GROUP BY statment, which defies the point all together.

so i was wondering if there was any way of discluding the id from the GROUP BY

for example

GROUP BY [this],[that] BUT NOT [id]

Any help appreciated.
thank you
jamie

 

[MikeLacey]

Hi Jamie,

So -- you might have some a table like this then.

create table tmp(
pk varchar(10) primary key,
col1 varchar(10),
col2 varchar(10)
col3 etc....
)

and data like this:
[tt]
select * from tmp;
pk[tab]col1[tab]col2[tab]col3
==[tab]====[tab]====[tab]====
1[tab][tab]a[tab]A[tab][tab]Widgets
2[tab][tab]a[tab]A[tab][tab]Large Widgets
3[tab][tab]b[tab]B[tab][tab]Small ones
4[tab][tab]b[tab]B[tab][tab]Really Small Ones
5[tab][tab]c[tab]C[tab][tab]Completely other stuff
[/tt]

I'm having difficulty in understanding what use it would be to exclude the primary key from a group by but still display it...

If you *could* do this, look what it would do....
[tt]
select pk, col1, col2 from tmp group by col1,col2 but not pk;
pk[tab]col1[tab]col2
==[tab]====[tab]====
1[tab][tab]a[tab]A
3[tab][tab]b[tab]B
5[tab][tab]c[tab]C
[/tt]
Which values for pk would the query return and what use would they be to you anyway?
The Group By clause is used when you're doing something like counting the occurences of a combination of data. It would be usual to write an SQL like this.
[tt]
select count(*), col1, col2 from tmp group by col1, col2;
count(*)[tab]col1[tab]col2
========[tab]====[tab]====
2[tab][tab]a[tab][tab]A
2[tab][tab]b[tab][tab]B
1[tab][tab]c[tab][tab]C
[/tt]
That's quite a long post for me -- I do hope it's clear enough to be of some use.
Mike
michael.j.lacey@ntlworld.com