Queries :: Date Difference Between Rows In Same Column?

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 Replies


ADVERTISEMENT

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

Get The Difference Between 2 Rows Of Same Column

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

Queries :: Calculating Difference Between Dates In 2 Rows

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

Queries :: Combine Different Rows Into 1 Column

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

Queries :: Data From Rows To Column

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

Queries :: Match Multiple Rows From Same Column

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

Queries :: Date Difference Excluding Weekends And Holidays

May 6, 2014

Is there a way, in a query or via the use of a module, to calculate the workdays between two dates excluding holidays without needing to maintain a separate table with the holidays listed out?

For example, I would like it to calculate 21 work days for the month of May. However, I don't want to have to go in the first of each year and manually list out all the holidays for the year. Is there a way for the Holidays table to just contain the number of holidays in a given month (i.e. in December we get 3 holidays (Christmas Eve, Christmas Day, and New Years Eve).

View 5 Replies View Related

Queries :: Finding Previous Record Date Difference?

Dec 17, 2013

I am trying to use a query to find the previous record of a transaction and provide an additional field that computes the difference in the dates in days. (The days from TransferID 23 to TransferID 24)

TransferID Job Number Tool Number TranferDate DaysOnRent
22 1 10 9/1/13
22 1 12 9/1/13
23 2 10 10/18/13
23 2 12 10/18/13
24 3 10 12/15/13
24 3 12 12/15/13

I have seen other posts that reference the following link.

[URL]

View 8 Replies View Related

Queries :: Calculate Difference Between Two Date Fields To Get Total Time

Oct 26, 2014

I have to create a query in access that will calculate two Date and time fields [Date & Time Left]/ [Date Returned], need to figure out between the two fields. Trying to identify when the rep returned the call and the number of business hours (6:00am - 4:30pm) it takes to return a message in Ms Access 2010.

The only issue is the calculation has to be done by time and so I have to calculate what time they left the message(so the difference between [Date & Time Left] and [Date Returned) [Date & Time Left] and when the rep returned the message which is suppose to be [Date Returned] but the problem with this field is the data entry is in date format (10/9/2014, 00/00/0000) of Date and not Date and Time like the [Date & Time Left] field, so I don't know what to do now. Not sure what to do now not a database that create or have allot of control over.

View 4 Replies View Related

Calculating Difference Between Dates In Different Rows

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

Column Difference From Same Table

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

Queries :: Return Entire Rows With Min Date Only

Apr 15, 2015

I am looking to return one row from groups of the same EpisodeID whereby the row with the minimum date is selected each time. This includes returning all other fields in the row such as EventID below and ideally others as well if that will be possible.

To illustrate I include the following. What Access 2003 query would I need to return all the rows with the earliest dates? EventID will be unique in the intial table.

Code:
EventsTable
```````````
EpisodeID | EventID | EventDate
-------------+---------------+-------------
1 | 001 | 01/02/2010
1 | 023 | 05/10/2009
1 | 103 | 12/02/2010
2 | 004 | 02/03/2013
2 | 102 | 12/10/2014
3 | 546 | 04/05/2012
3 | 100 | 08/08/2013
3 | 034 | 10/10/2012
3 | 066 | 02/03/2013
4 | 777 | 05/07/2014
4 | 233 | 01/11/2012
5 | 087 | 10/03/2011

Code:
ExpectedOutput
``````````````
EpisodeID | EventID | EventDate
-------------+-------------------+---------------
1 | 023 | 05/10/2009
2 | 004 | 02/03/2013
3 | 546 | 04/05/2012
4 | 233 | 01/11/2012
5 | 087 | 10/03/2011

View 13 Replies View Related

Queries :: Find Latest Date In A Table Where Dates Are In 2 Separate Columns And Multiple Rows

May 19, 2015

I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)

I have a table "Assessment tracker" with the following structure

Name Type
Candidate short text
Unit short text
EV1 Date Date
EV2 Date Date

My Data:

Candidate Unit EV1Date EV2 Date
TH1 10 07/05/2015 25/05/15
TH1 10 07/05/2015 07/06/15

I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.

Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit

[Code]....

Output:

CandidateUnitEV1 DateEV2 DateAchdate
TH11007/05/2015 25/05/201525/05/2015
TH11007/05/2015 07/06/201507/06/2015

It does this by using a function shamelessly copied from the web somewhere...

Function Maxdate(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.

[Code]....

This is working well (I think)

I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.

Query:
SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate
FROM [Candidate AC Dates]
GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit
ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;

But this is returning

Candidate Unit MaxOfAchdate
TH1 1025/05/2015

I expect it to return

Candidate UnitMaxOfAchdate
TH1 10 07/06/2015

It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)

View 14 Replies View Related

Queries :: Query Date Between Two Different Column

Aug 22, 2013

emp_idtask_idstartdateenddate
1217/7/201320/7/2013
1321/7/201328/7/2013
2317/7/201320/7/2013

this is part of my table. It mean that empid-1 will work task 2 from 17/7/2013until 20/7/2013.Now i face problem in query that I have criteria that emp_id is 1 and date is 23/7/2013, I want to know which task_id assigned to emp 1 on 23/7/2013. The result should return 3 as the task_id

View 1 Replies View Related

Queries :: How To Put Dmax And Date Add Together In One Column In Query

Oct 10, 2014

I just wondered is it possible to add my dateadd calculation to my findlast calculation using dmax in one column in my query

here's what I hoping to put in one column as at the moment it takes 2

findlast: DMax("[ServiceDate]","[PlantServiceTbl]","([ImpPlantItemID]= " & [PlantItemID] & ")")

this is what I want to add without a separate column

DateAdd("m",[Months],[findlast])

View 2 Replies View Related

Queries :: Date - Multiple Columns Into Single Column

Jun 6, 2013

I want to create the date from multiple columns to single colums. Just for example

table 1 (local purchase)
Itemname Date
Apple 12/01/2013
Mango 13/01/2013

Table 2 (Import purchase)
Item Name DAte
Apple 12/04/2013
Mango 08/06/2013

Now i want to make one query, which can you the date as follows when we give criteria = apple

Item Name Date purchase mode
Apple 12/01/2013 local
Apple 12/04/2013 Import

Means two dates from different table into one query column... One way in my mind to make one table for both tables.

View 9 Replies View Related

Column Values To Rows?

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

Reports :: Summing Up The Rows In A Column?

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

Moving Column Data To Rows

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

Update Average From Different Rows In One Column To Another Row In The Same Table

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

Date And Time Difference

Jan 12, 2007

I have four fields

Date Sent (date) Time Sent (Time) Date Recvd (date) Time Rcvd (time)

I want to know how many hours have elapsed between date and time sent and date and time rcvd


Any Ideas please as I am having a blonde moment

View 14 Replies View Related

Date Difference Problem

Feb 1, 2005

I'm trying to figure out how many day are between now and a date on my form.
I've done a search, but I can't find much that talks about DateDiff().
Here is what I have so far and it's not working. I'm typing this value into a text box on a form.
=DateDiff(Day,Now(),"ASSIGNED_DATE_TIME")
I want the interval to be in days.
Thanks.

View 2 Replies View Related

Date Time Difference

Jan 5, 2007

I'm trying to find the difference between 2 dates [Start_Date] and [End_Date], however I want to strip out out of hours work, which is [OOH_START] eg, 17:30 and [OOH_END], eg, 08:00. Also I need to strip out the weekends as well.

Can you help?

View 5 Replies View Related

Modules & VBA :: Update All Rows Within Column Using Value Entered On Form?

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

Date & Time Difference & Calculations

Aug 17, 2005

I need to calculate (in a query) the Actual Time of a job. I have the start date, start time, end date & end time (all separate fields). This seems to works ok by subtracting the start from the end.

Then I need to calculate the Estimated Time (time it should have taken). This is done by taking the Quantiy divided by the Rate-Per-Hour. This seems to work out ok in the Query, e.g., 101000 / 15000 = 6.66667 hours. But when I display this result in a report as a "Short Time", I get 16:00 instead of 6:40. How can I get the correct display?
The other problem is that I need to divide the Estimated Time by the Actual Time. This also gives me wierd results - I suppose because one is a decimal format time and the other is a "Short Time" format. How can I get the correct answer?

View 2 Replies View Related







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