Problem with group-level statistics

[Padma Sri]

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!

 

[Marshall Barton]

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))

 

[Guest]

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.

 

[Marshall Barton]

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]