group by

[suu]

hello!

i can't seem to do a group by. what's wrong here?

SQLStmt = "SELECT [Nº], Equip, Imob, Marca, Modelo, Nome, [Divisão] FROM TBL_COMP_U WHERE [Divisão]='CA' GROUP BY [Nº]"

i've been searching manuals and this seems alright.

[A1ien51]

When you use GROUP BY you have to list everything that is in the SELECT paramters

Soan example


SELECT A1,A2,A3
FROM aTable
GROUP BY A1,A3,A2


Eric

[suu]

thank you.

it worked. only it seems as if it isn't grouping anything. i'm trying to do this group by so that values won't appear repeated on my combo box when i get them from my db. the combo holds the employe number and it appears repeated.
he values still appear repeated even though i'm doing this group by:

SQLStmt = "SELECT [Nº], Equip, Imob, Marca, Modelo, Nome, [Divisão] FROM TBL_COMP_U WHERE [Divisão]='CA' GROUP BY [Nº],Equip, Imob, Marca, Modelo, Nome, [Divisão]"

this doesn't give me any error. it works. but it isn't grouping the same Nº.
any idea why?

[raf]

You don't give info about the column, but one of them will have unique values. Like the [Nº]. If one of the variables where you group by is unique ... then everything will be indeed grouped but wount be noticable.

[suu]

i don't know if i understood you correctly but my DB lists the computers a person have on my company. and a single person can have several computers. which is why i'm doing the group by Nº, where Nº is the person's number. it is unique but gets repeated on this DB due to the number of PC he might have.

i.e.: if a person has two computers, in the combo it appears:

401773 - John Doe
401773 - John Doe

i need it to appear only one time.
i thought group by would do the trick.

[A1ien51]

If you are only pulling the names for the database then why are you pulling 20 other columns????

You can also try to use

SELECT distinct A1,A2

Eric

[raf]

Yes, but it will group on the combination of the included columns, so in fact only removing identical records and behaves the same as a DISTINCTROW (which is what you should use if you wanted the result you now get.)
GROUP BY only needs to be used if you also return the result of agregate functions like count(*) etc
If you only return tablecolumn, then you need to use DISTINCT or DISTINCTROW

If you only want the distinct values of one column (or in your case two 'bound' columns) then you can only include these two columnnames inside the fieldslist. So then you need

SQLStmt = "SELECT DISTINCTROW [Nº], Nome FROM TBL_COMP_U WHERE [Divisão]='CA'"

If you wan't to pupulate more then one dropdown with unique values, then you either need to use more then one select (recommended) or you need to make the values unique inside your recordset processing by only inluding records where the fvalue (for N° for instance) is different from thevalue in the previous record.

<edit>posts crossed </edit>

[suu]

this is all my fault.

the reason why i have so many other fields on my select is because, according to whatever you choose on the combo, you will later fill a grid.

on my combo it appears:

Nº - Nome

then whatever a person choose a grid will appear filled with:

Equip Imob Marca Modelo......

but i've solved it. i did the select you told me with just Nº and Nome and after that i did another select with all the necessary fields.

thank you for your help!

[suu]

this is all my fault.

the reason why i have so many other fields on my select is because, according to whatever you choose on the combo, you will later fill a grid.

on my combo it appears:

Nº - Nome

then whatever a person choose a grid will appear filled with:

Equip Imob Marca Modelo......

but i've solved it. i did the select you told me with just Nº and Nome and after that i did another select with all the necessary fields.

thank you for your help!