How Summarize and use other Fields

[joseprez]

When I do
"SELECT SUM(Table1.Field1) as VariableSumField1 " _
& "FROM Table1, Table2 WHERE Criteria"
this work great!

Now I want use other fields from both tables. I try this
"SELECT SUM(Table1.Field1) as VariableSumField1, " _
& "Table1.Field2, Table1.Field3, Table2.Field1 " _
& "FROM Table1, Table2 WHERE Criteria"
but this is wrong.

How I can sumarize and use other fields from both tables in my SQL Command?

 

[MeanGreen]

Include a GROUP BY clause for your other fields.

Hope this helps.

 

[Zathras]

Your first statement:

"SELECT SUM(Table1.Field1) as VariableSumField1 " _
& "FROM Table1, Table2 WHERE Criteria"

Produces one record. If you want to continue to get only one record with your second statement, it is only logically possible if all of the Field2, Field3, etc. all have the same value in all of the selected records. If that is indeed the case then the following might be what you want:

"SELECT SUM(Table1.Field1) as VariableSumField1, " _
& "MAX(Table1.Field2) as T1Field2, MAX(Table1.Field3) as T1Field3, MAX(Table2.Field1) as T2Field1 " _
& "FROM Table1, Table2 WHERE Criteria"

The point is, if any aggregate functions are used in the SELECT clause, then every column selected must be an aggregate function or else must be referenced in a GROUP BY clause as MeanGreen has indicated.