DSUM in Group Footer

[Guest]

I am trying to use the DSum() function to calculate a total number of records
that are buyers for each salesman.

The syntax I entered in the control source of TotalBuyers is =DSum("[Count
of Leads]","[Agent Leads]","[Lead Type]='Buyer'").

The problem is it totals for all record not just the group.

 

[Duane Hookom]

Normally DSum() is a horrible waste of resources in a report. This is
especially true if the record source of your report is the "domain" you wish
to query.

However, you would need to add something to your where clause in the DSum()
like
=DSum("[Count of Leads]","[Agent Leads]","[Lead Type]='Buyer' AND
SalesmanID=" & [SalesmanID]).

 

[Guest]

Thanks for the quick response, appreciate it. I am open to any other way to
accomplish this. I added your suggestion and get an "#error" as a result.

Normally DSum() is a horrible waste of resources in a report. This is
especially true if the record source of your report is the "domain" you wish
to query.

However, you would need to add something to your where clause in the DSum()
like
=DSum("[Count of Leads]","[Agent Leads]","[Lead Type]='Buyer' AND
SalesmanID=" & [SalesmanID]).


--
Duane Hookom
MS Access MVP
--

I am trying to use the DSum() function to calculate a total number of
records
that are buyers for each salesman.

The syntax I entered in the control source of TotalBuyers is
=DSum("[Count
of Leads]","[Agent Leads]","[Lead Type]='Buyer'").

The problem is it totals for all record not just the group.

 

[Guest]

My Report looks like this.

LEAD STATUS LEAD TYPE LEAD COUNT
AGENT: Smith Prospect Buyer 2
Client Buyer 3
Prospect Seller 1


Total Lead: 6 Buyers X Sellers X

AGENT: Jones (REPEAT)

The 'X" are what I cant get to total correctly.

Normally DSum() is a horrible waste of resources in a report. This is
especially true if the record source of your report is the "domain" you wish
to query.

However, you would need to add something to your where clause in the DSum()
like
=DSum("[Count of Leads]","[Agent Leads]","[Lead Type]='Buyer' AND
SalesmanID=" & [SalesmanID]).


--
Duane Hookom
MS Access MVP
--

I am trying to use the DSum() function to calculate a total number of
records
that are buyers for each salesman.

The syntax I entered in the control source of TotalBuyers is
=DSum("[Count
of Leads]","[Agent Leads]","[Lead Type]='Buyer'").

The problem is it totals for all record not just the group.

 

[Duane Hookom]

What do you get if you add text boxes in your footer with control sources:

=Sum(Abs([Lead Type]="Buyer"))
=Sum(Abs([Lead Type]="Seller"))

--
Duane Hookom
MS Access MVP
--

My Report looks like this.

LEAD STATUS LEAD TYPE LEAD COUNT
AGENT: Smith Prospect Buyer 2
Client Buyer 3
Prospect Seller 1


Total Lead: 6 Buyers X Sellers X

AGENT: Jones (REPEAT)

The 'X" are what I cant get to total correctly.

Normally DSum() is a horrible waste of resources in a report. This is
especially true if the record source of your report is the "domain" you
wish
to query.

However, you would need to add something to your where clause in the
DSum()
like
=DSum("[Count of Leads]","[Agent Leads]","[Lead Type]='Buyer' AND
SalesmanID=" & [SalesmanID]).


--
Duane Hookom
MS Access MVP
--

I am trying to use the DSum() function to calculate a total number of
records
that are buyers for each salesman.

The syntax I entered in the control source of TotalBuyers is
=DSum("[Count
of Leads]","[Agent Leads]","[Lead Type]='Buyer'").

The problem is it totals for all record not just the group.

 

[Guest]

We are getting close. This counts the number of buyers and Sellers, which
with the example I included equates to Buyers = 2 and Sellers = 1. It
should be Buyers = 5 Sellers = 1. Then recalculate on each agent. So I
thing I need to do something based "Lead Count" where Lead Type = "Seller".

What do you get if you add text boxes in your footer with control sources:

=Sum(Abs([Lead Type]="Buyer"))
=Sum(Abs([Lead Type]="Seller"))

--
Duane Hookom
MS Access MVP
--

My Report looks like this.

LEAD STATUS LEAD TYPE LEAD COUNT
AGENT: Smith Prospect Buyer 2
Client Buyer 3
Prospect Seller 1


Total Lead: 6 Buyers X Sellers X

AGENT: Jones (REPEAT)

The 'X" are what I cant get to total correctly.

Normally DSum() is a horrible waste of resources in a report. This is
especially true if the record source of your report is the "domain" you
wish
to query.

However, you would need to add something to your where clause in the
DSum()
like
=DSum("[Count of Leads]","[Agent Leads]","[Lead Type]='Buyer' AND
SalesmanID=" & [SalesmanID]).


--
Duane Hookom
MS Access MVP
--

I am trying to use the DSum() function to calculate a total number of
records
that are buyers for each salesman.

The syntax I entered in the control source of TotalBuyers is
=DSum("[Count
of Leads]","[Agent Leads]","[Lead Type]='Buyer'").

The problem is it totals for all record not just the group.

 

[Duane Hookom]

Try:
=Sum(Abs([Lead Type]="Buyer") * [Lead Count])
=Sum(Abs([Lead Type]="Seller") * [Lead Count])


--
Duane Hookom
MS Access MVP
--

We are getting close. This counts the number of buyers and Sellers, which
with the example I included equates to Buyers = 2 and Sellers = 1. It
should be Buyers = 5 Sellers = 1. Then recalculate on each agent. So I
thing I need to do something based "Lead Count" where Lead Type =
"Seller".

What do you get if you add text boxes in your footer with control
sources:

=Sum(Abs([Lead Type]="Buyer"))
=Sum(Abs([Lead Type]="Seller"))

--
Duane Hookom
MS Access MVP
--

My Report looks like this.

LEAD STATUS LEAD TYPE LEAD COUNT
AGENT: Smith Prospect Buyer 2
Client Buyer
3
Prospect Seller
1


Total Lead: 6 Buyers X Sellers X

AGENT: Jones (REPEAT)

The 'X" are what I cant get to total correctly.

:

Normally DSum() is a horrible waste of resources in a report. This is
especially true if the record source of your report is the "domain"
you
wish
to query.

However, you would need to add something to your where clause in the
DSum()
like
=DSum("[Count of Leads]","[Agent Leads]","[Lead Type]='Buyer' AND
SalesmanID=" & [SalesmanID]).


--
Duane Hookom
MS Access MVP
--

I am trying to use the DSum() function to calculate a total number of
records
that are buyers for each salesman.

The syntax I entered in the control source of TotalBuyers is
=DSum("[Count
of Leads]","[Agent Leads]","[Lead Type]='Buyer'").

The problem is it totals for all record not just the group.