If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
time and colums
I have one table with 3 columns and would like to subtract same day time form
one another. (example - time clock) My columns are as follows: barcode, Qty, date_time. I understand how to use the "Datediff" expression with 2 columns but as you see, I only have one that is used for the date and time. Ex: 5776 1 1/26/2010 07:30:00 AM 5776 1 1/26/2010 03:30:00 pm Whats the best way to handle this? Thank you! Tom |
#2
|
|||
|
|||
time and colums
Tom
For Access to be able to subtract a field in one record from a field in another record, it needs to know which records. How do YOU know which records? -- Regards Jeff Boyce Microsoft Access MVP Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Tom" wrote in message ... I have one table with 3 columns and would like to subtract same day time form one another. (example - time clock) My columns are as follows: barcode, Qty, date_time. I understand how to use the "Datediff" expression with 2 columns but as you see, I only have one that is used for the date and time. Ex: 5776 1 1/26/2010 07:30:00 AM 5776 1 1/26/2010 03:30:00 pm Whats the best way to handle this? Thank you! Tom |
#3
|
|||
|
|||
time and colums
Use a query column for the DateDiff function. You'll need a source for the
second date/time. You can use the Now() function to get the current time from the computer clock. You can get the time from another field in the database where you can join the tables. You can calculate a field from a known point, like yesterday at 6:00 AM. Or you can use an external source via a serial or USB cable. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Tom" wrote in message ... I have one table with 3 columns and would like to subtract same day time form one another. (example - time clock) My columns are as follows: barcode, Qty, date_time. I understand how to use the "Datediff" expression with 2 columns but as you see, I only have one that is used for the date and time. Ex: 5776 1 1/26/2010 07:30:00 AM 5776 1 1/26/2010 03:30:00 pm Whats the best way to handle this? Thank you! Tom |
#4
|
|||
|
|||
time and colums
Tom:
Are you saying you want to subtract the latest previous date/time value from the current one to get the elapsed time? If so the following function returns the elapsed time between two data time values in the format hh:nn:ss, with the option of also showing days if the hours returned might be 24 or more Public Function TimeDuration( _ varFrom As Variant, _ varTo As Variant, _ Optional blnShowDays As Boolean = False) Const HOURSINDAY = 24 Dim lngHours As Long Dim strMinutesSeconds As String Dim strDaysHours As String Dim dblDuration As Double If Not IsNull(varFrom) And Not IsNull(varTo) Then dblDuration = varTo - varFrom 'get number of hours lngHours = Int(dblDuration) * HOURSINDAY + _ Format(dblDuration, "h") ' get minutes and seconds strMinutesSeconds = Format(dblDuration, ":nn:ss") If blnShowDays Then 'get days and hours strDaysHours = lngHours \ HOURSINDAY & _ " day" & IIf(lngHours \ HOURSINDAY 1, "s ", " ") & _ lngHours Mod HOURSINDAY TimeDuration = strDaysHours & strMinutesSeconds Else TimeDuration = lngHours & strMinutesSeconds End If End If End Function Call it in query with a subquery to return the latest prior date time value as its first argument, like so: SELECT barcode, Qty, date_time, TimeDuration( (SELECT MAX(date_time) FROM YourTable As T2 WHERE T2.date_time T1.date_time), date_time) As ElapsedTime FROM YourTable As T1 ORDER BY date_time DESC; If you want the elapsed times per barcode correlate the subquery on this column also: SELECT barcode, Qty, date_time, TimeDuration( (SELECT MAX(date_time) FROM YourTable As T2 WHERE T2.barcode = T1.barcode AND T2.date_time T1.date_time), date_time) As ElapsedTime FROM YourTable As T1 ORDER BY barcode, date_time DESC; If you want the elapsed time in a single time unit, e.g. minutes, you can of course use the DateDiff function in the same way as the above function. Ken Sheridan Stafford, England Tom wrote: I have one table with 3 columns and would like to subtract same day time form one another. (example - time clock) My columns are as follows: barcode, Qty, date_time. I understand how to use the "Datediff" expression with 2 columns but as you see, I only have one that is used for the date and time. Ex: 5776 1 1/26/2010 07:30:00 AM 5776 1 1/26/2010 03:30:00 pm Whats the best way to handle this? Thank you! Tom -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
time and colums
Ken,
Your solution seems to be right on the money! But I am new to sql and am having problems putting the code in the proper places to make it work. I did get it to work one time then i received an error of " Circular refference called by "tbltbl" ( the name of the stored input data from scanner) After reading the orignal expression, I made a duplicate table named "yourtable" Now I get an error of "Undefined function 'timeduration' in expression Any idea's? Thanks to all who have helped! Tom "KenSheridan via AccessMonster.com" wrote: Tom: Are you saying you want to subtract the latest previous date/time value from the current one to get the elapsed time? If so the following function returns the elapsed time between two data time values in the format hh:nn:ss, with the option of also showing days if the hours returned might be 24 or more Public Function TimeDuration( _ varFrom As Variant, _ varTo As Variant, _ Optional blnShowDays As Boolean = False) Const HOURSINDAY = 24 Dim lngHours As Long Dim strMinutesSeconds As String Dim strDaysHours As String Dim dblDuration As Double If Not IsNull(varFrom) And Not IsNull(varTo) Then dblDuration = varTo - varFrom 'get number of hours lngHours = Int(dblDuration) * HOURSINDAY + _ Format(dblDuration, "h") ' get minutes and seconds strMinutesSeconds = Format(dblDuration, ":nn:ss") If blnShowDays Then 'get days and hours strDaysHours = lngHours \ HOURSINDAY & _ " day" & IIf(lngHours \ HOURSINDAY 1, "s ", " ") & _ lngHours Mod HOURSINDAY TimeDuration = strDaysHours & strMinutesSeconds Else TimeDuration = lngHours & strMinutesSeconds End If End If End Function Call it in query with a subquery to return the latest prior date time value as its first argument, like so: SELECT barcode, Qty, date_time, TimeDuration( (SELECT MAX(date_time) FROM YourTable As T2 WHERE T2.date_time T1.date_time), date_time) As ElapsedTime FROM YourTable As T1 ORDER BY date_time DESC; If you want the elapsed times per barcode correlate the subquery on this column also: SELECT barcode, Qty, date_time, TimeDuration( (SELECT MAX(date_time) FROM YourTable As T2 WHERE T2.barcode = T1.barcode AND T2.date_time T1.date_time), date_time) As ElapsedTime FROM YourTable As T1 ORDER BY barcode, date_time DESC; If you want the elapsed time in a single time unit, e.g. minutes, you can of course use the DateDiff function in the same way as the above function. Ken Sheridan Stafford, England Tom wrote: I have one table with 3 columns and would like to subtract same day time form one another. (example - time clock) My columns are as follows: barcode, Qty, date_time. I understand how to use the "Datediff" expression with 2 columns but as you see, I only have one that is used for the date and time. Ex: 5776 1 1/26/2010 07:30:00 AM 5776 1 1/26/2010 03:30:00 pm Whats the best way to handle this? Thank you! Tom -- Message posted via http://www.accessmonster.com . |
#6
|
|||
|
|||
time and colums
Tom:
Firstly paste the function into any standard module. If it’s a new module save it under a different name from the function, e.g. mdlDateTimeStuff. Then open the query designer and switch to SQL view. Paste in the SQL statement in place of whatever is there and change the two instances of 'YourTable' to whatever is the real table name. Assuming the column names are exactly as you posted them you shouldn't need to change anything else. If it still doesn't recognize the function, which can sometimes happen when a function is pasted in, change the name of the function to something different like TimeElapsed. You'll need to change it in each place it occurs in the function's code, and in the SQL statement of the query. Ken Sheridan Stafford, England Tom wrote: Ken, Your solution seems to be right on the money! But I am new to sql and am having problems putting the code in the proper places to make it work. I did get it to work one time then i received an error of " Circular refference called by "tbltbl" ( the name of the stored input data from scanner) After reading the orignal expression, I made a duplicate table named "yourtable" Now I get an error of "Undefined function 'timeduration' in expression Any idea's? Thanks to all who have helped! Tom Tom: [quoted text clipped - 83 lines] Tom -- Message posted via http://www.accessmonster.com |
#7
|
|||
|
|||
time and colums
Ken,
Thank You for the fast reply! your solution was a bulls eye! renaming the module to "mdDateTimesStuff" was the problem. Thank You so very much "KenSheridan via AccessMonster.com" wrote: Tom: Firstly paste the function into any standard module. If it’s a new module save it under a different name from the function, e.g. mdlDateTimeStuff. Then open the query designer and switch to SQL view. Paste in the SQL statement in place of whatever is there and change the two instances of 'YourTable' to whatever is the real table name. Assuming the column names are exactly as you posted them you shouldn't need to change anything else. If it still doesn't recognize the function, which can sometimes happen when a function is pasted in, change the name of the function to something different like TimeElapsed. You'll need to change it in each place it occurs in the function's code, and in the SQL statement of the query. Ken Sheridan Stafford, England Tom wrote: Ken, Your solution seems to be right on the money! But I am new to sql and am having problems putting the code in the proper places to make it work. I did get it to work one time then i received an error of " Circular refference called by "tbltbl" ( the name of the stored input data from scanner) After reading the orignal expression, I made a duplicate table named "yourtable" Now I get an error of "Undefined function 'timeduration' in expression Any idea's? Thanks to all who have helped! Tom Tom: [quoted text clipped - 83 lines] Tom -- Message posted via http://www.accessmonster.com . |
#8
|
|||
|
|||
time and colums
How could I eliminate the "day" factor and not show any results over a 24
hour time span? I am calculating snow removal job times as well as my employee start and end time on a "as needed" basis that would never span past an 8 hour day Thank you very much!!! Tom |
#9
|
|||
|
|||
time and colums
Tom:
Are you saying ignore any results where the difference between the times is more than 8 hours so as not to return those where the query would be subtracting the previous end time from a start time? If so try this: SELECT barcode, Qty, date_time, TimeDuration( (SELECT MAX(date_time) FROM YourTable As T2 WHERE T2.barcode = T1.barcode AND T2.date_time T1.date_time), date_time) As ElapsedTime FROM YourTable As T1 WHERE DateDiff("h", (SELECT MAX(date_time) FROM YourTable As T3 WHERE T3.barcode = T1.barcode AND T3.date_time T1.date_time), date_time)) = 8 ORDER BY barcode, date_time DESC; You might want to increase the restriction from 8 to something higher to allow some leeway. The value needs to be greater than the maximum worked time for a shift, and less than the minimum time between an employee ending one shift and starting the next. If I've misunderstood perhaps you could give an example of some rows and what you'd want returned. Ken Sheridan Stafford, England Tom wrote: How could I eliminate the "day" factor and not show any results over a 24 hour time span? I am calculating snow removal job times as well as my employee start and end time on a "as needed" basis that would never span past an 8 hour day Thank you very much!!! Tom -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201002/1 |
#10
|
|||
|
|||
time and colums
Ken, You are dead on! thank you so very much. I was trying to wright the queries using the datediff but couldn't figure out how or where to place it in the sql without errors. May I donate to your cause? Thanks again Tom |
|
Thread Tools | |
Display Modes | |
|
|