Getting Totals

[Fakhruddin Zavery]

Hello All,

I have couple of tables namely Customer and Suppliers and want to create a
form that would display a Year To Date business done with each customer and
supplier differently.

For Customers all the business is recorded in a table called Trip and a
filed called FareAgreed. Now the idea is for each customer I would want a
total of all teh FareAgreed displayed in one box when I view the record.

I can do this using a report which is based on a query and by selecting all
the customers and the fare agreed and doing a group by on a report i get my
data.

However I want this sort of information on a form, all the help will be
appreciated.

Thanks and Regards
Zavery

 

[Steve Schapel]

Zavery,

One approach would be to put a subform on the Customer form. The
subform would be based on the Trip table, and would show the Trip
records for the current customer record (controlled via the Link Master
Fields /Link Child Fields properties of the subform). Then you can put
a textbox in the Footer of the subform, to show the total FareAgreed.

Another approach would be a textbox on the Customer form, with its
Control Source property set to the equivalent of:
=DSum("[FareAgreed]","Trip","[CustomerID]=" & [CustomerID])

 

[Fakhruddin Zavery]

Hello Steve,

Thanks for your thoughts however if I try and use the DSum function I get an
error and if I change slightly from your given example I get a total of all
the FareAgreed from all the customer.

With your example I get an error and with mine I get a total of all. This is
what I changed your expression to
=DSum("[FareAgreed]","Trip","[CustomerID]"= '[CustomerID]')

Any thoughts where we are going wrong?

Thanks and Regards

Zavery,

One approach would be to put a subform on the Customer form. The subform
would be based on the Trip table, and would show the Trip records for the
current customer record (controlled via the Link Master Fields /Link Child
Fields properties of the subform). Then you can put a textbox in the
Footer of the subform, to show the total FareAgreed.

Another approach would be a textbox on the Customer form, with its Control
Source property set to the equivalent of:
=DSum("[FareAgreed]","Trip","[CustomerID]=" & [CustomerID])

--
Steve Schapel, Microsoft Access MVP

Hello All,

I have couple of tables namely Customer and Suppliers and want to create
a form that would display a Year To Date business done with each customer
and supplier differently.

For Customers all the business is recorded in a table called Trip and a
filed called FareAgreed. Now the idea is for each customer I would want a
total of all teh FareAgreed displayed in one box when I view the record.

I can do this using a report which is based on a query and by selecting
all the customers and the fare agreed and doing a group by on a report i
get my data.

However I want this sort of information on a form, all the help will be
appreciated.

Thanks and Regards
Zavery

 

[Steve Schapel]

Fakhruddin,

What is the error?

Is CustomerID the exact name of the field? Is CustomerID a text (rather
than number)? If so, try like this...
=DSum("[FareAgreed]","Trip","[CustomerID]='" & [CustomerID] & "'")

Spread them out for clarity, it is like this:
=DSum("[FareAgreed]","Trip"," [CustomerID] = ' " & [CustomerID] & " ' ")

 

[Fakhruddin Zavery]

Steve,

Thanks it worked fine

Yes CustomerID is a Text field and I guess that's why it was causing a
problem.

Thanks and Regards
Fakhruddin

Fakhruddin,

What is the error?

Is CustomerID the exact name of the field? Is CustomerID a text (rather
than number)? If so, try like this...
=DSum("[FareAgreed]","Trip","[CustomerID]='" & [CustomerID] & "'")

Spread them out for clarity, it is like this:
=DSum("[FareAgreed]","Trip"," [CustomerID] = ' " & [CustomerID] & " ' ")

--
Steve Schapel, Microsoft Access MVP

Hello Steve,

Thanks for your thoughts however if I try and use the DSum function I get
an error and if I change slightly from your given example I get a total
of all the FareAgreed from all the customer.

With your example I get an error and with mine I get a total of all. This
is what I changed your expression to
=DSum("[FareAgreed]","Trip","[CustomerID]"= '[CustomerID]')