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!
View Replies
ADVERTISEMENT
Dec 18, 2013
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?
View 1 Replies
View Related
Dec 27, 2007
dear all;
i've a table #student like
studentID score
Sminth 88
samantha 76
Williamv 45
martell 78
and i also have a #grade table
Grade Start end
F 0 45
E 46 55
D 56 65
C 66 75
B 76 85
A 86 100
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
anyone can help me??thanks alot
regards
martell
View 12 Replies
View Related
Sep 5, 2013
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.
View 1 Replies
View Related
Aug 7, 2012
i am trying to clean up all unneccessary queries. i am now trying to get a calculation to apear in a text box.
what i want to calculate is [tblReceipts].[ReceiptCash] If Date()= [tblReceipts].[DateOfReceipt]
am i going about this the wrong way of is my pricipal correct but my expression wrong?
View 13 Replies
View Related
Feb 11, 2013
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".
View 3 Replies
View Related
Sep 22, 2011
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.
View 14 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
Apr 20, 2015
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?
View 8 Replies
View Related
Sep 25, 2014
I have a table that displays the year and month and another field that I want to calculate how many id have that month and year from another table.
View 3 Replies
View Related
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
Nov 27, 2014
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?
View 3 Replies
View Related
Feb 15, 2015
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.
MountpointsUsedbyCount_Mountpoints
/a/ins2server12
/a/ins2server22
/a/ins1server31
/b/ins2server31
/b/ins1server41
I only managed to get number of TOTAL mountpoints.... but by individual's mountpoint total count...
View 2 Replies
View Related
Nov 18, 2011
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....
View 1 Replies
View Related
Aug 24, 2013
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:
Code:
Me.txtTotalEarnings = DSum("Price", "KatiesPeriodTakings", "AppDate" >= CDate([Forms]! _&
[frmKatiesTakings]![txtStartDate]) And "AppDate" _&
<= CDate([Forms]![frmKatiesTakings]![txtEndDate]))
But I keep getting a mismatch error
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
View 5 Replies
View Related
Aug 16, 2015
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?
Using Access 2013
View 2 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
Dec 16, 2004
How would i go about getting the field 'GMargin' (Gross Margin) to be calculated from subtracting 'SellingP' from 'PurchaseP' (Selling and Purchase Prices)?
How do i do this? Help me please :)
View 5 Replies
View Related
Oct 6, 2005
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 ?
Cheers
View 4 Replies
View Related
Jan 18, 2006
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:
View 1 Replies
View Related
Aug 6, 2006
HI,
Excel is having difficulties calculating a workbook of mine. It complains about cirkular references, so I had to set the iterations manually.
The workbook holds about 5000 records and I´m using a cosinus function.
The thing is I don´t know what to do. Excel calculates the workbook, but it doesn´t do it right. What setting should I use with the iterations?
I´m thinking of running it in excel 97. Will that help?
I really need to be able to run this workbook. Does anyone have an idea?
Fuga.
View 6 Replies
View Related
Jul 10, 2007
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?
View 2 Replies
View Related
Jan 19, 2006
I want to calculate a data from anthony table, like (20 Jan 2005) + 365 days
will be 20 Jan 2006, how can i do it.
The Table setting is just like below:
Constituent (DataID,...);
Student (DataID, DOB,...);
School (DataID, 4YearsOld(As a Date Format),...)
Anyone can help me, Thank you.:)
View 3 Replies
View Related
May 12, 2007
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".
View 1 Replies
View Related
Nov 10, 2006
Hello,
I'm trying to calculate months not days between
EX: 1/1/06 to 6/30/06
I tried to use end date (-) begin date, but is only give me the difference in days. What expression do I use to get 6 months?
:confused:
View 2 Replies
View Related
Nov 19, 2006
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.
View 1 Replies
View Related
