Calculation In A Table

Feb 14, 2008

Hi all - I have a table that is keeping track of projects we are working on. There is a field with the overall cost of the project and a field with the percent paid. I would like a field that calculates that amount still owed. Is this possible?

Thanks!
Amelia

View Replies


ADVERTISEMENT

Keep Result Of Calculation In Table

Oct 18, 2004

Hi all.
I have a TableA with 3 fields: FieldA, FieldB, Result. A Form1 based on these fields. User will put data in FieldA, FieldB and calculate in Result. Problem is how to keep result of calculation in TableA?
Thanks

View 4 Replies View Related

Looking Up Calculation Expressions In Table

Feb 22, 2006

First off, I am not sure if this belongs in the queries forum but it seems most of the question is geared towards a table so I have posted it here.

At the moment I have a query that contains several fields that use nested IIf statements to determine which set of rules to apply. I was wondering if it is possible to somehow set the IIf variables up in a table and then for just to lookup the table in the query.

For example, one of my simpler IIf's looks like this...

Cost:
IIf([AType]='Skill',
IIf([StartDate]<#01/08/2004#,
IIf([AOP]>0,
([BasicRate]-[Frame])/[AOP]/100*[Proportion]),
([BasicRate]-[Frame])/[OP]/100*[Proportion]),
([BasicRate]-[Frame])/[OP]/100*[Proportion])


So I was thinking could I setup a table to show:


FieldName AType StartDate AOP Calculation
Cost Skill <#01/08/2004# >0 ([BasicRate]-[Frame])/[AOP]/100*[Proportion])
Cost <>Skill Is Not Null Null ([BasicRate]-[Frame])/[OP]/100*[Proportion])


and then somehow perform the calculation rather than just look it up.

The main reason is it would be so much easier to see what is being calculated rather than try and weed my way through nested IIf statements.

Can anyone shed any light on this or maybe even a better way to accomplish what I am after?

View 1 Replies View Related

Table/form Calculation

Sep 30, 2007

Is it posible to perform calculations within a table or data entry form. I have a date of birth field and an age field. I have an Update Action Query that automatically works out the date, (See below). However if I design the form with a close form event that triggers the query I get the confirmation message. I can only supress all Action queries confirmation messages or none at all and I do not want to disable this. Therefore I would like the age to be calculated within the table, (or form if that was possible). However I do not know how to do it.

DateDiff("yyyy",[DateofBirth],Now())+Int(Format(Now(),"mmdd")<Format([DateofBirth],"mmdd"))

View 2 Replies View Related

Automatic Calculation In Table

Jun 27, 2006

I am new to Access. Is there a way to do a automatic calculation in a table field?

eg fieldA divided by fieldB and have the result show up in fieldC

Thanks

View 7 Replies View Related

Use Calculation To Populate Table

Jan 2, 2008

Is there a way to take a value entered in a table to calculate a new value to populate a second field in the table? For example, if the user enters that the company has received $100 from a client, I need to show that $100 in the income field of the table, but then also calculate the commission based on a formula, and save that new commission value as well.

I was doing this in a query, but then the value doesn't get stored...

Thanks...

View 8 Replies View Related

Forms :: One To Many Table Calculation

Apr 30, 2015

I'm currently using a Dlookup function to display a calculated field from a query. This works fine when only one record is entered on the many side; however when additional records are entered it doesn't add the additional records. What function/code can I use to add each record's total as it's entered on the many side?

This is what is working when the many side only has one record:

=(DLookUp("[qryTotalBalance]![NetBalance]","[qryTotalBalance]","[CustID]= " & Nz([CustID],0)))

View 14 Replies View Related

Calculation Using Table Data

Apr 3, 2013

I am trying to build a Fitness tracking database, where i am trying to input a calculated item. there are two inputs and i want an out put, i put in Height and Weight, and then i have a TBL that has Height then the maximum weight that applies to that height...

so TXT BOX 1 = HEIGHT
TXT BOX 2 = current Weight
TXT box 3 = MAX Weight

my tbl looks like this

58" 131lbs
59" 136lbs
60" 141lbs
61" 145lbs

What im trying to do is have the form read automaticaly what i put in TXT BOX 1 and enter in automatically the MAX weight for that height.

View 14 Replies View Related

Making Calculation From Query Appear In Table ?

Feb 16, 2005

hi - i have tried search for the solution to my query - but to no avail, so here goes:

I have a query which is based on more than one table. In the query, I have specified a calculation, eg. Final Price: SUM([Sale Price] - [Discount])
From this query I have a form, just showing everything. Details are put into the form, and viola, they appear in the query if checked. However, they do not appear in the table
My assumption why this is not happening was because the Final Price is no longer "record source"d from the original table. How can I combat this so that it does appear in the table?
Thank you (sorry if it is easy - but i dont have a clue!)

View 6 Replies View Related

How To Query Unrelated Table To Do A Calculation?

Apr 30, 2006

Hi all, I have a problem in returning values from 2 different tables because they are not related. Let me explain:

I'm trying to do "Payment Due" query by substracting the amount in the "Cost" table with the amount in the "Payment" table ([CostAmount]-[PaymentAmount]). However, since no payment has been made, the table contains no related record.

At first I thought the problem lies in null values the table return hence I tried to use NZ function to convert null to zeros. Then I realised that no values has been returned from the table due to no related record available.

Can anyone help me?

Cheers

View 6 Replies View Related

Doing Calculation And Inserting That Value Into New Row Into Query Table

Apr 13, 2008

Hi, I am wondering If I can Sum the value of rows in one field of my Query Table and Inserting that Calculated value into the bottom of the summed Row in the VBA or through Query Design View.

Please let me know, Thank YOU!

Below I have attached the picture of the data that Im trying to sum and insert into a new row that Hopefully can be created through MS access Query.

View 1 Replies View Related

Calculation For Time X Hours In Table

Nov 19, 2011

I need to calculate in the tbleJobs table, the amount of the jobs total amount and place the result in the JobsTotalAmount field.

This has to be a manual calculation. Not an on event calculation.

Data is coming from two table:

tblLimos has the hourly rate.

tblJobs has the start time, the finish time and the total amount field.

View 6 Replies View Related

Calculation With Lookup Field In Another Table

Dec 3, 2014

My Sales! table has fields [ItemType] and [Price] and VAT! table has [ItemType] and [VATRate].

Both tables has a common field which is [ItemType].

I am trying to make a query which will calculate the VAT figure by multiplying the Sales table [Price] with the corresponding [VATRate] in the VAT table by matching the [ItemType] in both tables.

I tried DLookup but couldn’t find a solution. How do I accomplish this?

View 1 Replies View Related

Save Result Of A Calculation Or Function Into A Table

Apr 1, 2006

Hi there,

I've read through the forums on saving the results of a calculation into a field is a bad idea. I somewhat understand the reasoning for it. But I don't think such situations arise for everyone.

But I have gone with the suggestion and created an updatequery, which simply goes through and updates the calculated value into a table. Now each time the updatequery is run its asking for permission as to the fact if I'm sure I want it to be run. How do I turn this option off. And should I call this updatequery afterupdate or beforeupdate?

Thanks

View 3 Replies View Related

Convert Expression Calculation Into Data Table?

Jan 1, 2006

Hello all, I'm quite new to Access. I've read many Access tutorials and site but I have yet to found the solution for my problem. So here it is (pardon my english):

Field AmountTotal is
=(Nz([AmountSubTotal])+Nz([AmountSH])-Nz([Discounts]))

I manually input AmountSubTotal, AmountSH and Discounts. I expected an automatic calculation for AmountTotal.

In "Form view" I get the result of AmountTotal that I wanted. But the calculation result doesn't get recorded in my field data table AmountTotal. It just shows blank.

How do I record this automated results into my original table?

Thanks in advance!

View 4 Replies View Related

Write Result Of Calculation Back To Table

Jul 1, 2004

I am sure that in earlier versions it was easy to write the result of a calculated field on a form, back to a table, but I can't find the method in the help file for the current version of Access.

Would appreciate any help.

View 3 Replies View Related

Tables :: Date Of Birth Calculation In Table

Dec 15, 2012

I have a [DOB] field with birth dates. I have another field [DOBExp] where I want to automatically calculate at date 15 days out, but exclude weekends and holidays. I have a separate table listing the holidays.

Would like a formula to use in my first table for this. Have no clue how to do this.

View 2 Replies View Related

Tables :: Using Default Value For Calculation And Recording To Table

Nov 19, 2013

I shouldn't normally record calculated figures into my table. I'm willing to buck proper procedure here for ease of use for referencing in reports; also, it's just one data point that will be calculated only once and not changed.Anyhow, I have an IIF statement that calculates a total price based on a table of costs. I set this as the default value and it calculated properly - but once I set my text box to have a control source, it no longer calculates my cost.

I was hoping to set it so it calculated the cost and then write that total cost to my table.should mention that my calculated control is in a subform.

View 2 Replies View Related

General :: Calculation Hours For Schedule Table

May 23, 2013

I wana ask about calculation hours for schedule table. lets say how to calculate based on what i select from my schedule table for :

no class: how many hrs

lesson i got class: how many hours.. for example: 1+0.5= 1.5 hrs

total of hours: which is based on "no class" + " lesson i got class" for example: 1+1+0.5= 2.5 hrs

View 2 Replies View Related

Tables :: Saving ONE TIME Calculation To Table

Nov 12, 2012

Access 2003. I am trying to save a calculated field (Item No) from a form to the accosiated field within the Products table

I have a hidden text box (itemNo) which is bound to the relevant field and a second text box with the following formula

="TV-" & Left(CatShort.column(1),3) & "-" & Format([ProductID],"0000") as its source

What I am trying to do is to provide an item number for each item of stock based on the Category short name (Catshort) and the incremented item ID Number, with a TV prefix.

I am aware that its a bad itea to place calculated field into the table if the calculation is likely to change, however once my calculation is stored it has no need to change.

I have tried the beforeUpdate method within the ItemNo textbox

Private Sub ItemNo_BeforeUpdate(Cancel As Integer)
Me!ItemNo = Me!ItemCalc
End Sub

but nothing happens (in that the table is not updated)

Any way of putting together an incremental item number based on the selected category with a "TV" prefix....

View 6 Replies View Related

Table With Four Fields - Calculation Of Total Time

Feb 21, 2013

I have created a table with four fields. The names are:1stBusOn, 1stBusOff, 2ndBusOn, 2ndBusOff.

I want the time for the first two fields and the time for the second two fields to end up in a field called TotalRideTime.

Also, if only one set of the fields are completed I still want that total time to in up in the the TotalRideTime field.

View 1 Replies View Related

Tax Calculation - Save Total Amount In Table

Mar 16, 2013

We have a form where we enter various amounts and a box where these are added together to calculate taxes. This amount is shown on the screen but we need to save this total amount in the table where it can be used in other calculations in other forms

The control in the box looks like this:

=[registration fee]+[membership fee]+[fee per term]+[additional classes fee]+[costume]+[books]-[discount]+[applicable gst]+[uniform fee]

How can I get the result of this formula saved in another table field?

View 4 Replies View Related

Pivot Table Calculation Returns Zeros

Jun 16, 2015

I am trying to create a pivot table to show total absences. Each absence is an individual occurrence with a with a count of 1 in the attendance column. All I need for the table is the ID and the Attendance; ID is my row and attendance is what I need to be summarized but I just keep getting a zero in each place where the Attendance is a 1. This works well in Excel. What is different here?

View 2 Replies View Related

Major Challenge - Update A Table Based On Calculation

Nov 14, 2004

I am so interested to see a solution to this little problem…i think ithis is one of my more complicated questions!!!!

Problem:

Where I work we have a series of machines… named “ C1, H7, H8 etc”

Each line has 3 counters on it,

Impressions – records the number of cartons used on the line
Standard Pack – records the number of filled cartons with product
Output – records the number of cartons that complete the whole line process and are ready to be shipped to the customer.

Sounds straight forward enough….but the process gets a little more complicated.

Some lines are referred to as joint lines… this indicates that the product from each line the output figure is joined together and totalled. So it is not possible to calculate how many each line completed for the output figure. Note… it is only the Output figure that is joint, each line still has it’s own impressions and standard park counters

Grouped lines are given a group code – P8 in this case.

Example…

C1 not joined

H7 and H8 joined.

C1 –
Impressions –1000 Cartons
Standard Pack – 900 Cartons
Output – 800 Cartons

H7 - P8
Impressions –1600 Cartons
Standard Pack – 1500 Cartons

H8 – P8
Impressions –400 Cartons
Standard Pack – 300 Cartons

P8
Output – 1600 Cartons

The output counter is brought in to the database automatically via a linked spreadsheet and a macro, this data is then stored in a table called “DaycodeMachineShiftImpressions” you will see in this table that the grouping is referred to as Machine.

As previously stated we have no real way of working out the amount each line has output individually.. the way it is calculated at the moment, is to take the figure for P8 and split it into 2 equal figures and assign that to each line.

So in this case the figure assigned to each line would be 800, although you will see that this is not possible for H8 line as it only put 400 cartons on the line in the first place.

I now want to implement the following calculation to my DB,

(see Attached Spreadsheet)

the calculations a re stored in cells b12 & b13 in the spreadsheet.

so that the output figure is separated

in proportion to impressions made.

I then need the figure per line to be fed back to the “tblProduction” output field..

in the tblproduction the data for P8 is stored to line level so in this case as H7 and H8, hence why i want to use the calculation to work out the output figure per line...

Can this be done automatically, if so please help me…

Andy

View 2 Replies View Related

Storing A Calculation As A String In A Table And Executing It In A Query.

Jun 11, 2007

N.B. This is not your usual 'Bad practice to Store calculated values in a table' post

I have a table of items.
Each item has a different formula attached which I need to store as a literal value in the table and execute it as a normal expression in a query or vb.

e.g

Columns:Item, QTY, Hrs, mins, ItemFormula
Data: Car, 2, 7, 3, [QTY]+ [Hrs]/[mins]

So ' [QTY]+ [Hrs]/[mins]' would literally be stored for that rercod and is how it would be displayed in a table, form, report etc as it is important for the user to see how the calculation is performed as well as seeing the end result which will be calculated as normal in vb or a query.
The formula will vary from record to record.

How do I go about this?

TIA

View 6 Replies View Related

Major Challenge - Update A Table Based On Calculation

Nov 14, 2004

I am so interested to see a solution to this little problem…i think ithis is one of my more complicated questions!!!!

Problem:

Where I work we have a series of machines… named “ C1, H7, H8 etc”

Each line has 3 counters on it,

Impressions – records the number of cartons used on the line
Standard Pack – records the number of filled cartons with product
Output – records the number of cartons that complete the whole line process and are ready to be shipped to the customer.

Sounds straight forward enough….but the process gets a little more complicated.

Some lines are referred to as joint lines… this indicates that the product from each line the output figure is joined together and totalled. So it is not possible to calculate how many each line completed for the output figure. Note… it is only the Output figure that is joint, each line still has it’s own impressions and standard park counters

Grouped lines are given a group code – P8 in this case.

Example…

C1 not joined

H7 and H8 joined.

C1 –
Impressions –1000 Cartons
Standard Pack – 900 Cartons
Output – 800 Cartons

H7 - P8
Impressions –1600 Cartons
Standard Pack – 1500 Cartons

H8 – P8
Impressions –400 Cartons
Standard Pack – 300 Cartons

P8
Output – 1600 Cartons

The output counter is brought in to the database automatically via a linked spreadsheet and a macro, this data is then stored in a table called “DaycodeMachineShiftImpressions” you will see in this table that the grouping is referred to as Machine.

As previously stated we have no real way of working out the amount each line has output individually.. the way it is calculated at the moment, is to take the figure for P8 and split it into 2 equal figures and assign that to each line.

So in this case the figure assigned to each line would be 800, although you will see that this is not possible for H8 line as it only put 400 cartons on the line in the first place.

I now want to implement the following calculation to my DB,

(see Attached Spreadsheet)

the calculations a re stored in cells b12 & b13 in the spreadsheet.

so that the output figure is separated

in proportion to impressions made.

I then need the figure per line to be fed back to the “tblProduction” output field..

in the tblproduction the data for P8 is stored to line level so in this case as H7 and H8, hence why i want to use the calculation to work out the output figure per line...

Can this be done automatically, if so please help me…

Andy

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved