Access :: Calculated Value

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

Help With Calculated Data

May 9, 2005

I know it is said that you should not store calculated data in tables so I need some input on how to go about a problem I am having.

I am creating a DBase with some aircraft info in it. The DBase will be around for awhile so I plan to implement so sort of archiving later on.

The problem is one piece of info I am tracking is flight hours for the aircraft along with hours on certain components. I have a table with info pertaining to each aircraft by number (1 record per aircraft) one of the fields is total current flying hours. I have another table that has individual flight info including number of hours per flight. I want to add the individual flight hours with the total flight hours to get a new total flight hours. Easy with a query but if I get rid of old flights then how will you add them in the query. The only thing I can think of now is to save the newly summed number in the table which is supposedly taboo. Same with the components if it is on the aircraft during the flight it needs to get the new flight hours on it also .

Sorry if this is trivial but any ideas on how this should be done would be great.

Thanks in advance.
Frank

View 7 Replies View Related

Calculated Field

Feb 9, 2006

Hi,
I have a calculated field in a query that concatinates an order number prefix and the part number to give the order number:
OrderNum: [OrderPrefix] & [PartNum]

There are 2 tables:
tblPartsList
PartNum
PartDesc

tblPartsListCategories
OrderPrefix
OrderNumCategory

This works fine except for one of the categories. For the third category there are no entries in the tblPartsList - because they are miscellaneous.

How do I change the calculation so that it works as above where there are corresponding Part numbers in tblPartsList and otherwise gives me the order number and any 2 digits e.g. something like [OrderPrefix]*

Having been trying to do this for ages but having trouble with syntax.

View 10 Replies View Related

Calculated Records

Mar 14, 2006

Hi

I have read a number of these threads now where it says you should not generate new records based on calculated fields. So can some one help with my following problem.

I am creating a customer/contract database where when an order is created, a record set is generated to store dates for invoice periods. I have done this by using the start date of the contract and then adding onto this a value of x days that updates a table giving y records based until the end date of the contract.

Is this the best way to do this or can some one tell me a better way.
Thanks

View 4 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 Fields

Sep 13, 2006

Hi,

I have read that it's not recommended to put calculated fields on a table and they should instead be calculated using a query.

How about if I need to store that calculated value in a table (e.g. field1 + field2 = value in field3)

How can I do that please?

Thanks,
B

View 5 Replies View Related

Store A Calculated Value?

Jul 9, 2007

I was once told that Access does not like to store calculated values but I would like to.

Here is my expression: =([Shift Lenght]-[Total idle time]-[Total down time])/([Shift Lenght]-[Total idle time])*(0.33*[Total pc])/([Shift Lenght]-[Total idle time]-[Total down time])*([Total pc]-[Total scrap])/([Total pc]+0.1)*100

This gives me the value that I desire, now I need to store it in the table with the rest of the records data.
I am planing on storing 3 -4 expressions like this and then averaging them.
Then building a query to the averages for the day,week,month, and year.

Please be gentle I am not very familiar with access but learning a lot:)

View 7 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

Calculated Expression

Aug 16, 2005

I have two email addresses in a table one personal and one work and a question asking which to use, I have a query running from the table and have an IIF expression creating a field which looks to see which one to use (that all works fine !) anyway my client has now asked for an extra field in the query which combines the two email addresses, as in aaa@homedomain.com;bbb@workdomain.com so that I can run an email to both addresses at the same time. fraid I just can't figure it out ! I'm sure it's obvious, I can't find a similar question posted either, I've tried the expression builder using AND for the two email addressses but just end up getting -1 in field when I run the query, not the two email addresses separated by a ;

Anyone any ideas

many tks

:)

View 3 Replies View Related

Calculated Value In Where Clause

Sep 30, 2005

In one of my queries, I'd like to reuse a calculated field as
criteria in the Where clause. SQL seems reluctant to want to
do this. You can RESTATE the calculation in the Where clause,
but can't refer to it by Name. You can Order by it, but it
can't be in the Where clause.

If the query result set consists on a set of Named columns, why
can you refer to a calculated column by Name in the "Order By"
clause but not in the Where clause?

Why is this OK?

Select FldA As FirstField,
FldB As SecondField,
FldA - FldB As TheDifference <-- Just a calculated field
From SomeTable

And this is OK?

Select FldA As FirstField,
FldB As SecondField,
FldA - FldB As TheDifference
From SomeTable
Order By TheDifference <-- Order By is OK

And this in not OK?

Select FldA As FirstField,
FldB As SecondField,
FldA - FldB As TheDifference
From SomeTable
Where TheDifference > 100 <-- Can't use TheDifference in Where clause

Don't want to repeat the calculation.

Select FldA As FirstField,
FldB As SecondField,
FldA - FldB As TheDifference
From SomeTable
Where FldA - FldB > 100 <-- I know this works, but ...
Order By TheDifference

Wayne

View 3 Replies View Related

Calculated Date ....Help Please

Aug 13, 2006

Hi Folks

I have a simple table that has a date field.

This is called dateopened and is in the format ddmmyy hh:m

I have a query based on this table.

What I'm trying to do is workout the day that a case was opened from the date entered.

i.e I have an entry that was opened on 10/08/06

My calculated filed works out that this was raised on a Thursday ?

I can then do dcounts etc on how many cases were opened on a Thursday or a Friday etc

I cant really get my head around the syntax in the query fileld

Can anyone advise me how to do this ?

Many thanks

Jimmy

View 3 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

Calculated Query?

Mar 6, 2007

Hi,
I am trying to display all records that are 30 days old from date of registration (& not paid). I have:

30 Days: [SrDate]+30
SrPaid = No

But I don't want to see those that have registered in the (say) last few days, but only those that have not paid and 30 days are up. How would I achieve this?

Thank you

View 2 Replies View Related

Sum Up The Values Calculated.

Jul 8, 2007

hi I am making a late report of each employee's data and I calculated for each individual day by having late for how long, but I would like to make another column which displays the total amount of time late. This is currently what I have now:

http://i8.photobucket.com/albums/a45/janue/num1.jpg

My SQl looks like this:

SELECT username, DateValue(clocktime) AS Date_Clocked, Min(TimeValue(clocktime)) AS Clock_In_Time, ((Clock_In_Time-#9:00:00 AM#)*24) AS Late_By_inHours
FROM tablename
GROUP BY username, DateValue(clocktime)
HAVING Min(TimeValue(clocktime))>#9:00:00#;

clocktime is a date/time field, in reality it is regardless whether it is clock in or clock out time. And now I want my table to look smth like this:

http://i8.photobucket.com/albums/a45/janue/num2.jpg

Has anyone knows how to do smth like this? I tried using the SUM() but it states error stating it cant take the expression ((Clock_In_Time-#9:00:00 AM#)*24) smth lidat

View 2 Replies View Related

Calculated Fields

Mar 26, 2008

I am trying to create a calculated field in a query, that shows money owed from customers. I have 4 different job codes that have their own prices. I also have customers with the amount of hours they owe for, for each job. There are two customers that owe for two different jobs. I know how to write in : Money Owed: [price per hour]*[number of hours]. but i want to have only one line for each customer when i run the query. How do i combine??

View 1 Replies View Related

Calculated Controls

Jan 24, 2005

I have a main form which shows customer personal details including their budget and a subform which shows the costs for each course they book. The sub form has 4 boxes which I use to capture course fee, travel fees,subsistance and other expenses (along with other details such as course code etc). These cost details are stored in the table study_leave_recs in the respective 4 fields. I show on the subform the total spend for each course being booked using a calculated control. On the main form I use four dsum statements added together to show the total spend per customer.
=DSum("[COUR_ACT_FEE]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")+DSum("[COUR_ACT_TRAVEL]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")+DSum("[COUR_ACT_SUBSISTANCE]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")++DSum("[COUR_ACT_OTHER_EXPEN]","study_leave_recs ","BUDGET_HOLDER_ID=[study_leave_recs].[Form]![budget_holder_id]")

It works ok but is there a better way of doing this? Also is there any way I can use the resultant output to calculate / show the remaining budget (the budget is held in the general table "people"? I'm having trouble using the above statement to subtract the calculated spend from the budget box shown in the main form!!

I would appreciate any help, I seem to have spent ages on this on! Thanks in advance. Regards Peter

View 3 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