If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problem with group-level statistics
Hi
I have an Access 2002 report based off a tree-level structure. I use a query to get the report. I need to get a group-level sum of all the records where the parent name = group name. The problem is Dsum does not work for me in Access, even though my syntax is right. I et a #error So can you suggest a solution using the iif condition or a macro or event to get the result. In SQL, the query would look like: select sum(sales) where pname = gname group by gname; But in the report I still want to show all the other records also belonging to the group. I would appreciate your help immensely. Thanks! |
#2
|
|||
|
|||
Problem with group-level statistics
Padma Sri wrote:
I have an Access 2002 report based off a tree-level structure. I use a query to get the report. I need to get a group-level sum of all the records where the parent name = group name. The problem is Dsum does not work for me in Access, even though my syntax is right. I et a #error So can you suggest a solution using the iif condition or a macro or event to get the result. In SQL, the query would look like: select sum(sales) where pname = gname group by gname; But in the report I still want to show all the other records also belonging to the group. Not sure I followed all that, but I'll guess that you want a group footer text box with an expression something like: =Sum(IIf(pname = gname, sales, 0)) -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Problem with group-level statistics
I tried this but the expression always comes to 0. The
gname is the group level item while pname comes in each record. Can you further elaborate as this is the most promising solution I have seen. Not sure I followed all that, but I'll guess that you want a group footer text box with an expression something like: =Sum(IIf(pname = gname, sales, 0)) -- Marsh MVP [MS Access] . |
#4
|
|||
|
|||
Problem with group-level statistics
wrote:
I tried this but the expression always comes to 0. The gname is the group level item while pname comes in each record. Can you further elaborate as this is the most promising solution I have seen. The expression I posted is a standard way to sum a group's records that have a specific value. If you only get a zero result, it means there are no records in the group that match the IIf's condition. Double check the names to make sure they are the field names used in the report's record source table/query, not the name of a control in the report. -- Marsh MVP [MS Access] Not sure I followed all that, but I'll guess that you want a group footer text box with an expression something like: =Sum(IIf(pname = gname, sales, 0)) |
Thread Tools | |
Display Modes | |
|
|