Make a Sum of an Expression

[Guest]

Hello everybody,

I need some help in the following:

I have a report, where a field is an expression C = A * B.
Field A and B are changing every time the report is opened, by settig the
value of the ControlSource with a macro - so the are not constant.

So my problem is the following: I can list the field C int the report, but
cannot generate a sum of it at the bottom of the reprot. (The expression
Sum([C]) does not seem to work).

Please, help me.

Thank you in advance.

Tony

How can

 

[Jeff Boyce]

Tony

I believe you could use a Sum(A*B), rather than referring to C.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Jeff,

thank you for the quick reply..
Unfortunately i was trying this option, and the result was a blank sheet
instead of the report.

So I need another option.

Tony

„Jeff Boyce†ezt írta:

Tony

I believe you could use a Sum(A*B), rather than referring to C.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Hello everybody,

I need some help in the following:

I have a report, where a field is an expression C = A * B.
Field A and B are changing every time the report is opened, by settig the
value of the ControlSource with a macro - so the are not constant.

So my problem is the following: I can list the field C int the report, but
cannot generate a sum of it at the bottom of the reprot. (The expression
Sum([C]) does not seem to work).

Please, help me.

Thank you in advance.

Tony

How can

 

[Duane Hookom]

You may need to set the control source of your sum at the bottom of the
report in the same way that you are setting the control sources of Field A
and B. Since we don't know how/why you are doing this, I don't think we can
provide less vague support.

--
Duane Hookom
MS Access MVP
--

Jeff,

thank you for the quick reply..
Unfortunately i was trying this option, and the result was a blank sheet
instead of the report.

So I need another option.

Tony

"Jeff Boyce" ezt írta:

Tony

I believe you could use a Sum(A*B), rather than referring to C.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Hello everybody,

I need some help in the following:

I have a report, where a field is an expression C = A * B.
Field A and B are changing every time the report is opened, by settig
the
value of the ControlSource with a macro - so the are not constant.

So my problem is the following: I can list the field C int the report,
but
cannot generate a sum of it at the bottom of the reprot. (The
expression
Sum([C]) does not seem to work).

Please, help me.

Thank you in advance.

Tony

How can

 

[Guest]

Hello Douane,

This is a database, where i make offers for our customers.
In fact, the report has the following colums:

Sort of material
Name or item
Price of item (A)
Quantity (B)
Price (C)

The price if item is depending on the sort of material, it is set on the
form. By opening the report (printed offer) I start a macro, which sets the
control source of (A) according to the specified field in the form.

All the question I started, because I'd like to generate the price together
for all the offered items.

I hope, this is enough for giving better advice.

Thanks


Tony

 

[Duane Hookom]

Too vague regarding the logic/business rules/specifications of determining
control source of A. If you can't tell us how A is determined, we can't tell
you how to create an expression that sum a calculation based on A.

 

[Guest]

So, the calculating method is the following:

1. There's a big table (price list) listing all our items in the first
column and listing all sorts of material in the first row. Under it we have
all our prices for every item and every material (like a matrix).

2. When I open the form "offers", I have a main form on the top including
offer nr., name of customer a kombined list of the materials, and an
auxiliarry form in the bottom (I don't know, wheter it't the right
expression). There I can set a list of items choosing from the price list and
the quantities. On the screen I can see the prices immediately for every item
in every material separately and also altogether.

3. What I want to do is printing an offer, that includes all the listed
items, but only in the material choosen by the kombined list on the form.

The shape of the database is:

Tables: price list; offers; offer contents (binding to the offes by offer
number on each record)

Inquiry: Having the 3 tables above plus calculating fields (A * B) for every
material: Price1=PriceA1 * B; Price2=PriceA2 * B ... etc.

Report: Offer: on the top with offer nr., sort of material
in the stem (middle): list of items, single price
(A), Quantity (B)
and Price (A * B) or Price1 from the inquiry (both
are working)
at the bottom should be a sum of the prices, but
neither Sum
[(A*B)] or Sum[Price1] are working.

I hope again giving sufficient information for your help.

Thanks again for working with my problem so much.

Tony



„Duane Hookom†ezt írta:

 

[Duane Hookom]

I believe your issue is unnormalized tables. I would replace the matrix with
a normalized table structure where each price would create its own record
like:

Spoon Silver $15
Spoon Pewter $10
Spoon Steel $1
Spoon Plastic $0.05

To be honest, I wouldn't spend much time on this without normalizing (but
that's my issue).

--
Duane Hookom
MS Access MVP
--

So, the calculating method is the following:

1. There's a big table (price list) listing all our items in the first
column and listing all sorts of material in the first row. Under it we
have
all our prices for every item and every material (like a matrix).

2. When I open the form "offers", I have a main form on the top including
offer nr., name of customer a kombined list of the materials, and an
auxiliarry form in the bottom (I don't know, wheter it't the right
expression). There I can set a list of items choosing from the price list
and
the quantities. On the screen I can see the prices immediately for every
item
in every material separately and also altogether.

3. What I want to do is printing an offer, that includes all the listed
items, but only in the material choosen by the kombined list on the form.

The shape of the database is:

Tables: price list; offers; offer contents (binding to the offes by offer
number on each record)

Inquiry: Having the 3 tables above plus calculating fields (A * B) for
every
material: Price1=PriceA1 * B; Price2=PriceA2 * B ... etc.

Report: Offer: on the top with offer nr., sort of material
in the stem (middle): list of items, single price
(A), Quantity (B)
and Price (A * B) or Price1 from the inquiry (both
are working)
at the bottom should be a sum of the prices, but
neither Sum
[(A*B)] or Sum[Price1] are working.

I hope again giving sufficient information for your help.

Thanks again for working with my problem so much.

Tony



"Duane Hookom" ezt írta:

Too vague regarding the logic/business rules/specifications of
determining
control source of A. If you can't tell us how A is determined, we can't
tell
you how to create an expression that sum a calculation based on A.