Modules & VBA :: Calculations Using Previous Record Calculation
Oct 4, 2013
I have partially done it using PrevRecVal module I found on the web.
I created what I need to do in Access in Excel first, the problem doing this in Access (for me) is I need to refer to the results in the previous record, PrevRecVal worked but I need to finish if possible.
The main report holds client policy data of which InvestAmount is used to start the calculation of the sub report, the data entered to run the calculations on the subreport would be
I have a database where I need to record student data, specifically marks and absenteeism. The marks are recorded each term as a running total. I have a final mark which needs to be equal to the 4th term mark.
I also need to record the number of days absent each term with a total at the end of the year.
My problem is that the term 4 mark is a in the previous record and the total days absent needs to be the total of the previous 4 records (term 1 - 4).
I using a create table query and an update query but that was a no go.
Hi, I'm wondering if anyone can help me with this query. I've had a look at some of the previous posts under previous record, but don't seem to be able to get to the bottom of it:
I have a set of data on employees who have all had one or more financial searches done on them and I want to return a field based on the previous record of that employee.
I would like an extra field that looks at the previous row, decides whether or not the previous row is the same employee id and if it is returns "Same" and if it isn't returns "Different"
I have a table full of meter readings. I want to have a field called consumption which looks at the reading just entered for that month and then subtracts the previous months reading for that meter which leaves the consumption.
Can I get a formula that can work this out automatically?
I was wondering if there is a code for Next and Previous record in a form but for a specific value.
Example: I have a customer that has a specific ID and that customer has a list of orders that all contain that ID. When I open a form for orders I would like to be able by using Next and Previous record buttons to be able that was to move only through records of that customer not the whole list of orders for every customer.
I am trying to write some vba code to auto fill a cell's value based on the value of the cell in the previous record (In a datasheet form) i.e. if the value in record one was '1' and the down-arrow key was pressed then the value '2' should be entered into the new ext record
I have written some pseudo-code to show what I am trying to accomplish:
if keydown = down-arrow and current cell contents isnumeric then Cval = current cell contents if current record = last record then create new record move down 1 record set cell value of new record to cval+1 else move down 1 record if cell value = null then set cell value of record to cval+1 end if end if
The GetDates Command BUTTON should generate the red data above, like:
Private Sub GetDates_Click()
Go to a new record Go to the field App_Freq and fill it with: Copy/paste the data from the field of the same name in the previous record Go to the field App_Date and fill it with: data based on this calculation using the PREVIOUS record fields: App_Date + App_Freq (which are days) Go to the field Action_Date and fill it with: data based on this calculation: App_Date (of current record now filled out - step above), minus 7 (days). End Sub
That's it!
All the dates format is set to medium date. An error message should come up in case App_Freq is empty, for example
Also note that the data can be manually changed at any time and should not revert to its last calculation. The button only generate data into a new record.
I want subtrack a value on the current record from the previous record and display the result in text box in subform datasheet. This is done during data entry. The calculation works most of the time and randomly it returns the wrong value. I have the code in an after update event after I enter a number. Here is what my code looks like:
I am building an application for a small clinic. In the interests of good database design, I don't want to be storing numbers that are derived by calculation.
A patient with insurance will have a deductable that will be entered into a field on their first (ms access) transaction form.
A calculation will put the remaining deductable into another field. No problem. However, the NEXT visit has to look back to find previous transaction records, again perform the initial calculation and then perform another for this transaction, and again the next time and the next until the deductable reaches zero.
Additionally, since I'm not storing these values, if I pull up an old transaction form, that form has to re-perform the calculation ONLY on transactions from that date and earlier. It must not reflect visits that took place later.
So, this field must query tblTransactions, filtered by the name of the patient (a combo box called tblTransactions.Patient_Name that looks at tblContacts), the transaction date/time (stored in two date/time fields, cleverly named tblTransactions.Date and tblTransactions.Time), and the total deductable value stored in a field called tblTransactions.Deductable.
As someone who has never coded anything more complicated than DOS batch files, I'm at somewhat of a loss as to how to go about this.
Any assistance, code-wise and which Property value to put it in would be MOST appreciated.
I need a way to dynamically store a particular value in "field_2" of the CURRENT record depending on whether or not the value of "field_1" of the CURRENT record is identical to the value of "field_1" of the PREVIOUS record within the same table. The table is sorted on "field_1".
So, if the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is also "ABC", then store a value of "PPP" in "field_2" of the current record. IF on the other hand, the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is "XYZ", then store a value of "WWW" in "field_2" of the current record.
I have a report that will use these results to count only the number of records that have a "WWW" in "field_2".
Is this doable, maybe in a query somehow?
I should add that whatever the solution, it needs to be compatible with Access 2000.
How to fill values in a new record with data from previous record?
I've total 11 columns in a table and values in 3 4 columns are repeating for few rows before it needs to be changed eventually. I want to fill these rows with values from previous record.
I need to know how to auto enter the date of the previous record into a new record. I have this db for my vehicle log sheets and I submit monthly a claim for the business km from my company. I usually cant remember the date of the previous claim and have to scroll back until I find the field with a date. It will be useful if the date was copied into every new record until I edit the date when I submit the claim. What I ask is during the new month all the dates will be the same until I submit the claim. I do not know how to do this, and yes I have done a search.
I'm struggling with a query to evaluate current and prior record data. I have a query producing 5000 records. I need to group records by Case ID and compare current date record to previous date record to determine if a team and worker name has changed during the year. I need to count how many times a cases is transferred to and out of a particular team from the beggining of the year. i.e., On 1/1/2014-Team1 has an inventory of 500 cases.
During the month 25 cases are transferred into Team1 and 15 cases are transferred out of Team1. So on 2/1/2014 Team1 begins with an inventory of 510 cases. Throughout the year cases come and go from and to Team1 each month so need to figure out how to create a query to count each change. See attachment displaying how the data is listed and how I invision it to work with the In/out column counts.
Specifically, at the begginig of the year (1/1/14) for case ID 1003 you can see it belongs to Mary in Team1 for January and February. Then in March the case is transferred out and went to Joe in Team 3. So for Mary a "-1" is recorded as a negative count for that Case ID. If later in the year the case is transferred back to Mary a "+1" would be recorded. Respectively evaluated for each of the 5000 records to get a total count for each of the teams by Case ID throughout the year.
I've started with DLookup, tried comparing current month to previous month using DMax. It works as long as I only select one case ID used in a separate query but if I use the whole subset (5000 records) it fails. I can't figure out how to group each set of case IDs and then apply the query.
create a macro that automatically fills in the next invoice number in sequence",,I'm assuming this macro would look at the value of the previous record and add a one to itI don't want to use a AutoNumber field as I might need
I am trying to make calculations at a single record level. I have many products and I want to select only 6 of them, which I just found out that I can do it by their SupplierID. then I have a query that finds the amount of pcs that I have for each product. The aim is to determine the final weight of this 6 products. So to start I need to find the weight of each individual product by multiplying the number of pieces (from the query) by the weight of a single piece. Obviously each product weights differently, and at the end I need to add all the weights of the individual products to make the total weight of the 6 products.
don't know how to approach the problem of selecting a single record. And also I don't know what would be best to do it in a query, a form or a report.
I have solved the problems getting values on the subform. I have not in getting values on the subform.For instance, on the one titled phone use the formula in the tutorial is:
=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]
#Error results when the doc is put into a form mode.
Now when I input each value in the equation above separately.I still get no entry.
For " = sbfCustomerRoomUse" , I get #Error; and for "= txtTotalPhoneUse", I get #Name.
CustomerRoomUse and txtTotalPhoneuse are from the subform that was dropped into the customer form in a previous step. It shows that explicitly when designed sbfCustomerRoomUse on the main that CustomerRoomUse come from a subform. This does not seem to need to be done with txtTotalPhoneuse, and I am not sure why. Neither one gives me a desired calculation result.
I need to add two previous record fields together in a query. I have attached a picture to illustrate.
The "StudyYears" filed is the primary key of the table the data is being pulled from. The zero's are placeholders for the sum of the previous record's data.
How do you compare one record to the previous record? I have a query that shows items sold. It is sorted by day with today on top. I would like to create a column that has a 'thumb up' or 'thumb down" if todays was a better day than yesterday.
Ok i know negative numbers can be a bugger in calculations:
Code: Function FEV1pred(height As Double, ClientID As Double) As Double
Dim a As Double Dim Age As Integer Dim DOB As Date Dim TestDate As Date Age = DateDiff("yyyy", DOB, TestDate) + CInt(Format(DOB, "mmdd") > Format(TestDate, "mmdd")) 'In this case the clients Age is 39 a = -0.0244
'Now for the fun stuff...
FEV1pred = a 'returns -0.0244 so i know the dim a holds the neg number FEV1pred = (a * Age) 'returns 1.708 (which is incorrect, how this calculation is reached.) FEV1pred = (a * 39) 'returns -0.9516 (which is correct) End Function
So something about the dimming of Age seems to be doing something to the equation but i can't work out what?
I work for a bank and have build a few little DB's for differant groups but my current project is a work flow tool for a department. I have it 90% completed and i still have to build in the reporting side. Part of the reporting is caclulating times and this is where i am having problem.
I have some coding built but i am getting inconsistant results, as an example if i am trying to calculate the total amount of time between [start Time], [End Time] then minus any [Stop Time].
I have this on a command button, i have this same code for a few other calculations and in some cases it seems to be working i have just changed my start and stop fields along with what stop time to take out in each case. I have attached some parts of my DB, one of the modules, my main table and the form where i am running the coding.
I have a form with a subform in a navigation menu. Something like:
Navigation Form NavigationSubform ParentForm (header data and some unbound calculation fields) Subform (Multiple lines tied to query)
I have some fields in the ParentForm (i.e PF1, PF2) that would effect the values on the Subform (i.e. SF1, SF2). Also, there are some user editable values on the subform that will calculate the remaining fields on the subform (still using data from parentform).
My calculations work fine for the changes made on the subform. However I need to be able to calculate all the children lines on the subform when the form loads or a change is made to certain fields on the parentform. My On_Current event only wants to recalculate the first line.
Parent Form PF1=10 PF2=3 Subform QTY ADJ SF1 SF2 Line 1 5 .05 10.6 112.89 Line 2 8 .14 10.38 105.15 ... Line N SF1=PF1+(PF2/QTY) SF2=SF1*(SF1+ADJ)
So if PF1 or PF2 were changed then all the lines is subform would recalc SF1 and SF2. If changes were made to QTY or ADJ, then that line would recalc SF1 and SF2.
I have an inventory report that I am given among the fields are Item, Ship Date, Signed Quantity, Filled-Recvd, On Hand I am given an initial amount in the onhand field but the fields below that for that item are blank. What I would like to do is [Signed Quantity] - [Filled-Recvd] and then add that to the On Hand from the previous record and place it in the on hand field..
If it were in excel I would say G2 – H2 + I1 G = Signed Quantity H = Filled-Recvd I = On Hand TIA! Scott
I have a Motorhome which I want to check it's Fuel Economy, seems simple! I record the odomiter readingwhen I fill up with petrol and the amount of petrol pumped in to fill tank and the cost I have done it in Excel 97 but I would like to convert to an Access 97 Database. The purpose is to open an Access Form, add a record with Three Fields, namely the odomiter reading, Amount of Fuel pumped into tank, and Cost. I would then like Access to subtract the previous record's odomiter reading from the new odomiter reading to obtain the distance traveled since last fill and using this number and the other 2 fields I can calculate my fuel economy I tried to attached the Excel (97) spreadsheet for your information but it was too large for this forum The calculations are based on Australian Dollars, LPGas and Litres but it would make no differance if it were Gallons, US Dollars etc. I would very much appriciate a note to tell me if anyone is able to assist me with this problem. Thank You Paul Leaver Glenview Queensland Australia rpleaver@bigpond.net.au
