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.
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?
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.
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.
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.
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.
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.
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.
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)
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
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?
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.
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
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.
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
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.
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.
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;
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")
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).
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
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
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.