Calculated Fields in Access 1

[suzieQuz]

I need to set up three different calculations. Is it possible to do it in one Query?

First I need to add two fields to get a quote total,
then I need to add two different fields to get a Cost Total
Lastly, I need to be able to give a %profit, by subtracting the Cost Total from the Quote, and then dividing that answer by the Quote.

Pretty confusing, I have gotten the first Quote total, however, I am not at all sure how to do the other two.
I would appreciate any help on this one.

 

[willir]

suzieQuz

Actually, this is not hard.

In design vew, create three unbound text fields on your form.

Make sure the property window is open. (Not sure how? Right click anywhere on the form and select the properties option)

Select your first text field, and then select the data tab within the property window. Select the "Control Source" field. In it place your formula. For example, for the quote...
=[PriceForSale]+[ShippingCharge]

Where PriceForSale and Shipping Charge are used to generate the customer quote. The open / close square brackets [] are used to indentify the field variable name.

Important: Include "=" sign in the formula as is used in Excel.

One other thing. The system will use a "text"plus to identify numeric number to identify the field on the form. Not very friendly. On the property window, navigate to the "Oher" tab. Change the name to something more meanful such as txtQuote.

Repeat the process for the second text field. An example for the formual would be...
=[ManufacturingCost]+[OurShippingCost]

Change the field name to txtCost.

For the third field, assumming you renamed the fields as I instructed,
=txtQuote-txtCost

Richard

 

[suzieQuz]

What I have is a database that is set up for tracking jobs, each record lists the job#, description, materials, labor, and quote, (this is the break down for the customer quote)

then it also lists in the same record the company material costs, and company labor costs, and the total cost, followed by the profit.

Within each record I need a calculated field that adds the materials and labor to arrive at the Quote
material costs + labor costs to arrive at the Total Cost
then I need subtract the Total Cost from the Quote, and divide that amount by the Quote to arrive at the Profit Margin

I need to be able to display all of this in a Report


When you say to put an unbound text in a form, I am unable to create a report from a form, I also got an error when I did put it in the form design view and then went into form view

Perhaps I was not clear, or perhaps I'm not understanding your response

Thanks for any insight you have

 

[willir]

Hmmm
Well this forum is called
Microsoft: Access Forms Forum

There is another one called
Microsoft: Access Reports at

http://www.tek-tips.com/threadminder.cfm?spid=703

I understand you need a problem resolved, but I had assumed you were working on a form issue based on where you posted your problem. That being said, lets move on...

The idea of creating a report and form are much the same, except the with a form, the end user can interact, and the developer has a bit more control.

Create your report using the report wizard. Hint, if your report and form use more than one table, you can create a query using query builder and base your report or form on the query instead of using a table.

After completing the rough report, open it up in design mode. You will need to add your unbound text fields using the field names to the detail section of the report.

Based on your information, I do not now the specific names of your fields, or your table design. So I will have to make some assumptions.
- Field names on the report are referenced with the open and close square brackets []
- Field names are: [MaterialCharge], [LaborCharge], [MaterialCost] and [LaborCost]
- In the detail section, you already know how to print these fields on your report.

I am going to call your three calculated fields
[TotalCharge]
[TotalCost]
[ProfitMargin]

When you place a new text field on the report, Access will automatically name the field [Textnnn] where nnn is a sequential number. Example: Text116. You can change the name of the text object by using the property window. To open the property window, right click anywhere on the report and select the "properties". Then navigate to the "Other" tab and change the default [Textnnnn] name to a more meaningful name. This extra step does not have to be done, but it sure adds "readability" to your code when you or some one else edits your report or form later on.

Add three text boxes to the detail section of the report. Select the first text box, and then focus on the property window.

Navigate back to the data tab on the property window.

For the "Control Source", for the [TotalCharge] enter...
= [MaterialCharge]+[LaborCharge]
The formula names used for the control source MUST match the actual field names.

Now select the second text box and focus on the property window.
For [TotalCost], the "Control Source" should be
= [MaterialCost]+[LaborCost]

Now select the third text box and focus on the property window.
For [ProfitMargin], the "Control Source" should be
= ([TotalCharge] - [TotalCost])/[TotalCost]

Access, when it "prints" your report, actually processes it twice (or more). On the first pass, adjust for formatting, "level breaks", cululative values, etc, and it will calcuate the values for your new text fields that you just created.

Later, on the subsequent pass, it prints the actual values. Based on what you have provided, and my assumptions, it should work.

Richard