Access :: Calculated Field

Basing A Calculated Field On Another Calculated Field

Apr 12, 2006

My query contains two calculated fields [TaxSavings1] and [TaxSavings2], which are based on some currency and number-type fields in one of my underlying tables.

I just created another field in my query which looks like: [TaxSavings1]+[TaxSavings2]. Instead of adding the two fields, it actually lumps the two numbers together. For example, if [TaxSavings1] =135 and [TaxSavings2]=30.25, it will give me: 13530.25. I need it just to simply add, i.e. answer of 165.25.

Does anyone know how to correct this? Thanks in advance.

:confused:

View 1 Replies View Related

Tables :: How To Make Calculated Field Based On Field In Another Table

Jun 18, 2014

Table1 contain Two fields (3Months) and (6Months)

Table2 contain Two fields (3Months) and (6Months)

the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder

View 1 Replies View Related

Queries :: Calculated Field To Display Value Of One Field Based On Another

Jul 23, 2015

I have a query with a Date field for EndDate (the dates for end-of-week, Fridays in our case) and another field for Sales (number of sales, not dollars).I want to add 4 calculated fields that represent weeks and have the Sales appear in the correct column (field) for that date.So I will have columns for 10 July 15, 17 July 15, 24 July 15 and 31 July 15 and I want the Sales for each record to land in the correct date column, based on the EndDate column. (The 4 fields is just for the sake of the example, I will actually be having dozens of these calculated date fields).I tried to do it by setting up the 4 calculated fields like:

10Jul15: Sales
and then adding Criteria like:
EndDate = #10/07/2015#
It doesnt work.

View 3 Replies View Related

Tables :: Calculated Field Error - Cannot Define Field More Than Once

Jun 10, 2015

I use access 2010.

I am assuming by the error code, one can not use a calculated field to calculate another field.

View 1 Replies View Related

Calculated Field

Apr 20, 2006

hi. i wanted to kno how to have a field in a table that is calculated from other fields if this is possible. for example i have a order table which has a quantity field and a price field and i want to add a total field that will alutomatically calculate the total (quantity*price) and add it to the field. i know how to do this in a query but don know how to get that field from the query into the table. thanks for any help

View 2 Replies View Related

Calculated Field

Aug 11, 2005

I am new to Access so please be gentle.

I heve started a new table and one of the fields is Date of Birth.

I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.

Any ideas on how to make this work? does it have to be a querie?

Mant Thanks

View 3 Replies View Related

Calculated Field

Aug 11, 2005

I am new to Access so please be gentle.

I heve started a new table and one of the fields is Date of Birth.

I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.

Any ideas on how to make this work? does it have to be a querie?

Many Thanks

View 1 Replies View Related

Calculated Field

Aug 11, 2005

I am new to Access so please be gentle.

I heve started a new table and one of the fields is Date of Birth.

I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.

Any ideas on how to make this work? does it have to be a querie?

Many Thanks

View 12 Replies View Related

Calculated Field

Apr 2, 2006

I have a table with customers, one with products and one transaction table. How can i produce a report, based on a query that will add up the total any one particular customer has spent to date from the transaction table?

View 1 Replies View Related

Calculated Field

Apr 20, 2006

hi. i wanted to kno how to have a field in a table that is calculated from other fields if this is possible. for example i have a order table which has a quantity field and a price field and i want to add a total field that will alutomatically calculate the total (quantity*price) and add it to the field. i know how to do this in a query but don know how to get that field from the query into the table. thanks for any help

View 1 Replies View Related

Calculated Field

Oct 26, 2006

i have opened a field (which was created in a table) in a form as a calculated fieled
the calculations are ok but only in the for
the same field in the table remainded empty

how can it be set so the table's field will get the clcaulation ?

** here is a sample file

View 2 Replies View Related

Calculated Field

Oct 15, 2007

I have this query which selects two fields and calculates it in another field.
But the calculated field has more than 1 expression.Eg
If field A < Field B,Field B + Field C AS X,If field A < Field C,Field A + Field C AS Y.The problem is that when I show this on the Form I want only 1 of the fields to be shown ie either X or Y .if any of the expressions is satisfied then only that field should be shown as a textbox control on the form and not both.How do I do this?? Its really complicated for me..Pls help..

View 14 Replies View Related

A Little Help With A Calculated Field

Dec 5, 2006

Hello,
Can someone please help with this expression?

I have a table with text field Cust_ID and a
Number field MCounter.

I would like to put in an expression as source in a form
Text field based on the same table to AGGREGATE
all MCounter values for a Given Cust_ID

Something like
Sum(MCounter) where "Cust_ID"=[Cust_ID]

Is this a correct expression?

Many thanks for any help.

View 3 Replies View Related

Calculated Field

Feb 22, 2007

This seems a daft question but I have searched the forums and can't find an answer so I hope someone can help me.

I have a table: Register(RegID, Pupil, Activity, Duration, Preparation)

I have a query that I want to calculate the total hours the pupil has spent on activities.

The Duration and Preparation fileds are held as number of minutes so for a 1 hour event the number 60 is held.

I have a query with 4 fields:
Pupil
SumofDuration
SumofPreparation
TotalHours = ([SumofDuration]+[SumofPreparation])60

The problem I have is that TotalHours is calculating as an whole number.
e.g. I have a Record
Pupil = 701
SumofDuration = 270
SumofPreparation = 0
TotalHours = 4

But I want TotalHours = 4.5

I have set the Duration and Preparation field sizes to Decimal and Format to General Number.

I don't know what else to try.

View 4 Replies View Related

IIf Calculated Field

Mar 10, 2005

Can anyone tell me why this won't work? It calculates the 2nd argument (reduction_man) but not the first (reduction)

I have tried both of these and still can't get it to work.

=IIf([reduction])>0,([reduction]*[2004volume])/(13-[cutinmonthnmbr]),([reduction_man]*[2004volume])/(13-[cutinmonthnmbr]))

=IIf(IsNull([reduction])=False,([reduction]*[2004volume])/(13-[cutinmonthnmbr]),([reduction_man]*[2004volume])/(13-[cutinmonthnmbr]))

Thanks!

View 1 Replies View Related

Calculated Field

May 24, 2006

Hello Guys,

I am trying to do the following in a form.
1) (Travel hours + work hours) * dollarperhr to get the "Total" bill to a customer.

2) The results will be put in a field "TotalDollars" that is a part of a table.

Please help.

I tried to put an event (on change) on the "dollarperhr" box so that after entering the values in "travel hours" + "Work hours" * "dollarperhr", the calculation will be done to populate "TotalDollars", but it didn't work.

please help

View 7 Replies View Related

Calculated Field

Dec 21, 2004

Hi here is my problem

I have a table named= Commun tasks
which has 3 fields= [Commun tasks],[Quantity] and [Unit Cost]

I created from that table a form who shows these 3 fields. I also created in that form a calculated field (named Total) who shows the Total. I simply wrote
in the control source =[Unit Cost]*[Quantity]

I have another table (Project Information) who has 3 fields=[Prior total],[New Total],[cost addition]

What I need to do is each time the [Quantity] or the [Unit Cost] are changed, I need to show the [New total]. But also i need to show the [Prior total] which is based on the [Quantity] and [Unit Cost] before the change. So i think i need to store the calculated field (Total) in a table, but I don't really know how to do that (I,m a newbie).

Ok let's simplify my question

How can I store in a table a value from a Calculated field? I know that we should not store calculated value but in my case I need to store them.

Thanks for help

View 4 Replies View Related

How To Add Calculated Field

Jul 30, 2014

How to Add a calculated field named CustomerName as the first column that is either the company or the concatenation of the last name, a comma a space, and the first name. Sort in ascending order by Customer Name ?

I've came up with CustomerName: IIf([CompanyName] , [LastName] & ", " & [FirstName])

View 8 Replies View Related

How To Use Calculated Field In Next Field's Source

Nov 5, 2005

I am using a function to calculate the number of days in a quarter. I have the Control Source for field "A" set to
=CalcQtr1Days([AdminDate]). I am calling CalcQtr1Days and passing the AdminDate. It calculates the number of days I'm looking for and returns that number in my field. Then, I want to use that number, along with DaysAbsent, to calculate DaysPresent. So, in field "B" Control Source, I have =CalcQtr1Days - DaysAbsent.
This gives me #Name? in field "B". I've also tried =DaysPresent = CalcQtr1Days - DaysAbsent but doesn't work either. What is the syntax for using the value calculated in field "A" in my calculation for field "B"?

Thanks,
Rod

View 1 Replies View Related

Calculated Field From A Combo Box

Mar 9, 2006

I'm trying to get some equations to work for a database involving ski lifts.If I can get it to work right it should give me the resulting tension on the lift.
I have a two combo boxes that contains two values, counterweight and Hydralic, the other one has the values extends cylinder and retracts cylinder.

if counterweight is selected it should calulate the value with the equation
([L/bore]*[w/Rod]*[D/quan]*145+[additionalweight])/[counterweightparts]

If Hydralic and extends cylinder is selected it should calculate it with
(3.14*[L/bore]^2)/(4*[D/quan]*[additionalweight])

If Hydralic and retracts cylinder is selected it should calculate it with
(3.14*([L/bore]^2-[W/Rod]^2))/(4*[D/quan]*[additionalweight])

I'm trying to get this to work using IIF statements in a query but I can't seem to get it right.

I hope I was clear enough in my explanation. If any body know how to get this to work or nows a better way of going about it please help, Thanks.

View 7 Replies View Related

How Do I Add Calculated Field To A Query

Nov 23, 2007

I have an inventory transaction file that has quantity on hand as a calculated field. I am trying to add this unbound field to a report using a query but I can't figure out how to add my quantity on hand field to the query since it is a calculated field and not a bound control.

Any help would be appreciated.

Thanks

darrrellx

View 1 Replies View Related

Calculated Field In A Table?

Nov 12, 2004

Hi folks,
Is there a way to have a calculated field within a table? I have a table that I've added four fields to: AmountIT, AmountRE, AmountKK, AmountRes. I want to be able to sum these four fields to give AmountTotal, but is there a way to have in a table?

View 8 Replies View Related

Summing A Calculated Field

Jan 17, 2005

Hi,

I want to sum a calculated field on one of my forms. The field is calculated by multiplying the cost of a good by its quantity. So far I have been unable to sum it. Does anyone know how I can do this?

Here is a link to what the field is related to just so you people know what I am talking about: Project (http://www.access-programmers.co.uk/forums/showthread.php?t=78815&page=1) ...basically I have fixed all the problems mention in that thread. All that remains is this field. I will attach my project in case that helps. (the calculated field is in the Treatment Form) Thanks in advance :)

View 14 Replies View Related

Help Please Calculated Field In Table Or Other?

May 23, 2005

Im not sure the best way to go about this and would be most grateful if I could be pointed in the right direction.

If I had a table called tblEmployee and a field within it called "PayRate" and another table called tblHours with a field in it called "LabourCost" and a field called "Hours". What is the best way to hold the "LabourCost" value so it doesnt get affected if someones "Payrate" is changed.

I expect I have asked this all incorrectly and I really do apologise.

Many thanks in anticipation... :)

View 4 Replies View Related

When To Save A Calculated Field

Jan 12, 2006

Hello everyone,
I have been trying to calculate the number of working days a person has off sick between two dates.I was aware of how to calculate the difference between two dates using datediff, this was not quite hat i needed.
after much experimentation and a little searching I came across this site that has I would recommend to other beginners to use as a query and a module.

http://www.mvps.org/access/datetime/date0006.htm

I have a calculated field that uses this function to work out the number of days, My question is, Is it worth me saving this calculated field physically in my table instead of in the query?

The reason for this is that im only with company for limited time and others will be building reports in the future and would prob prefer to build reports on tables rather than queries. If so, how can I go about setting the field value to that of the query result?

View 4 Replies View Related