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:
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
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.
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..
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???
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.
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?
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???
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.
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:
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.
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 ....
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.
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.
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
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?
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:
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.
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.
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.
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.
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.
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
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?
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.
