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