How To Calculate A Value In One Table Row From Another Row
Mar 22, 2015
I am relatively new to Access, and wasn't sure if this is query-related or not. I am trying to set up a database in Access 2013 for vehicle maintenance and fuel tracking. I have a table called "fuel" where I enter fuel purchases (including mileage at fillup, total cost, total gallons, etc.)
I want to have a field in this table called "MPG". This field, for a given row, will take the number from the "mileage" field and find the total miles traveled by looking back through the previous rows and finding the next highest mileage. It will then divide this difference by the number entered in the "gallons" field for the current row, and this will give the number for the "MPG" field.
I'm sure I'm making this sound way more complicated than it is - I'm pretty sure everyone knows how to do an MPG calculation - and I could do it with Excel by just using VLOOKUP or INDEX/MATCH (or even just by assuming that all fuel entries are chronological and looking back at the previous row). Just not sure how to accomplish this in Access!
I am trying to calculate 5 fields within my table in a query, but for some reason some rows do not calculate as not all the fields contain a value (blank field), is there anyway that I could bypass this issue?
and my question is,how to calculate the student table's grade value? i want the result like below.. #studentID score Grade Sminth 88 A samantha 76 B Williamv 45 F martell 78 B
I have created a database that records the scores of each player etc and puts them in league table like structure.
Within this database which I would like to extend I would like to know if its possible to have a query to calculate a league table for the teams if home team overal score is greater than away team home team get 2 points for win 1 for draw no points of loss and same if away team won.I also want to be able to change it to a web database and add club address details like contact list to it.
I'm trying to step from Excel to Access and I have some issues to translate functions that I used in Excel to Access.This is how it looks in Excel.And I have issues to translate the following function in Excel to Access.
=IF(AND(I6=0),"Done",K6-A$4)
A$4 is related to cell where it states the function =now() Basically what I want is to calculate in the tablecolumn is the following.In the column "Days remaining" I want the remaining days from "date to pay" and today. If the amount states "0" then the outcome must be "done".
I have a form for entering safety training which includes Training Date and Expiration Date, this form is called "Scheduled Training".
The table containing training courses with their requirements and frequency is called "Course Requirements". Within this table I have a "Frequency" field (1, 2, 3, 4, 5, 6) and another field "FrequencyPeriod" (Year, Month, Initial, As Needed).
What I'd like for the Expiration Date on the form to do: Use [ScheduledTraining].[Training Date] and look at both the [CourseRequirements].[Frequency] & [CourseRequirements].[FrequencyPeriod] to populate the Expiration Date.
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).
I am making a basic hospital management system in Access 2013.I have two tables named "Bed" and "Receipt". Bed(BedID,AssignedDate,PatientID,DischargeDate,Bed Charges) Reciept(ReceiptID,PatientID,BedCharges) I want to calculate "BedCharges" by calculating the number of days using "AssignedDate" and "DischargeDate" and then multiplying with a constant amount of charges per day. Also the BedCharges calculated in "Bed" Table also needs to be in the "Receipt" table. How can I count the number of days and then calculate the "BedCharges" in both the tables?
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 am struggling with calculate difference between two query table values, I first created a make a table query(current meter reading) which contains one column called "meter read" , and I created a second make a table query(previous meter reading) which also contains "meter read" column, I linked those two make table queries to calculate the value difference between two date, how can I create a form to calculate diff between any two date?
I want to count number of mountpoints appear in the table.
As below sample table, - Different mountpoints will appear, based on number of server using it. As you can see, the mountpoints will have repeating name based on number of server using it. - The Count_Mountpoints will calculate number of total Mountpoints appear in the list.
I have a problem in doing a task with my form. Actually I have a button to add a new record which opens a new form there i enter the values to the record. But when I press the Addnew record button I want to calculate the maximum of the Identity field +1 and open the new form with that new number which i have calculated. How can i do this....
I am trying to use dsum to calculate a sum from either a table or query to display on a form and this needs to be for different services within a period so I am using the following:
The Query is called KatiesPeriodTakings The field I want totalled is called Price The date field is AppDate and the 2 controlling dates are the Startdate and EndDate from the Form
I found the attached example a while back (can't find the site again though ) and it calculates a moving average. I've hacked out the parts I need for my own work and I can create my moving average query without an issue.
However, I need to extract the MA data into a table so planned on using append. I kept getting type errors so I tried make table to see what type it was creating and it appears to be Short Text rather than a number.
I've added an extra button and Make Table query to the example.
As far as I can tell from the code, the moving average value when calculated is a Single. However, when I write it to the table, its a Short Text.
How do I make the created Table use Number Type for my calculated moving average?
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)
How would i go about getting the field 'GMargin' (Gross Margin) to be calculated from subtracting 'SellingP' from 'PurchaseP' (Selling and Purchase Prices)?
I have a field which I enter a short time such as 3:13, 5:45, etc
Now i am creating a report which at the end of the report I would like to include an unbound text box, which displays the total amount of time for that field (pls note that the total will never exceeds 24 hours) What is the best formula to use in order to calculate this ?
Hello, I am trying to sum minutes from a database using an Access query, but the format in the database is hh:mm. I know the query to use in Oracle: ROUND(SUM((substr(actual_time,1,(instr(actual_time ,':' )-1))) *60 + (substr(actual_time,(instr(actual_time,':' )+1),2))) but not sure of the syntax to use in an Access expression. Any ideas?:confused:
Hello, I didn’t know how to explain this or where to post this but here we go! I have two databases and one of the PC's here at work can’t use some of the items in the database like on one of the databases there is some formulas in text boxes and that PC won’t calculate what’s needed in them and every PC in the plant will except for this one PC. Also the one PC won’t bring up the Allen Browne's Calendar. When you press the start or end button to bring up the calendar it never pops up but if I enter the start date and end dates and use it that way ect then it works. I have no clue why this is just happening to this one PC. Any ideas?
i have a table with fields "[DATE OF BIRTH]", "age", "temp-age"
i have a query that calculate the age: example; the query will get user to input the "temp-age" and upon "temp-age" is input and "ok" on the query, it will calculate a person's age and store the value inside table "age".
however, the calculated value for the age have some data problem. for example, when a person whose age is yy:mm 24:05 the age inside the table will be 1924:05. there will be a additional "19" at the age.
what cause the additional "19" problem? --------------------------------------------------------------------- my query in design view is like this; (see attachment) it uses " [temp-AGE]-[DATE OF BIRTH] " to calculate the age and store into table "age".
Hi, i'm trying to calculate the amount of employee for each company giving me a total in the queries.. how do i go about this? I'm not an person who uses Access to solve this. I have amount 20 Companies stored and over 800's employee.. Here are the field in the table:EmployeeIDEmployeeNameNumberofEmployeeThanks! Hope this helps.
