Subtracting Dates From Adjacent Dates In Same Column
Sep 7, 2006
Hiya-
I have a database with 5000 entries, corresponding to about 10 entries for about 500 people. Each of the entries is dated, and I need to calculate the time intervals between each person's sequential entries in the table.
One way of doing this is to create another column that contains the date of the previous entry. I can then use DateDiff to subtract one date from the other and give me the difference in days.
This approach falls down if I then work with only a subset of the entries - I would have to re-enter the previous entry dates as the time intervals would have changed.
What I really need is a way of subtracting the date from the date in the cell directly above it. Will Access let me do this, or is there a better way?
Many thanks, Jules.
View Replies
ADVERTISEMENT
May 12, 2014
Any way to have a form with Dates as column headers to update a table where the dates are stored in rows???
The table set up is like this:
tblOpHdr
DiaryID (PK) - OpDate (Date)
tblOpDetail
DiaryID (FK) - CostCode - MachineNumber - MachineHours - etc
I'm just wondering if there's any way I can do this with a datasheet or a crosstab type setup?
It's Access 2010.
View 1 Replies
View Related
Dec 12, 2005
I found the following code on this site in a search for a method to subtract two dates ie [Startdate] and [EndDate] and display the result as a whole number. ie 2, 6 etc. This code finds the difference but displays the results as 1 year 5 months 2 days, where I need the equivalent as 17.
Can this code be modified to produce the desired result and if so how would I go about it. Thanks for the help.
Option Compare Database
Option Explicit
Public Function getTimeElapsed(StartDate, Optional EndDate As Date) As String
On Error GoTo Err_Handler
If IsNull(StartDate) Then
getTimeElapsed = "No startdate"
Exit Function
End If
Dim YY As Integer
Dim MM As Integer
Dim DD As Long
' use current day if EndDate is not supplied,
' + 1 makes both days inclusive.
EndDate = IIf(EndDate = 0, Date, EndDate) + 1
MM = DateDiff("m", StartDate, EndDate)
DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)
' recalculate if DD is negative.
If DD < 0 Then
MM = MM - 1
DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)
End If
YY = MM 12 ' integer division.
MM = MM Mod 12 ' remainder.
getTimeElapsed = YY & IIf(YY < 2, " year ", " years ") & _
MM & IIf(MM < 2, " month ", " months ") & _
DD & IIf(DD < 2, " day", " days")
Exit Function
Err_Handler:
MsgBox Err.Description
Exit Function
End Function
View 1 Replies
View Related
Feb 1, 2013
My form consists of a textbox with a selected date. I have a combo box with integers 1-15. I have a table with 15 people and their birthdays.
I am trying to find the birthdays after a selected date by the integer selected in the combo box. For example, if I select 2/1/2013, and pick 7 in the combo box, the subform should show me people with birthdays 2/1 through 2/8.
I have a query with a datediff function. This is my query expression Expr1: DateDiff("d",[DateofBirth],[Forms]![Calendar]![Text1]) The criteria is <=[Forms]![Calendar]![Combo3].
It displays some data, but it's not consistent at all. I'm hoping to fix this without any SQL changes or form code. But if that's what I need, I'll do it.
I'm using access 2010, and I'm fairly new at it.
View 1 Replies
View Related
Oct 30, 2006
Is it possible to perform calculations across 2 adjacent rows in SQL?
At the moment, I have a running count that tracks the number of updates that go into our database against a specific JobID.
Is it possible to subtract the running count for a previous update from the current update.
For example, I would like my resulting table to end up looking like this
JobID Time Updates Since Last Update
1 10:10:00 1 1
1 10:30:45 3 2
1 11:15:10 9 6
In this example, the Since Last Updates should be found by
subtracting the Updates at 10:10:00 from the Updates at 10:30:45 giving 2 and the Updates at 10:30:45 from 11:15:10 giving 6. It would continue like this for as many rows of data have the same JobID (which could be several hundred!)
What I would then like to do is use the 'Since Last Update' number to display only those updates that are applicable to a certain Time, perhaps using Select Top in SQL.
Am I approaching this from the right direction, or would it perhaps be better to try to do this using Time, as this will always be unique?
Thanks for looking
W1dge
View 1 Replies
View Related
Jul 6, 2007
Jus thought I'd offer this code to the forum as spent a long time finding a suitable solution..
SELECT DischargeTime.ID, [Arrival Times].ID, [Arrival Times].[A&EArrivalDate], [Arrival Times].[A&EArrivalTime], DischargeTime.[Date of outcome], DischargeTime.[Time of outcome], DateDiff("d",[A&EArrivalDate],[Date of outcome]) AS DateDifference, [Time of outcome]-[A&EArrivalTime] AS TimeDifference, IIf([DateDifference]>=1,([DateDifference]*(1/24)*576)+[TimeDifference]*(1/24)*576,([DateDifference]*(1/24)*576)+[TimeDifference]*24) AS hours3
FROM DischargeTime INNER JOIN [Arrival Times] ON DischargeTime.ID = [Arrival Times].ID;
Basically I had an admission date and in a seperate box an admission time and then I had a discharge date and discharge time again in seperate boxes. The information was spread over two tables hence the join. How I solved the problem was by gaining both the days difference between and then the difference in hours and then I created an iff function and played around with it till it worked. Dont really know how i got it working in the end but it does so hopefully this code will save some1 alot of time!
(I know that if id shown my dates as date/time in 1 box the problem would have been much easier solved, however I dont personally like displaying dates and times that way especially when you have inexperienced people entering data)
Find below a result from the query
DischargeTime.IDArrival Times.IDA&EArrivalDateA&EArrivalTimeDate of outcomeTime of outcomeDateDifferenceTimeDifferencehours3
1119/06/200715:0420/06/200712:00103:0420.93
View 2 Replies
View Related
May 7, 2013
My first question is how to set a column to be the current date read by the computer.
Example: It will automatically say 5/8/2013 tomorrow without needing updating.
Question 2: How to add a number of days to the current date.
Example:
Add 7 days to the current date.
on 5/8/2013 it will read 5/15/2013, then automatically update to 5/16/2013 on 5/9.
View 1 Replies
View Related
Nov 6, 2005
Here's a sample of the query code for survey I made, for example
Question 1 will be
1) Male or Female?
(radio button answer_id 111) Male
(radio button answer_id 112) Female
etc etc...
My question is how do I set my query to show a graph / percentage value of total unique answer_id's in relation to the question_id.
e.g. Question 1: 66.67% chose 111, 33.3% chose 112
question_idanswer_id
1111
1111
1112
2116
2117
3119
3121
4126
4126
4126
I hope I was clear enough am quite new to Access and I really thought I could do this with pressing a few filter/sort buttons but alas I haven't found a way yet.
View 1 Replies
View Related
Mar 29, 2012
Question... i Wanted to create a Graph that has a bar for each category or column that has dates.. i want it to filter to show only the dates greater than 12-31-2011 so basically all dates in 2012.. i figured out how to do a query but i was only able to do a query for ONE column at a time so i'll have like 30 querys...is there a way to do this with one query and have each bar on the graph be for each column and have the graph showing how many people have completed these after 12-31-2011. URL...
View 10 Replies
View Related
Aug 28, 2013
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
View 1 Replies
View Related
Apr 9, 2015
I have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record)
I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date
So the first step (as I see it anyway) is to try to create a query which will give me each date between the two reference dates, in the hope that I can then JOIN that onto another query to perform the necessary calculation for each of the returned dates.
Is there a way to do this?
So basically, if for a particular record, the 'start' date is 01-Apr-2015 and the 'end' date is 09-Apr-2015, can I produce a dataset of 9 records as follows :01-Apr-2015
02-Apr-2015
03-Apr-2015
04-Apr-2015
05-Apr-2015
06-Apr-2015
07-Apr-2015
08-Apr-2015
09-Apr-2015
(The *obvious* solution would be to create a separate table of dates, from which I could just SELECT DISTINCT <Date> Between #04/01/2015# And #04/09/2015# - but that seems like a dreadful waste of space, if that table is only required to generate the above? And it would have to cover all possible options; so it would either have to be massive, and contain every possible date - ever! - or maintained, adding new dates as necessary when they are required. Seems horribly inefficient!)
Is it possible to just select each date between the two reference dates? Or can you only query something which exists somewhere in a table?
View 4 Replies
View Related
Aug 21, 2013
So I have a report generated, listing all my companies personnel in one column and the next column has the expiration dates of a certian training certificate. My question i would like to add some statistics to the bottom of the report, mainly how many certificates are expired, which is the ones over a year.
I have attempted to use:
=Sum(IIf([AT_LEVEL 1]<"Now()-365",1,0))
previously in excel my spreadsheet counted it like this:
=COUNTIF(C5:C77,"<"&TODAY()-365)
View 3 Replies
View Related
Feb 24, 2014
How to create a table in MS Office. Current table has name in first column and start & finish dates in other two columns. It is necessary to create a table in MS Access which has name in the first column and validation dates in the second column. For Example:
currently
1. White 16 xxxx19
2. Black 1 xxxx 5
required
1. White 17
1. White 18
2. Black 2
2. Black 3
2. Black 4
View 1 Replies
View Related
Jul 8, 2014
I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.
To explain the query I need, for example:
table1
01/01/2014
15/01/2014
17/01/2014
30/01/2014
table2
01/01/2014
02/01/2014
05/01/2014
17/01/2014
18/01/2014
20/01/2014
21/01/2014
25/01/2014
So the answer of the query would be 2,0,4.
Explanation:
Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates)
Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2
Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 2
View 2 Replies
View Related
Nov 15, 2011
I have a master table which shows all transactions per record (person) over a financial year.
Each record person has a seperate package period over which their spend needs to be measured. Therefore although I have all their transactions for the year, I only want to sum their transactions between their given [start date] and [end date] which are in columns.
I need to be able to create a field which sums all expenditure per record between the start and end dates
Name Start Date End Date Invoice Date Amount
Matt 15/5/11 15/9/11 1/11/11 £100
Matt 15/5/11 15/9/11 7/7/11 £200
Matt 15/5/11 15/9/11 12/12/11 £200
In this case I would only want to sum 7/7/11 as this is between the start and end dates
I want to write something like sumif([Invoice Date] is between [start date] and [end date] - not sure where or how exactly
(The start date and end date will always be the same per person)
Is this possible in access?
View 10 Replies
View Related
Jun 30, 2014
I am trying to format an excel spreadsheet through access, specifically trying to convert a column from text to dates (I receive the data in text format and need to translate it to dates).
Code:
Dim excelApp As Object
Set excelApp = CreateObject("Excel.Application")
excelApp.screenupdating = False
excelApp.Visible = False
Set excelWB = excelApp.workbooks.Open("Z:DataBasicSMData.xlsx")
[Code] ....
I'm no longer getting an error, but it isn't actually modifying the spreadsheet...
View 2 Replies
View Related
Nov 3, 2005
Hi,
Please bear with me here as it's a little involved.
I'm doing a staff profile website which includes a section where they can enter their annual/other leave details.
I decided to store their leave in two fields Start_Date | End_Date rather than each individual date that they took - the short and wide approach vs long and narrow.
This has left me needing to do a query that would return all the dates between the start and end dates inclusive.
Example:
StaffID---Start_Date---End_Date
---1-----12/12/2004--14/12/2004
Returns:
StaffID---Leave_Dates
--1-------12/12/2004
--1-------13/12/2004
--1-------14/12/2004
I appreciate i could do this using some script to loop through a recordset and build an array of dates but i wondered/hoped that it could be done using SQL.
As it is an asp page i can't use user defined functions in a VBA module in Access so the solution would need to be pure SQL.
Is this possible?
Any help v.much appreciated.
TS
View 3 Replies
View Related
Apr 4, 2012
I have a scenario where the first three rows of date which have dates of 4/1, 4/4/ 4/6 with ndc 5513026701; next six rows that have dates from 4/8 to 4/20 with ndc 5513014801; next three rows that have dates from 4/25, 4/27, 4/29 with ndc 5513026701.
The issue I am having is I do not know how to have separate min/max dates for ndc 5513026701 since when I group by ndc 5513026701 min = 4/1 ; max = 4/29. I need to have min = 4/1 and max = 4/6 for one row and another row of min = 4/25 and max = 4/29.
Any easy way to sequentially create min/max for each ndc 5513026701? I wasn't sure how to verbalize this so I have attached a sample worksheet.....
View 2 Replies
View Related
Aug 18, 2014
I'm not sure if I am biting off more than I can chew. I have a text field in each record in my database (Inherited) The db has nearly 5,000 records. I would like to split the field into records in a seperate table. An Example of the table as is now;
Code:
MemberIDBoats
5882Opossum(78-80) (87-89) Otter(80-84) Opportune(91-93) Turbulent(97-00).
5883Astute Auriga Aeneas Affray Amphion
2407H34 O10 Porpoise Trenchant Tapir.
I want to create a table as follows;
Code:
MemberIDBoatFromTo
5882Oppossum19781980
5882Oppossum19871989
5882Otter 19801984
5882Opportune19911993
5882Turbulent19972000
5883Astute
5883Auriga
5883Aeneas
5883Affray
5883Amphion
Etc.
Is this possible in one hit or do I need to process the records without dates first and then run another process to split those with Dates? I say dates but the field is a text field. About 15-20% of the records contain dates which are always enclosed in parenthesis.
View 14 Replies
View Related
Jan 2, 2013
Is there a way in this program to create a list of dates between 2 dates?
i.e I have Arrival Date and Departure Date. Is there a function or expression that will list all the dates on and between?
View 2 Replies
View Related
Jul 25, 2006
I have two columns, one is number needed and the other is number stocked. How can I get the number stocked column to subrtract from the number needed column. I need to have it done in query. Those are also the title of the columns. Thanks.
View 1 Replies
View Related
Mar 17, 2008
I have a client that wants to enter a range of dates in a query of when they will call that person back. Then they want to be able to type in a range of dates and have a make table query show them all the people that fall in between these two dates....is this even possible???
Ex.
Joe March 3 to March 8
Mary March 4 to March 9
John March 5 to March 10
So if they type into the query March 3 to March 6 all three people should show up because one of the dates specified lies within the parameters they are asking for.....man I am out of ideas
Anyone.....
View 5 Replies
View Related
May 2, 2005
Hi
I want to add Hours to a date value.
For example the date value=05/04/2005 18:12:35
I want to add three hours to that date value so the new date value will be 05/04/2005 21:12:35. Is there operatior to add dates.
View 2 Replies
View Related
Nov 11, 2005
I want to search a table for records between two dates.
My code is like that
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim str As String
Dim Sdate As String
Dim Edate As String
Sdate = Me.txtBeginningDate
Edate = Me.txtEndingDate
Set db = CurrentDb()
str = "SELECT ID, Date, Item, QtyRec, QtyIssue FROM DailyIssue Where Date >= '" & SDate & "' AND DATE <='" & Edate &"'"
Set rs = db.OpenRecordset(str, dbOpenSnapshot)
But it is not working at all. Error message is Data Type Mismatch.
Any help is appreciated in advance.
rahulgty
View 6 Replies
View Related
Dec 4, 2005
i've been reading about the us/uk date problem and found some helpful threads such as http://www.access-programmers.co.uk/forums/showthread.php?t=39675 but would like to ask (cause i'm still a bit confused):
if someone enters all of the dates into an .mdb the same way, either day-month-year or month-day-year, will the dates somehow be stored correctly regardless of the system's setting? (in this case, entered day-month-year into u.s. system-settings).
i have seen how access flips "wrong" dates. if i understand correctly, the dates are then actually stored "flipped", or wrongly.
is there some way of making the wrong dates (the ones that have been flipped) right again?
also, how does one view the dbl-precision number that is stored?
View 1 Replies
View Related
Mar 1, 2007
I capture last change date on a form using now().
When I export the file my vendor wants the date to be a text field and 8 bytes in length. It has to fill 8 bytes like this - YYYYMMDD
Example in my form - 3/1/2007 8:11:55 AM
I need to convert this to text - 20070301 when I export my file.
thanks!
View 13 Replies
View Related
