Query Design To Produce Calculated Value Using Previous Record Value

Jan 28, 2013

I would like to build a query that has a calculated field, involving value from a previous record. I have five columns, Year (sorted ascending), InvestmentRate, StartingBalance, AnnualContribution, and InterestIncome.

The first row, being the smallest year value, uses a previously defined elsewhere starting balance, annual contribution, and interest income. This is recognized via an Iif statement and establishing the first set of values which the second row should use to calculate.

The second record, and until the end of the query, automatically calculates the new starting balance value based on the sum previous row record's starting balance, annual contribution, and interest income.

Furthermore, for the second record, and untill the end of the query, the InterestIncome is calculated value incorporating the second row's StartingBalance value (which is the product of the sum of the previous record's values).

This calculated referencing to the previous record is causing difficulties.

View Replies


ADVERTISEMENT

Calculated Field Utilising Data From Previous Record

Sep 14, 2004

Somebody please help me!!!

I am creating a database that will record the details of approx 30 shops and will store information on Annual Turnover, Profit, Return, etc.

I need to create a field that will calculate the increase/decrease in annual turnover between the current year and the previous year.

So far I have created a subform that will show the last 12 years of data for the particular shop, but now I need to add a calculated field that will get the current years turnover and also reference the turnover for the previous year, i.e. the previous record., and display the result. The other thing that I will need to do, is to have this field not calculated for the initial years trading, so this field will need to be hidden in the first record.

Can someone please offer some advice on how to go about achieving this???

View 1 Replies View Related

Having A Records Calculated Field Get Info From Previous Record

Jan 20, 2007

I have a table (tblStats). The user enters his current weight in a field named Wt. There are 2 other fields in the table named WkLoss and TTDLoss. These two fields are intended to be calculated. What I am trying to do is when the user enters his current weekly weight figure into the Wt field, I want the field WkLoss to get the user's weight from the previous record and place the difference in the field WkLoss. Also I would like to keep a total to date in the TTDLoss field.

So far this has been my attempt.
Code:Public Sub setWeight()Dim rs As DAO.RecordsetDim Startval As DoubleStartval = 190Set rs = CurrentDb.OpenRecordset("tblStats")'Select records to be updatedSet rs = CurrentDb.OpenRecordset _("SELECT * FROM tblStats ") rs.MoveFirst Do rs.Edit'field to update rs.Fields("WkLoss") = Startval rs.Update rs.MoveNext'increment step value Startval = WkLoss - Wt Loop Until rs.EOF rs.Close Set rs = Nothing End Sub
All I get is the value 190 placed in the first record and all zeros in the subsequent records. Also I don't have a clue as to how to get the Total To Date field to calculate.

Any help would be appreciated
Thanks for your time.

View 1 Replies View Related

Reports :: Unable To Produce A Report Which Uses A Static Design And Does Not Change

Jun 13, 2013

I'm trying to produce a report which uses a static deisgn and does not change. For example,I require in the detail of the report a table say 3 * 8 ....and in the report I need the data to fill each one of these cells. However I may not have 8 records , therefore I need the remaing columns/cells empty - the design of 3 * 8 must not change... at the moment I have a dynamic design so if i have 3 records I have 3 *3 table leaving a massive gap on the report.

View 2 Replies View Related

Queries :: Average Calculated Field From A Previous Query - Crosstab Data Type Mismatch

Jun 3, 2014

I am trying to construct a crosstab that averages a calculated field from a previous query. It is returning a "Data Type Mismatch" message.

The field I am trying to average is a subtraction of dates to find total days. I assume my field is not a number so I have tried to wrap it in CDbl() to change the type.

The formula is

Code:
CASE_DAYS: CDbl(IIf([Actual Close Date]-[Creation Date]>=0,[Actual Close Date]-[Creation Date],""))

View 5 Replies View Related

Refer To Previous Record In Current Record Query

Sep 15, 2005

In my query, i want to automatically display the value of another field from the previous record in a field in my current record. i.e.

Name Value Previous
Record 1 1
Record 2 2 1
Record 3 7 2
Record 4 1 7

Is it possible to create some kind of simple expression to refer to data in another record?

Cheers,
Ben

View 6 Replies View Related

Refer To Previous Calculated Field

Jan 17, 2007

Access 2003.
I was just wondering if there was any way to refer to a calculated field in the same query.
For example if the first calculated field was - Total: ([Quantity])*([Price]), could you add another calculated field - Inc Tax: ([Total])*1.175.
At present I am having to type the whole formula in the calculated field again which seems to work for some queries, but not for others, no matter how I fiddle about with the syntax. The other way is to create another query and refer to my previous query. Both these methods seem extremely cumbersome and I was wondering if I have missed something with referring to a prior calculated field.
The query above is just an example, my query formulas are actually fairly long and for editing purposes it can be difficult to correct them.

View 4 Replies View Related

Compare Current To Previous Record In Query

Aug 14, 2007

I have a table of customers who report trouble on their equipment. I would like to compare the date in the current record to the previous record to see if it occurred within 30 days. Can I do this with an SQL query or expression?

View 2 Replies View Related

Queries :: Find Previous Record In A Query?

Jun 13, 2015

How do I find the previous record in a query using the autonumber field?

View 10 Replies View Related

Queries :: Query To Pull Previous Record

Jan 20, 2014

I have a form that is used to book a new event for a client who is already in the database.Within that form I have a subform that is based on a query which displays information from that client's previous event. I did this using a solution found in the following thread: URL...It is based on pulling the second to last record that is related to the current client. It works perfectly when booking a new event that has taken place on a previous date. However, if the previous event occured on that same date (but at a previous time), it doesn't register.

I would prefer a query that would pull the record previous to the current one, instead of pulling the second to last record out of all that client's events.it would also mean that if more events are booked, then a past event is opened in a form, the sub forms in that form will display the event just prior to the current record.

View 14 Replies View Related

Queries :: Refer To Previous Record In A Query

Mar 29, 2015

I'm trying to refer to previous record in a query and i'm stuck as i do not know how to do it.

Here is my query:

Code:
SELECT
[Prod2Buy].[Product Code],
IIF([Missing] IS NULL,0,[Prod2Buy].[Quantity To Order]-[To Order]) AS [Rest To Order],
s.s_manu_code AS [Manufacturer Code],
s.prod_cost AS [Price Code],

[Code] ....

In fact the line: IIF([Missing] IS NULL,0,[Prod2Buy].[Quantity To Order]-[To Order]) AS [Rest To Order] should refer to previous record.

View 9 Replies View Related

Queries :: If 0 From Subtract In Query Then Goto To Previous Record

Oct 5, 2014

I want substract from 9/20/2014 totreg40 - reg = ovt but when reg=0 the remainder subtract from 9/19/2014 (previous record) totreg40 - reg = ovt, etc. The sum of reg = 40.

IDFECHA REGOVTTOTREG40
2309/15/2014 5.5 0 46.75
2319/16/2014 8 0 46.75
2329/17/2014 8 0 46.75
2349/18/2014 7.75 0 46.75
2979/19/2014 8 0 46.75
2359/19/2014 8 0 46.75
2339/20/2014 1.5 0 46.75

I want the result like this:

IDFECHA REG OVT TOTREG40
2309/15/2014 5.5 0 46.75
2319/16/2014 8 0 46.75
2329/17/2014 8 0 46.75
2349/18/2014 7.75 0 46.75
2979/19/2014 8 0 46.75
2359/19/2014 2.75 5.25 46.75
2339/20/2014 0 1.5 46.75

View 1 Replies View Related

Filtering The Last Record Of Many From Many People Using Totals In Query Design View

Jul 1, 2005

I have made a couple of posts about this and had no response as yet. Maybe i didn't explain it well.

I have a form that logs emails. The emails are either "to" or "from" a person. I want to have two queries that pick out either to or from. Now each person i know sends me several emails over time obviously but i am only concerned with their last email or MY last email to THEM because that determines if i should email them back or if i am waiting for them to contact me. At the moment i am having problems filtering out the last record for each person which determines whether i need to email them or they need to email me. I also use the record to log when the last mobile text was sent, phone call etc so it is not just emails. The following is what my formsfields look like

CommunicationID
Communication type
TO or From
Date
Day (automatically taken from the date)
Subject

I cannot for the life of me get the filter to work properly. Currently i am either getting more than one record for each person or the results are jumbled up. I really need a better understanding of how to use the totals thingy in the querys design view. I'd really appreciate some help on this. Thanks....Ross
:confused: :confused: :confused:

View 6 Replies View Related

Need A Query To Produce Current Date

Nov 28, 2006

Guys,

I am trying to put together a query that will give me a calculation of days between the currect date and a date in the future.

How do I tell a query that I want to display the current date, and then calculate this?

View 1 Replies View Related

Queries :: Produce HEX And ASCII In Query Results

Sep 17, 2013

We have a need to produce hex and ascii in the same results field.

The field with data will be bound to a 2D barcode (the square ones you see everywhere now). The format of the data is to look like this:

[)><RS>06<GS>xyz<RS><EOT>

The <RS>, <GS>, and <EOT> all need to be hex values as follows:

<RS> = Hex 1E, Decimal 30
<GS> = Hex 1D, Decimal 29
<EOT> = Hex 04, Decimal 04

When I run my Access query as follows:

BarCode: "[)>"&Hex(30)&"06"&Hex(29)&"xyz"&Hex(30)&""&Hex(04)& ""

It produces the following:

[)>1E061Dxyz1E04

So, it just converts it to ASCII; however, when I go to scan it, it actually reads that information also. I need the 1E, 1D, and 04 to actually be scanned as HEX for the validation to occur for our client.

Is it possible using an Access query to return these desired results? If not, I will have to look atother piece of software to produce the bar code labels.

View 6 Replies View Related

Forms :: Produce Data From A Selected File/ Query

Feb 16, 2015

I have over 100 queries of weather data, for each month, and would like to display certain or filtered information for a selected month.

I have been playing around with a combo box to select a month from a given year, and was hoping that maybe an 'After Update' would do what I want. The problem I have is that I don't know how to do this, as the month picked and displayed in the combo box is variable , and so a sort of wildcard might do the job, if I knew how.

View 14 Replies View Related

Forms :: Produce Datasheet That Derives Its Information From Query

Mar 6, 2013

I have a form that produces a datasheet that derives its information from a query. The query works fine giving the desired information.

In this datasheet on "PurchaseOrderID" i have a On Dbl Click event that states this.

Private Sub PurchaseOrderID_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "FrmPO_Received"
stLinkCriteria = "[ItemID]=" & Me.ItemID
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Now it works sometimes, but sometimes it pulls up a different "PurchaseOrderID". Is there a way to fix this. I thought maybe there would be a way to put two criteria, "ItemID" and "PurchaseOrderID".

View 2 Replies View Related

Dynamically Update Field Of A Current Record Based On Previous Record

Apr 30, 2007

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.

View 1 Replies View Related

Tables :: Auto Fill Values In New Record With Data From Previous Record

Apr 29, 2015

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.

View 10 Replies View Related

Forms :: How To Auto Enter Date Of Previous Record Into New Record

Aug 26, 2013

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.

View 3 Replies View Related

Queries :: Evaluating Current Record To Previous Record To Get Counts

Aug 8, 2014

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.

My attempted query: DLookUp("[Team]","[Team_qry]","[Team_qry]![Date]=#" & CStr(DMax("[Date]","[Team_qry]","[Team_qry]![Date] <#" & CStr([Date]) & "#")) & "#")

View 3 Replies View Related

Auto Increment A Number Based On Record Value From Previous Record

Nov 17, 2010

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

(a) to modify value or
(b) need to skip numbers.

View 8 Replies View Related

Queries :: Produce Query Using Combo / Pick List Field Held In Main Table

Jun 17, 2014

My computer has been updated to 2010 whilst I've been off sick (was 2003 before my accident).

I've created a main table, for devises across the company, and a combo box/selection box based on another table which holds a list of all the "Responsible" employee's aswell as another combo box/selection box for the device location.

So the person entering the information, can enter all the information for a device (torq wrench, socket set etc), who is responsible for it and which department they belong to (where to find the device).

Which all works fine

However, I'd like to create 2 queries, one to enable the user to run a report of all device's allocated with an employee or to be able to run a query for all device's stored in a particular department.

But I have been unable to set the correct query criteria, to enable to query user to be able to selection from a drop down list, which responsible person or location to pull back the correct list.

I was getting an error asking me to set the parenthesis, I have now deleted criteria for both queries, as even if I put [Enter] and type a Responsible person's name exactly as its held on the table, the report comes back blank.

View 1 Replies View Related

Queries :: Add Previous Record Data To Next Record

Apr 11, 2013

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.

RunningBalance is calculated as follows:

Code:
RunningBalance: IIf([a].[StudyYears]=[ReserveParameters]![StartingBalanceYear],CCur([ReserveParameters]![StartingBalance]),0)

Bal is calculated as follows:

Code:
Bal: [AnnualContribution]+[InterestIncome]+[Inflation_Adjusted_Expenditures]

How to go about adding the two together as in the picture?

Is there a way to do this relatively fast, as well as keeping the query updateable?

View 1 Replies View Related

Queries :: How To Compare One Record To Previous Record

Feb 20, 2014

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.

View 1 Replies View Related

Queries :: Can A Calculated Field Look At Record Before Current Record

May 1, 2013

I have a query to bring in values, I need to select 2 rows of data but the criteria is as such:

x= starting value on form

now the row of data must match the following criteria previous row to current row(ref temp)<=x And Current row(ref temp)>xnext row to current row(ref temp)>=x And Current row(ref temp)<x

I have dealt with SQL before but how to do the above.The isolated 2 rows of data will then go into unbound boxes on a form from which I will do intercept and gradient calculations.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved