Calculated Field Based On The Value Of The Next Record

Nov 11, 2007

Hi,
I have a query that contains the results of repeated tests on a number of persons. The goal of treatment is to get those test results below 50. I need to find, for each person, the period of time (with a start and an end date) during which their test was below 50. The dataset looks like:

ID + LabID + LabDate + Result + Undet
----+------+-----------+-------+------
101 + 21 + 1/1/2000 + 780 + 0
101 + 21 + 2/2/2000 + 320 + 0
101 + 21 + 3/3/2000 + 50 + 1
101 + 21 + 4/4/2000 + 50 + 1
102 + 21 + 6/6/2005 + 1000 + 0
102 + 21 + 7/7/2005 + 900 + 0
102 + 21 + 8/8/2005 + 50 + 1
103 + 21 + 9/9/2005 + 50 + 1
103 + 22 + 9/30/2005 + 567 + 0

What I need is a query that produces the following:

ID + StartDate + EndDate
----+-----------+---------
101 + 3/3/2000 + 4/4/2000
102 + 8/8/2005 + 9/9/2005

I can also provide an attachment with more data, if that would help. Thanks in advance,

View Replies


ADVERTISEMENT

Tables :: How To Make Calculated Field Based On Field In Another Table

Jun 18, 2014

Table1 contain Two fields (3Months) and (6Months)

Table2 contain Two fields (3Months) and (6Months)

the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder

View 1 Replies View Related

Queries :: Calculated Field To Display Value Of One Field Based On Another

Jul 23, 2015

I have a query with a Date field for EndDate (the dates for end-of-week, Fridays in our case) and another field for Sales (number of sales, not dollars).I want to add 4 calculated fields that represent weeks and have the Sales appear in the correct column (field) for that date.So I will have columns for 10 July 15, 17 July 15, 24 July 15 and 31 July 15 and I want the Sales for each record to land in the correct date column, based on the EndDate column. (The 4 fields is just for the sake of the example, I will actually be having dozens of these calculated date fields).I tried to do it by setting up the 4 calculated fields like:

10Jul15: Sales
and then adding Criteria like:
EndDate = #10/07/2015#
It doesnt work.

View 3 Replies View Related

Calculated Field Based On SQL In Text Box

Feb 13, 2005

Hello, Bit of a newby to all this. Am trying to use a text box as a calculted field. I am doing so using an sql statment in control source The sql stat is built and tested using build query and then copied and pasted from sql view)

I keep getting #name? as an error.

I have managed using dlookup of the same saved sql to return the required value (which works) but find it to be a bit slow and clunky so wanted to give this a go instead. If somebody can have a look at the query below I would much apreciate.

=(SELECT Sum(QryInout!CostExVATGBP) AS TTlCostExVATGBP
FROM QryInout &_
WHERE (((QryInout.StockImportNoJoinToAll)=[Forms]![FrmSPurch]![ImportNoNew])) &_
GROUP BY QryInout.ProdType &_
HAVING (((QryInout.ProdType)="Freight"));)

Note that I have tried with and without the &_ and also with or with out the brackets..

Thanks

View 4 Replies View Related

Possible To Filter A Form Based On A Calculated Field...?

Apr 12, 2006

Hi.
I have an Order form that is based on an Order table. In this form there are also a number of text boxes (not bound to table) with calculations, and one final text box with a deadline based on these calculations.
Is it possible to filter this form based on the deadline? I can do this by making the form based on a query, but then again I'm not able to edit the information in the form.... Please anyone???

View 2 Replies View Related

Calculated Field Based On Accounting Year

Feb 8, 2006

Within a query i am trying to run historical pension values based on 3 fields from a table:

Value
Year
Period

Period: Is aligned with the accounting year April to March rather than Jan to Dec. So I cannot define Year as 2005 and period 1 to 12.

I am looking to define year as 2005/2006 and then calculated the sum of period 1 to 12.

Has anyone experienced this before and found a solution?

Thanks

View 2 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

Question On Query To Flag A Field Based On Calculated Data

Apr 25, 2005

I have a table with numerous fields including timeIn and timeOut field (these are date/time format). I need to find out how many people were clocked in during each operating hour (based upon their time in and time out). How can I run a query that will allow me to use the times in and out to 'flag' each hour that each person was on duty (a new field for each hour) so that I can run a count on the completed data for every hour?

View 4 Replies View Related

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

Queries :: Calculated Field To Find Latest Date For Each Record

Jul 11, 2015

If I have four date Fields in a query, Astart, Bstart, Cstart, and Dstart and want to have a calculated field to find the latest date for each record how would I do that? I have tried things like:

LatestDate: MAX(Astart, Bstart, Cstart, Dstart).

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

Calculating Field Based On Value Of Another Field In Previous Record

Sep 18, 2014

I'm trying to create something like an online banking view that shows the running balance as each transaction occurs. I have tblTransactions with fields AccountNumber, ItemDescription, and TransactionAmount. I'm trying to create something that shows these three fields and a fourth field with the running balance.

So if I initially deposit $100 it will show the first record with TransactionAmount = $100 and RunningBalance = $100. Then the next transaction will subtract the Transactionamount for the new record from the RunningBalance from the previous record to get the RunningBalance for the new record. So if I make a purchase for $2, the AvailableBalance for that record is $98. Is this possible with a query? Here's a picture to describe what I'm talking about ....

View 3 Replies View Related

Updating A Field Based On Previous Record

Apr 3, 2008

I'm trying to update an imported table from an excel spreadsheet with missing details. The table's records are in order so I just need to fill in a blank field with data based on the previous one as shown.


ID Name Location
1 Bob London
2 Larry
3 Harry
4 Jerry Glasgow
5 Paul
6 John Southampton


I need to fill in the location blanks simply with the last location details, so records 2 and 3 with London and 5 with Glasgow. Is this easily done or would I need to pull all the data into an array and work on it there?

I've tried searching for an answer but haven't had any luck.

View 1 Replies View Related

Value Of A Field Based Upon Value Of Another Field In The Same Record.

Nov 8, 2004

Hi,

My apologies in advance if this has been answered before.
Ok, Here is the question:
Is it possible to filter the values of a field (field 'y') based upon the value chosen in a previous field (field 'x') of the same record? Can this filtering occur within the table itself.
Values for fields 'x' and 'y' are taken from table 'ProductGroup' and 'Products', respectively.

liguor60

View 3 Replies View Related

Create A New Record, Opening A Form Based On The Key Field

Jan 28, 2005

I seem to need some help!
I have a table with customer information in it ie: name, vehicle, (Key=Id number)...
it is the master link to the repair table ie: mileage, (Key=ROnumber), and repair data..
these two work together beautifull
now I need to add another table, a check sheet for checking over a vehicle.
I create the table and the form however I cannot open a new form with the ROnumber from the second already open form into the newly open form.
(the second form is based on a Query, and I have tried changing it to a SQL statement both with no luck)
is there an example of what I need to be doing to link the ROnumber to the ROnumber in the new form, or create the ROnumber so it will add the data in the linked table?
I need this to open using a button on the open repair form
David

View 1 Replies View Related

Forms :: Sort Putting One Record On Top Based On Yes / No Field

Mar 8, 2013

My table has 3 fields, Employee Number (text), Job Description (text) and Current (yes/no). The table keeps track of the Job Descriptions that an Employee has had along with his current Job Description (actually Title is more appropriate a word but client requested Description) So the table has records such as:

0001 - Floor Sweeping - no
0001 - Ceiling Cleaning - yes
0001 - Dumpster Turning - no

The yes signifies that that is the current Job Description for employee #0001 and the others with no are previous Job Descriptions that the employee #0001 has held.

Now - I created a form to add these records to the table and on that form I want a list box to show the records in the table for this employee but I want to sort it such that the current Job Description is listed first and the rest show up in alphabetical order following. I have the query to list all of the Description, but how can I get the current one to always appear on the top of the list?

View 14 Replies View Related

Queries :: Query To Show Field Based On Last Record ID?

Sep 4, 2014

I am looking to get a query to show my list of customers "Grouped By" [CustomerName], that show only the single [CurrentBalance] field for each customer based on the "Last or Highest" [RecordID].

Also, each customer can have up to 4 different [StockType]'s but at least 1 [StockType].So my results would look like this:

[CustomerName] - [StockType] - [CurrentBalance]
Customer#1 StockType#1 5

Customer#1 StockType#2 4

Customer#2 StockType#1 5

Customer#3 StockType#1 5

View 6 Replies View Related

Queries :: Update Field Based On Existing Record Fields

Jun 3, 2013

I have a field called uniqueID which I would like to update based on 2 other fields that are already populated in my recordset (from running previous queries). I heard it is not possible to do an Update Select like shown below.

select max(uniqueID) from myTable where a = "value of field a from first record in record set" and b = "value of field b from first record in record set"

If it is true that I cannot do an Update Select then I am trying to do something in VBA. How can I Loop each row in the record set and store the values from fields a and b. I would obviously then need to pass those values into the sql above and store the result in a variable. I would then do a straight update to put the value of uniqueID into myTable.

View 1 Replies View Related

Modules & VBA :: Conditional Reporting Based On Value Of A Field In Selected Record

Mar 27, 2014

I have the following code that I need to modify:

Code:

Private Sub ChargeReport_Click()
On Error GoTo Err_ChargeReport_Click
Dim stDocName As String
stDocName = "Charges_Report"
DoCmd.SendObject acReport, stDocName, acFormatPDF, , , , "Charge Sheet"

[Code] ....

Currently, this code opens an input box that accepts the ID number for a particular record. Then it attaches a specific report for the selected record to an email. It works fine for this purpose.

I want to modify it so that it attaches one of several different reports depending upon the value of a [Staff_ID] field in the selected record.

I've tried a number of different solutions using an InputBox to get the record ID along with an If/ElseIf/Else construct that evaluates the [Staff_ID] field in order to determine which report to attach to the email, but I cannot find my error.

View 4 Replies View Related

Basing A Calculated Field On Another Calculated Field

Apr 12, 2006

My query contains two calculated fields [TaxSavings1] and [TaxSavings2], which are based on some currency and number-type fields in one of my underlying tables.

I just created another field in my query which looks like: [TaxSavings1]+[TaxSavings2]. Instead of adding the two fields, it actually lumps the two numbers together. For example, if [TaxSavings1] =135 and [TaxSavings2]=30.25, it will give me: 13530.25. I need it just to simply add, i.e. answer of 165.25.

Does anyone know how to correct this? Thanks in advance.

:confused:

View 1 Replies View Related

Auto-populate Data Into Field Based On Information Typed Into First Record

Sep 3, 2014

I'm trying to figure out a way to get my access database to auto-populate data into a field based on the information I type into the first record.

So what I'm looking to do is that when I type a name into the UserName Field it will automatically fill in the rest of the field with the same information.

To clarify I'm looking for a way so that when I type something into one record it will fill the rest of the field in with that same information.

View 8 Replies View Related

Modules & VBA :: Create Unique Reference Number Based On Field Values For Record

Jun 18, 2015

I want Access to automatically generate a reference number for a record based on the values in on two other fields for a given record using a form.

The first field is called Operation Number.

The second is Bag Number.

The reference number needs to be in this format: 19C.3.1

Where 19C is the Operation Number, 3 is the bag number, and 1 is automatically generated. Additionally I need the last number --the automatically generated one--to go back to 1 if with each new bag number.

This is kind of like library catalog numbers. Not sure how to do this.

View 2 Replies View Related

Modules & VBA :: Unbound Textbox To Display Memo Field Based On Listbox Record Selection

Apr 21, 2014

My table:

tblHeatTreatment
- HeatTreatmentID - PK
- HeatTreatmentDesc - Text
- HeatTreatmentDetails - Memo

My form has a listbox (lstHeatTreatments - Multi-Select disabled) that displays Heat Treatment descriptions and an unbound textbox (txtHTDetails) that I would like to have display the corresponding memo field when a description is selected from the listbox.

This is my code so far:

Code:
Private Sub lstHeatTreatments_AfterUpdate()
Dim myConnection As ADODB.Connection
Dim myRecordSet As New ADODB.Recordset
Dim mySQL As String
Dim selectedRequirementKey As Long
Set myConnection = CurrentProject.AccessConnection
Set myRecordSet.ActiveConnection = myConnection

[Code]....

When I run the code I get an error:

Quote:

Run-time error '-2147352567 (80020009)':

The Value you entered isn't valid for this field

When I debug, it highlights:

Code:

Me.txtHTDetails = myRecordSet.Fields

View 3 Replies View Related

General :: Calculated Columns Based On Another Table

Aug 18, 2012

Is it possible to have a calculated column based on another table. i have a 1 to many table. the table that is the parent hold a start time. all of the 'many' have an individual time length. i would like a calculated column in the parent table showing the full length. is it possible or will i have to use an update query?

View 1 Replies View Related

Forms :: Calculated Controls Based On Weekday In Form

Sep 18, 2013

Here is the basic info:

Table has Checkbox column
BusinessDate column (mm/dd/yyyy format)
OrderTotal column (in dollars)
There are others but these are the relevant ones

In the header of the form I have a calculated control box with the following control:

=Sum(Abs(nz([Checkbox],0))*nz([OrderTotal],0)),0)

Which works just fine. The user is presented with a list of all the orders from this table in the form. They check the checkboxes and the control in the header shows the total of the records checked. I need to make 7 of these calculated controls, one for each day of the week. Is this possible? This way the user can see the order totals for each day of the week they have selected.

I tried:
=IIf(Weekday([BusinessDate])=2,Sum(Abs(nz([Pull],0))*nz([OrderTotal],0)),0)

but it still sums up the whole week as the expression holds true.I do not know VB, and I'm sure there is a really neat and easy way to do it in there. I just don't know how.

View 4 Replies View Related







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