Group name field in SQL-query

[cifjha]

Hi,
I'am trying to use a "SELECT SUM(tbl.field)" SQL-query inside a grouping and want to use the Group name field to calculate the different values, but I don't get this right.

Crystal help says:
"group name fields look like function calls. However, they are really shorthand notation for a report field.
Group name field: GroupName({Orders.Ship Via})."

I do not understand this and would be very grateful if someone could help me.


My sql-query look like this:
SELECT SUM(`tbl`.`field1´)
FROM u_tbl
WHERE `tbl`.`field2`=2 and `tbl`.`field3`=Groupname({`tbl`.`field4`})


I'am using CR10 and MS Access.

Thank you for your help!

/

 

[synapsevampire]

Are you using a Add Command database object?

What connectivity are you using to Access, ODBC?

Standard SQL syntax is:

SELECT SUM(`tbl`.`field1´)
FROM u_tbl
WHERE `tbl`.`field2`=2
GROUP by
`tbl`.`field4`


The simple means would be to just create a report with the table, then insert the table.groupfield using insert group, then drop the field to sum into the details, right click and select Insert->Summary

Now the field from from the details and suppress the details.

You can check if the SQL is passed using Database->Show SQL Query if you're using ODBC, but Access isn't a real SQL database, so direct connectivity won't show it, and direct is faster.

Hope this helps.

-k

 

[cifjha]

Thank you very much for your answer!

What I want to do is to print the summary in the group header before I print all the separate parts in the details section.

I have tried to use a Running total but I don't get it to work outside the details, why I thougth I use a SQL Summary instead.

Using your SQL-query gives an error stating that "at most one record can be returned by the subquery".

I have tried using other sql-querys that works fine. I'am using an ODBC connection.

Thanks for your time

/