Group by in a Query

[key]

Hi all,

I've got the following (dynamic) SQL query:

SQLString = "SELECT field1, field2 FROM table1 WHERE ID=" & cboId & "AND No.=" & cboNo

I need to group this SQLString by ID. I've tried several ways and every time got the error message.

Do you have any suggestions where I should add the GROUP BY clause?

Many thanx,

Key

 

[Drevlin]

What are you trying to do with the group by? If you use group by you need an aggragate function for the other fields in your table (i.e. Count, Sum...)

I would suggest actually creating the query you want with your Where clause filled in using a valid values, then copy and past the SQL into your code exchanging the actual values with your variables.

That way you know the SQL you are using is valid before you throw in your variables.

I hope this makes sense.

 

[key]

The query works, but if I want to run it

in VBA it sucks!

 

[Drevlin]

Sucks how?
What is it you want to do and what is it that it isn't doing?

 

[Jacob Mathai]

Let me give you an example of using GROUP BY.

A table has 3 columns. They are dept (text), employee name (text) and hours(number).

Now you want to summarize HOURS by dept. Obviously we should not include Employee name.

Query :

Select dept,sum(hours) from tblXXX
group by dept;


Hope this gives you some idea of using 'GROUP BY'