sql statement

[azrina]

I generate report with Data Report without using Data Environment.

I have table like below
weight(NoRujukan,Sampel)

I want the data of the table group by NoRujukan.

My sql statement is,

rs.Open "SELECT weight.NoRujukan, weight.Sampel FROM weight group by NoRujukan", conn, adOpenStatic, adLockOptimistic 

But, when execute, the error of the sql is something about aggregate function.
Wht does it mean?

Can anyone give me a sample of how to group data report?

[BruceG]

Aggregate functions are functions like SUM, COUNT, AVG, etc. When doing a GROUP BY query, any fields in the SELECT clause that are not included in the GROUP BY clause must use an aggregate function. For example, your query could be corrected as follows:

rs.Open "SELECT weight.NoRujukan, [b]SUM(weight.Sampel)[/b] FROM weight group by NoRujukan", conn, adOpenStatic, adLockOptimistic 

[azrina]

Originally posted by BruceG

rs.Open "SELECT weight.NoRujukan, [b]SUM(weight.Sampel)<b>Aggregate functions are functions like SUM, COUNT, AVG, etc. When doing a GROUP BY query, any fields in the SELECT clause that are not included in the GROUP BY clause must use an aggregate function. For example, your query could be corrected as follows:
</b> FROM weight group by NoRujukan", conn, adOpenStatic, adLockOptimistic 

[/b]

I use the code above and there is error which is --->DataField 'Sampel' not found.

If i didnt use the "SUM" and "GROUP BY", the report can be displayed.

Why is that so?
Thank You in advance.

[leinad31]

Originally posted by azrina
I use the code above and there is error which is --->DataField 'Sampel' not found.

If i didnt use the "SUM" and "GROUP BY", the report can be displayed.

Why is that so?
Thank You in advance.

Sum is an expression hence the result will be automatically named Expr1 unless you indicate the fieldname/alias it will use. Since your using the fieldname in the datareport control's datafield property, indicate the alias.

'Query
rs.Open "SELECT weight.NoRujukan, SUM(weight.Sampel) [b]AS AGGR_Sampel[/b] FROM weight group by NoRujukan", conn, adOpenStatic, adLockOptimistic

'In datareport
DataReport1.Sections("Details").Controls("txtSampel").DataField = "AGGR_Sampel"