Criteria in the DSUM Function

[benton msimango via AccessMonster.com]

I got the follwoing DSUM FUCNTIOn: DSUM("APIMvmnt","HealthProduction","
[broker]=1000012946 ")

I wouldn't like to specify the broker number in the criteria part because it
only returns the values associated with this broker (1000012946),This formula
doesn't cater for other broker numbers i.e 10000003458,10000087977,
100000289722.How can i make it to cater for other brokers without specifying
a broker value in my criteria like i've done.

Yo help would be greatly appreciated

 

[Guest]

To sum for all brokers simply take out the criteria
DSum("APIMvmnt","HealthProduction")
To get a sum for each broker Create a 2 column query. Column 1 is the
Broker, column 2 is the APIMvmnt. Click the totals button. Leave column 1 at
group by
and change column 2 to Sum

 

[fredg]

I got the follwoing DSUM FUCNTIOn: DSUM("APIMvmnt","HealthProduction","
[broker]=1000012946 ")

I wouldn't like to specify the broker number in the criteria part because it
only returns the values associated with this broker (1000012946),This formula
doesn't cater for other broker numbers i.e 10000003458,10000087977,
100000289722.How can i make it to cater for other brokers without specifying
a broker value in my criteria like i've done.

Yo help would be greatly appreciated

The low tech way:
ColumnNameSUM("APIMvmnt","HealthProduction","[broker]= " & [Enter
broker#])

The above requires the user to know the correct BrokerID.


The more efficient way:

Make a new unbound form.
Add a combo box that will show the BrokerID and the Broker Name
fields.
Set the Bound Column to 1.
Set the Combo Widths property to
0";1"

Add a command button.
Code the button's Click event:
DoCmd.OpenQuery "QueryName"
DoCmd.Close acForm, Me.Name
Name this form "ParamForm"

In the Query use:
ColumnNameSUM("APIMvmnt","HealthProduction","[broker]= " &
forms!ParamForm!ComboBoxName)

Open the form.
Find the Broker Name in the combo box.
Click the command button.

The query will display just those records selected.
The Form will close.

It is quite possible that you may have more than one broker with the
same name. It might be advisable to add a 3rd column to the combo box
(City, or street Address, etc.) to help select the correct broker.