Auto Calculate Field

Feb 1, 2006

Hi all,

I'm trying to set up a holiday register. In my holiday table i have the following fields HolidayID, PersonID, StartDate, EndDate. I would like to have a final field which auto-calculates the number of days taken between start and end date. I read somewhere this should be done in a query using the DateDiff expression but I can't seem to get this to work.

Any ideas please?

Thanks all!

View Replies


ADVERTISEMENT

Tables :: Auto-Calculate Field From Other Table Values

Aug 10, 2015

How to set up my tables as I'm just starting off with setting my database up.

I'm doing a simple database to track the purchase orders (PO) I am managing. Each PO has a PO Number and an Original Value. POs may have multiple amendments which would change the PO value. I would however like to keep the history of the PO original value and all different amendments.

So I created two tables:

tblPO:
ID
PO Number (Number)
PO Original Value (Currency)
PO Sum of Amendments (???????????)
PO Current Value (Calculated = PO Original - PO Sum of Amendments)

tblPOAmendments:
ID
PO (Lookup from tblPO)
PO Amended Value (Currency)
Amendment Date (Date/Time)
Amendment Desc (Text)

Now the two, million dollar questions are:
1.) Is this the right table structure to use.
2.) How do I go about calculating the Sum of Amendments field?

View 4 Replies View Related

Linking Values To Auto Calculate

Aug 6, 2007

I have parts that go into and out of inventory. Each time a part is removed or returned, the user inputs the number into the computer. I was wondering how I could link the numbers so that as parts were removed or added, the total number in inventory and the total number on the floor automatically changed. Thanks so much.

View 1 Replies View Related

Forms :: Purchase Order Form - Calculate Auto Total Price

May 20, 2014

I need to make an order purchase form like i have customer table and product table, i want to make a form which creates order for a specific customer and I can add as much items as i want and than calculate auto the total price, and an option to make a report for it.

View 5 Replies View Related

Forms :: Auto Age Calculate From Birth Date To Selected Entry Date

May 11, 2013

I create a database through ms access and there have a birth date box and admission date. Another box for Age.I want to see the age in month or year figure in to the age box when I go next field. Which will be calculate from admission date to birth date.

View 1 Replies View Related

Tables :: Calculate Value For A Field Based On Value Of Another Field - Statement

Dec 30, 2012

I'm trying to calculate a value for a field based on the value of another field, [Field1] has a value list of 28 choices I want [Field3] to take that value and multiply it by the value of [Field2]. I'm using an Iif statement and it sort of works. I looks like this:

IIf([Field1]=1 Or 2 Or 3 Or 4 Or 11 Or 12 Or 13 Or 21 Or 22 Or 28,[Field2]*0.06,IIf([Field1]=5 Or 6 Or 14 Or 15 Or 23 Or 24 Or 29,[Field2]*1.1,[Field2]*2.1))

I receive nor errors but it will only return the value of [Field2]*.06 no matter what is selected in [Field1].

I'm sure I'm missing something and there is probably an easier syntax to use, but I'm at a loss at the moment.

View 4 Replies View Related

Query To Calculate Field Depending On Value Of Another Field?

Oct 13, 2005

hey guys . .

I have 3 fields: TEST, NETQTY, EXTENDED

NETQTY usually = EXTENDED depending on the TEST

BUT the Test PTCGCD has a EXTENDED value that is TWICE the value of NETQTY

How do i create a query which will update the EXTENDED field depending on the TEST value>?

For Example:

The general TESTS: AFP, ANAS, CYC etc . . EXTENDED = NETQTY(1)

PTCGCD: EXTENDED = NETQTY(2)

?

View 1 Replies View Related

Calculate Totals In A Field

Dec 6, 2004

I have 4 fields in my table and form (Towing Charges) (Storage Charges) (Other Charges) and (Total Charges). I need to have Towing Charges, Storage Charges & Other Charges to calculate and populate in my Total Charges field. I tried a script in the control source of the field, but its not working. I probably have the script wrong.

Thanks Everbody

View 3 Replies View Related

How To Calculate Field In Query

Jun 5, 2007

Hi Friends,

I have made one form based on query. Its a invoice entry form. I have price, quantity and amount. I want when i enter price and quantity, amount should be calculated. I know its very simple for you. Any suggestion.

thanks

mithani

View 2 Replies View Related

Automatically Calculate Field

Dec 16, 2005

Hi guys,

I have a mainform "frm_CaseReference" and a subform "subfrm_CasesControls".

In the main form I have "DOB" field and in the subform I have "DateSlideTaken" and "AgeAtSmear" fields.

I want to automatically calculate age in the "AgeAtSmear" from the "DOB" and "DateSlideTaken" but having problems.

I have tried the following code but it doesnt work:
=DateDiff("yyyy",Forms!frm_CaseReference!DOB-[DateSlideTaken],Now()

I have read it is not good idea to store age but my work place want this so i have to include it. Can someone please help....

View 2 Replies View Related

Having A Field Calculate Other Fields

Aug 15, 2007

I am trying to get one of my fields to calculate this expression which includes other fields:

If "Financing Type" = 1 or 2 THEN 20% * "Loan Amount" OR
If "Financing Type" = 3 or 4 THEN 100% * "Loan Amount" = 2,000,000.


I thought that this would work, but it only works in queries or reports.

=IIF([FinanceType]<3,[LoanAmount]*.2,[LoanAmount])

I would like this to work in the table.

View 11 Replies View Related

Calculate Field Using Different Field From Different Table?

Jun 2, 2012

i have got this table

Table 1:-- List of available classes

Design view

Class -- Primary key ---text

Subject ----text

Num. Lessons---- Number

Enrolments ---- Number

Max Class Size--- Number

Action -- Calculated because if enrollment is higher then max. class size then its says full otherwise it will tell enrol

Table 2:-- Teacher And Class Income

Class --- text

enrolment --- number

max class size ---- number

income per enrolment ----- currency

incomer per class ----- currency ?

1) but what i wanna do is if i change enrollment and max class size VALUE IN TABLE 1 ... i want that change in table 2 enrollment and max. class size FIELD AS WELL For e.g. if i change enrollment =25 and max class size = 30 ,,, i want that change in table 2 .......so i want that table 2 should display Enrollment = 25, max class size = 30 ,I want this things in TABLES not queries , what should i do... and i compulsory have to use access... NOT EXCEL.

2) then i want that Total Incomer per class (In Table 2) = Enrollment * Income per enrollment so that value should be display on total incomer per class ....So if i change enrollment value ... then the total income per class value has to change ....

3) i want in table that my total income in all class should display in same table ( Table 2)

View 3 Replies View Related

Field To Calculate Time Elapsed

May 15, 2006

I have nearly achieved this from an example on the Microsoft site but am having problems with the subforms. Can anyone take a look and if possible provide me with guidance as to what I am doing wrong. The instructions are included.Any help with this would be very much appreciated. Kind regards. Bernard

View 6 Replies View Related

Calculate Numbers From A Text Field?

Dec 27, 2006

Ok, this one has me stumped.

I have a several fields on my form that are list boxes that must have text as the properties so I can populate the lookup data with things like" Severe rating = 7 points" and "Moderate rating = 3 points". I have another similar field where the choices are "High probability = 9 points" and "Low probability = 2 points" etc. A third field needs to show the total points (product) of the choices from the first two fields, i.e. 9 points x 2 points = 18 points. Then,
a fourth field needs to display where the answer falls in a grid, i.e. 1-14 points = Low, 15-29 points = medium, 30-49 points = high, etc.

How do I apply the calculations on the field choices if the field properties have to be text instead of number?

Thanks,

bugleboy:confused:

View 10 Replies View Related

Calculate Query's Field With Two Result

Sep 28, 2005

Hi All
I try to create a query based on Table1 and fields Date and Result. Is it posible to get a query that calculate two amount of Result field when that field is null and not null?
That two values of Result field I will use to create monthly Pivot report in which each bar will display amonts Completed and NonCompleted result.
Thanks.

View 1 Replies View Related

Query To Calculate Conditional Field

Feb 5, 2007

Dear all,

I have a query that is based on orders table and contains "Qty", 'UnitCost', and "ctualUnitCost", in addition to yes/no control to indicate if "SalesTax" is applicable.

I want an expression in the query to calculate total cost in one of 2 conditions:
if no sales tax then TotalCost = Qty*UnitCost
if sales tax is applicable then Total cost = Qty*UnitCost*1.1

how can I write this expression

thanks

View 2 Replies View Related

Calculate # Of Occurrences Of String In A Field

Jun 25, 2007

Thought I'd be able to find out easily, but.. here I am...

How can I query the number of occurrences of a string in a field, e.g.:

if a name field has the text: Martin Lacoste

how can I get it to tell me there are two "a"s in the field?

I can use InStr to find one, and make a few more queries to find a few subsequent, but the data I need to search could have 30-40 occurrences of the desired text in a field.

Ideas?
Thanks!
Martin Lacoste

View 6 Replies View Related

How To Calculate The Accumulate Value Of The Current Field

Jan 21, 2005

If I have the field name as below:

Oil quantity
Accumulate Oil quantity
Date
Recordnumber


I want to get value of "Accumulate oil quantity" of current record to be equal to sum of "Oil quantity" from first record till the current record:

Let say
after I input "oil quantity" in the record # 1 ,2 & 3
The "Accumulate oil quantity" of record value show below:

"Accumulate oil quantity" of record#1 = "Oil quantity" of record#1
"Accumulate oil quantity" of record#2 = "Oil quantity" of record#1 + "Oil quantity" of record#2
"Accumulate oil quantity" of record#3 = "Oil quantity" of record#1 + "Oil quantity" of record#2+"Oil quantity" of record#3

Anyone know , please help
Thank you so much

View 1 Replies View Related

Calculate Text Box And Insert To Field

May 15, 2012

i have 1 table name "table1"and i have in this table 4 fields

1) id
2)pay
3) tax
4) total

The id is primary. I have form in this form i have 3 text box "pay" "tax" "total". i want to insert some number to pay and number to tax and make some button to make calculation of the 2 text box and it will show me the result in the total and insert them to the fields.

View 3 Replies View Related

Calculate Numerical Data In A Text Field

Nov 29, 2004

is there any way I can make a field with a data type that calculates numbers in the field but also allows text to be entered into the field (e.g. N/A or No Score) The non-numerical data certainly wouldn't have to be calculated and could be filtered out when calculating averages and other numerical operations.

Thanks

View 2 Replies View Related

Queries :: Calculate Multiple Values Same Field

Feb 22, 2014

I have a table where there are multiple vehicles, each identified by their vehiclenumber. Each record holds the vehiclenumber, date and odometer reading. I need to figure out how to calculate records in this table per each vehiclenumber.

Below is a code that works, but only when i have each vehicle with the same vehiclenumber.

SELECT tblOdometer.VehicleNum, tblOdometer.ODate, tblOdometer.Odometer, tblOdometer.Odometer AS OdomAlias,
Nz(DLast("Odometer","tblOdometer","[Odometer] < " & [OdomAlias]),0) AS Previous, [Odometer]-[Previous] AS Difference
FROM tblOdometer;

View 4 Replies View Related

Field In A Database That Calculate Age Of A Person Based On DOB?

Jun 23, 2014

I need to have a field in a database that will calculate the age of a person, based on their DOB. I have it in the excel sheet that I imported into Access to create the data base, but it just copied the number that was already there. When I add new entries into the database, the age isn't calculated. How do I do this?

The forumla I use in my spreadsheet is: =DATEDIF(O2,TODAY(),"y")

View 1 Replies View Related

How To Create A Field To Calculate Value Which Obtain Value From Different Table

Jan 1, 2013

I'm trying to summarize the value from multiple fields in a table and the total value will be updated on a different table as per highlighted below (taken from Northwind Web Database).

View 3 Replies View Related

How To Calculate Number Of First Letters Of Names In A Field

Sep 9, 2011

how to write out the query Im trying to create so I'll create an example:

Field: Drivers Names
I want to know how to create a query that will calculate the # of drivers names which starts with, A, B, C etc.
I want my query to be:

Drivers names first letter # of first letters
A 10
B 19
C 15

how I can create such a query

View 10 Replies View Related

Forms :: Can A Field Auto-fill From Previous Entry In Separate Field

Mar 21, 2013

I've just returned to work after kids and started managing a large Access database related to health, back-tracking over many years.

Currently in filling a form we physically enter:
Apples 2.2
red apple 2.4
red apple cut 2.45
Oranges 5.6
Cucumbers 8.5

Is it possible to get field 2 to automatically fill with a number code due to the text typed in field 1?

FWIW, I'm confident at more basic Access e.g making follow on default value = Dlast("field""table") type stuff but the more complex stuff I haven't touched since Uni over a decade ago and you will need to be gentle while I blow away the cobwebs

View 3 Replies View Related

Tables :: Auto Populate Field Based On Answer Of Another Field In Same Table

Feb 28, 2013

Within my table if Field 1 has an answer of Self (from drop down), then, I would like Fields 6-12 to auto populate; however, if Field 1 does not have an answer of Self, then leave Fields 6-12 blank.

I am not quite sure how to lay this out. I am using Access 2010.

View 8 Replies View Related







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