Queries :: Difference Between Rows - How To Extract CHANGED Value In A Column
Jan 24, 2015
I have a query question . How can I extract a CHANGED value" in a column? Example here under table1. Time and TAG makes a record unique, No ID available ...
Table1
time tag value
10:02 Tag1 10
10:02 Tag2 11
10:03 Tag1 10
10:03 Tag2 11
10:04 Tag1 10
10:04 Tag2 12
10:05 Tag1 10
10:05 Tag2 12
10:06 Tag1 10
10:06 Tag2 12
10:07 Tag1 11
10:07 Tag2 12
10:08 Tag1 11
10:08 Tag2 11
I want to have as output
10:04 Tag2 12
10:07 Tag1 11
10:08 Tag2 11
(so at 10:04 tag 2 changed in value and on 10:07 tag1 changed value and 10:08 changed Tag2)
Preferably with the previous value as well, but this is not the highest prio
10:04 Tag2 12 11
10:07 Tag1 11 10
10:08 Tag2 11 12
View Replies
ADVERTISEMENT
Apr 20, 2014
I have the below query (A). How do I calculate the Day of Therapy as shown in B
A
MRN Last Name DOA Service startDate INR Dose WarfID VisitID
12001 Smith 6/28/2013 Warfarin 6/29/2013 1.20 6 mg 10 138
12001 Smith 6/28/2013 Warfarin 6/30/2013 1.20 6 mg 11 138
12001 Smith 6/28/2013 Warfarin 7/1/2013 1.3 6 mg 12 138
12001 Smith 6/28/2013 Warfarin 7/2/2013 1.40 6 mg 13 138
12001 Smith 6/28/2013 Warfarin 7/3/2013 1.40 7.5 mg 14 138
12001 Smith 6/28/2013 Warfarin 7/4/2013 1.70 7.5 mg 15 138
12001 Smith 6/28/2013 Warfarin 7/5/2013 2.00 7.5 mg 16 138
11002 Brown 7/1/2013 Warfarin 7/1/2013 1.00 2 mg 17 139
11002 Brown 7/1/2013 Warfarin 7/2/2013 1.3 4 mg 18 139
11002 Brown 7/1/2013 Warfarin 7/3/2013 1.50 2 mg 19 139
11002 Brown 7/1/2013 Warfarin 7/4/2013 1.8 2 mg 20 139
11002 Brown 7/1/2013 Warfarin 7/5/2013 2.20 2 mg 21 139
1205972 Jones 6/28/2013 Warfarin 7/6/2013 1.0 6.5 mg 29 138
[code]....
What I really want is to calculate the time (# day) it takes to reach a target goal INR (2-3) I have tried DateDiff between DOA (date of admission) and Start date but this obvious won’t work because some patient won’t start on warfarin until later.
Another possibility is to calculate date difference between two rows in same column but I don’t know how For example Pt 1 = It take 7 days to reach an INR of 2, Patient #2 = it takes 5 days to reach an INR 2.2 and Patient #3 takes 6 days to reach an INR of 2.1. Average = 6 days.
View 6 Replies
View Related
Aug 15, 2006
i have a table that has meter readings for copiers on it and dates.
i need to add another column to show the differences in the meter field between itself and the row entered before it (the month before) to find out how many copies were made. i can do this in excel very easily, but cant figure out how i would do it in access
sample:
Date Meter Diff.
06/01/2006 0 0
07/01/2006 1000 1000
08/01/2006 1500 500
we will be entering the date and the meters in every month, but i need to have it calculate the difference between months so i can then calculate overage, if any.
View 2 Replies
View Related
Oct 25, 2013
I have a list of client activity - client name, loan ID and loan date. I would like to create a new field that shows the number of days between one loan and the next. If I was doing it in excel, I would need to sort the data by client name and loan date and then calculate the number of days between the loan date of one loan and the loan date of the loan immediately prior to this.
View 14 Replies
View Related
Oct 16, 2013
I have a table which shows when a product is available in the stores.
For example.
Product Store Available
A NS Jan, 2013
A BC Marc, 2013
How can I combine the available/store field which just shows
Product Available:
A - Jan, 2013 in NS AND Mar, 2013 in BC.
View 1 Replies
View Related
Apr 6, 2014
I need to change rows data into column.
I have a table name as Temp_Report1 with fields Lab_No, Test Name, Reslt, Test_master
Lab_no
Test Name
Result
Test_Master
4.414
Hemoglobin
00
HAEMATOLOGY
4.414
Red Cells
01
HAEMATOLOGY
4.414
E.S.R
02
HAEMATOLOGY
4.414
Reticulocytes
03
HAEMATOLOGY
etc
The another table where I want to copy data is New_repo2 with fields Lab_no, v1, v2, v3 etc. Need as
Lab_No
V1
V2
V3
V4
V5...
4.414
Hemoglobin
Red Cells
E.S.R
Reticulocytes
Etc
How to change this from rows to one column.
View 1 Replies
View Related
Apr 2, 2014
I have parent-child one to many data in one pair of relationships, and now I've been asked to see be able to find out what matches a defined regimen; each is also defined in a parent-child relationship.
Best is to show sample data. I'm going to show them as two tables, but the "Components" are actually in a parent-child relationship, e.g.,
PersonList -= Meds
Regimen -= Meds as well
Note that PersonList and Regimen do not really have any relation; we just want to see if things are being done one of the ways they are "supposed" to be done, without a slow manual check. It's worked as set up for reports, and I really don't want to change everything to a big long list of fields, one field per med for a lot of reasons (not least of which is that is denormalizing)
Quy 1 Result:
PersList T1Component
Andrew Med 1
Andrew Med 2
Brett Med 1
Brett Med 3
Brett Med 4
Charles Med 2
Duane Med 1
Duane Med 4
Quy 2 Result
Regimen T2Component
Goody1 Med 1
Goody1 Med 3
Goody1 Med 4
Goody2 Med 1
Goody2 Med 2
I'd like to be able to do two queries - one that are "OK" one that are not. Don't need to replicate the med list, just the regimen if matching..
"Good" would return
Person Regimen
Andrew Goody2 (he has med 1, 3, and 4)
Brett Goody1 (he has med 1 and 2)
"Bad" would return
Person
Charles
Duane
What they "almost match" does not matter; it tells people which ones we need to check into a bit more.
View 4 Replies
View Related
Nov 18, 2011
I currently have a table that has patient ID (unique record (SSN)) and a date of visit field. The SSN may have numerous rows with different dates of visits. What I am trying to do is a DateDiff function that will retrieve records that are 48 hours apart or 2 days apart. We are tracking patients that return within 48 hours of a previous visit.
View 10 Replies
View Related
Jul 11, 2005
I have a table that contains a significant amount of data. The columns are as follows;
ID, WeekNo, Ref, Cost
What I want a query to do is only show me differences in Cost value where the Ref is the same
For example
ID, WeekNo, Ref, Cost
1, 1, ABC, 123
2, 1, DEF, 456
3, 2, ABC, 123
4, 2, DEF, 789
The query I would like, would only show me the result of Rows 2 & 4 because the value of cost is different between the weeks.
At the moment I have two queries, One that pulls out the last weeks value (WeekNo 1), and the other that pulls out the same information but for the comparison week (WeekNo 2). In the third query I created I linked the two columns from the Ref fields, but could not work the logic to only show me ones where the differences in cost were displayed.
Thanks in advance of the resolution to this. I know it is basic query work, but just have a logical block in getting it to work.
Regards
View 4 Replies
View Related
Jul 18, 2006
Anyone know how to reference the name or header of a particular column in a listbox control?
i've tried,
Me.lstBox.Column(1).Name
Scott
View 2 Replies
View Related
Dec 20, 2006
Hello I have a query in MSACCESS that looks like this
N| Q | A | B | C | D | E | F |
X|..4.|..1.|..6.|..7.|..9.|...|...|
now my problem is that I want to have a query select the 2 closes values to Q from columns either A B C or D and put them into columns E and F
I am just so stuck here, if anyone can help or give me a steer in a right direction i would greatly appreicate it!
View 1 Replies
View Related
Aug 25, 2006
I am trying to devise a way to display query results in a row instead of a column. The table on which the query is built has this structure:
Column1: Index (pkf)
Column2: Group (int)
Column3: Position (int)
Column3: ItemCode (txt)
When I do a select query, the data comes back like this:
Group Position ItemCode
11A
12B
13C
14D
15E
16F
Etc.
I want to arrange the data this way:
1ABCDE (Etc)
I keep thinking that a cross-tab query is the answer, but all I seem to be able to do is to get counts of Position 1, 2, 3 Etc.
I’m baffled. Does anyone have a suggestion?
Thanks,
Rick
View 4 Replies
View Related
Mar 20, 2013
How do I sum up the rows of a column in a report ? My example is here below
Sick Days
Employee1
Employee2
Employee3
Total of Sick Days
View 1 Replies
View Related
Dec 21, 2011
I have an Excel sheet with data organized like:
1 A
1 B
2 A
2 B
2 C
I want to reduce the first column to unique records, and have the second column listed on the same row. (I'm assuming I'll need Access to do it.) Is there a way to produce a table like this?
1 A B
2 A B C
The final result would need to be something I could put back into Excel.
View 3 Replies
View Related
Aug 2, 2012
I am still quite new to Access and have spend the last two days looking for a solution to an issue but don't seem to find anything that fits. The problem is that I want the three year average of [NIbyA] inserted in [AvgNIbyA]. The years are stored in [FocalYear] (as Date/Time formate) and [TextYear] (as text formate) and [ID] indicates which entity the record belongs to (see below).
UniqueID
ID
TextYear
FocalYear
NIbyA
AvgNIbyA
1020180-19981231
1020180
19981231
[Code]...
Example.accdb
I have been playing around with update queries but can not seem to get the syntax right. The query needs to add NIbyA for year 1998, 1999 and 2000, divide it by 3 (or use Avg() function or something of that sort) and insert the result into the row where [FocalYear] = 13/12/2000. To add to the confusion, it should obviously only add those records that belong to the same ID. Years are from 1998 to 2010 and ID's are random (this obviously means that the rows where [FocalYear] = 13/12/1998 and [FocalYear] = 13/12/1999 will not have an entry).
View 11 Replies
View Related
Nov 7, 2013
I'm trying to update all the rows in a column (column A, PO Number) within a table (iSupplierTable). The value (txtPONbr) is entered by the user on a form (NewPO).
Code:
Private Sub cmdSubmit_Click()
On Error GoTo cmdSubmit_Click_Error
Dim db As Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "iSupplierTable"
[code]....
View 9 Replies
View Related
Feb 26, 2015
I created a query and one of the fields was "name". In the query it listed the names and then changed to the ID number of the names from the name table. The query was created using the wizard. Why did the query change to the ID number mid report and how do I get it to report only the names and not the ID number.
View 5 Replies
View Related
Apr 22, 2014
I have a query based on 2 tables, joined on Memberid, the result showing :
Table1 Table1 Table1 Table2 Table 2 Table2
Category Association Memberid CustomerName E-mailAddress MemberID
Board Member(Lookup,integer) SAMGA(Lookup,integer) 44 Smith smith@abc.co.za 44
Board Member ADHTY 44 Smith smith@abc.co.za 44
Grower SAMGA 44 Smith smith@abc.co.za 44
I only want to show 1 row, based on the duplication of E-mail address. I know i should be using the row_number function, but cannot get to the result I want.
View 4 Replies
View Related
Jul 1, 2013
I have an Access Database and I want to split up one column into multiple columns. The one column has multiple rows that relate to one person, so for instance I have the following:I am taking information from both tables and I want to create a query but I need to separate out the mother, father, and guardian with the names attached
So from the Child table :
Joe Smith Address Phone
The query that I created is from two tables, the child table and the family table: ( I used the dashes just for it to be easier to read)
------Child Table/PrimaryKey --------Family Table---------------------
First Name--- Last Name--- First Name--- Last Name--- Relationship
Joe ----------------Smith ----------Jane --------Doe-------------Mother
Joe ----------------Smith ----------John---------Smith---------- Father
So in this scenario I need to get the name of the second parent in another column....meaning have all the parents appear on the same line for this child, and each child there after..
So I need it to look like this:
------Child Table/PrimaryKey --------Family Table---------------------
First Name------ Last Name------ First Name------ Last Name------ Relationship------ 2First Name------2Last Name------2Relationship
Joe----------------Smith------------ Jane-------------- Doe------------- Mother-----------John---------------- Smith------------ Father
View 1 Replies
View Related
Mar 29, 2012
I have a database with all the hours employees have logged stored in the database. Our payroll company wants an excel spreadsheet that has very specific info in particular columns and fields on the excel spreadsheet, so I'm trying to design a query which will put the correct info in the correct fields per their system.
The challenge is, I have currently a query with Employee ID, Overtime Hours, and Regular Hours as separate columns.
I need to translate this to a query with a single column for hours and a separate column that designates those hours as OT or Reg, with two rows for those employees who have both types.
Current:
ID / Regular Hours / OT Hours
101 / 70 / 7.5
102 / 30 / 0
103 / 5 / 0
Needed:
ID/ Hours / Type
101 / 70 / Reg
101 / 7.5 / OT
102 / 30 / Reg
103 / 5 / Reg
I don't know how to create a query or a formula in a query to break out each employee row into multiple rows with different data in the hours column. It seems like there's something pretty straightforward that I've done in a similar vein but it doesn't seem to work - I can do the opposite and combine those hours by using the SUM function in a query, but I can't seem to break it out this way.
Access 2007, Windows 7.
View 5 Replies
View Related
Jun 5, 2013
Using a database 2010. I have a monthly import from a spreadsheet to one of my tables. Sometimes it has the same previous data from prior month or months that has not changed. I don't want to keep appending this data to my table if none of the fields have changed.
Is there a way to append data to table only if fields have changed in an append query? Ex. (Name, Address, PhoneNum, Rate) If all fields are the same; don't append. If one or more fields are changed; append.
View 14 Replies
View Related
Feb 28, 2014
I have some queries that pull data from one table and other queries that pull data from two related tables. In both cases, how do I prevent the data from being changed after the queries results are displayed? I know I could make a form and lock each field so it couldn't be changed, but I was hoping to avoid having to do that.
View 6 Replies
View Related
Sep 24, 2013
I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.
View 7 Replies
View Related
Nov 14, 2007
I'm trying to set up a query where it returns me only the items that are repeated.
ie...I have a table that has clients that are one time clients and other that are repeated. Each client has a client number. I would like to get only the clients whose client number appears more than once. Is that possible?
View 1 Replies
View Related
Jan 2, 2014
I have a (simplified) table with
ID | Date | Time | machine | value
304 1-1-2014 06:00 115 0.54%
305 1-1-2014 06:00 111 0.56%
306 1-1-2014 07:00 111 0.52%
307 1-1-2014 07:00 115 0.53%
308 2-1-2014 07:00 111 0.56%
309 2-1-2014 07:00 115 0.58%
310 2-1-2014 06:00 111 0.54%
311 2-1-2014 08:00 115 0.53%
I try to find the ID corresponding to the maximum date+time grouped by Machine.
In this case I would like to find 311 (maximum date/time for machine 115) and 308 (maximum date/time for machine 111)
The way to extract the maximum date + time is not difficult
SELECT Max([Date]+[time]) AS datevalue, tbl_TexControl.Machine
FROM tbl_TexControl
GROUP BY tbl_TexControl.Machine;
But how do I get the corresponding ID? Not with Max([ID]) because then ID 310 is selected which is not the maximum of the time.
View 5 Replies
View Related
Dec 17, 2013
Model Full Name: "Jones, John P." (this was the one not in MS' examples)
Last Name Extract = Jones
lastN = IIf(InStr(1,[Model Full Name],",")=0,"",right([Model Full Name],len([Model Full Name] - (InStr(1,[Model Full Name],",")+2)))First Name Extract = John
firstN = IIf((InStr(1,[Model Full Name],",")+1)=InStrRev([Model Full Name]," "),Right([Model Full Name],Len([Model Full Name])-(InStr(1,[Model Full Name],",")+1)),Mid([Model Full Name],(InStr(1,[Model Full Name],",")+2),(InStrRev([Model Full Name]," ")-1)-(InStr(1,[Model Full Name],","))))
View 1 Replies
View Related
