473,544 Members | 1,024 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Time diff between records

Hi all,
I have a query (query1) which shows scan date, scan time & operator. One
scan = 1 record. What I want to do is create a report based on query 2 from
query1 which shows all the scans AND the difference between each scan.
Something like below. I have spent days on this and can't figure it out.

Date Time Login Diff
04-Dec-03 23:33:12 27478
04-Dec-03 23:33:38 27478 00:00:26
04-Dec-03 23:34:00 27478 00:00:22
04-Dec-03 23:34:21 27478 00:00:21
04-Dec-03 23:35:11 27478 00:00:50

Thanks in advance

Mark
Nov 12 '05 #1
4 4220
Use a subquery to get the most recent login date and time for the same login
number.

That's a matter of typing something like this into the Field row of your
query to create the calculated field:

Seconds: DateDiff("s", (MyTable.Date + MyTable.Time),
(SELECT TOP 1 (Dupe.Date + Dupe.Time) AS PriorTime
FROM MyTable AS Dupe
WHERE ((Dupe.Login = MyTable.Login) AND ((Dupe.Date + Dupe.Time) <
(Mytable.Date + MyTable.Time)))
ORDER BY Dupe.Date DESC; Dupe.Time DESC ) )

This would be considerably more efficient if the date and time were stored
in the one field. Hopefully your fields are not actually called Date and
Time as theser are reserved words in VBA.

Consider adding the primary key to the ORDER BY clause as well, so Access
can distinguish between 2 records that have exactly the same date and time.

If you want the time as a string instead of a number of seconds, see:
http://members.rogers.com/douglas.j....iff2Dates.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark Reed" <ma*********@nt lworld.com> wrote in message
news:hN******** ****@newsfep3-gui.server.ntli .net...
Hi all,
I have a query (query1) which shows scan date, scan time & operator. One scan = 1 record. What I want to do is create a report based on query 2 from query1 which shows all the scans AND the difference between each scan.
Something like below. I have spent days on this and can't figure it out.

Date Time Login Diff
04-Dec-03 23:33:12 27478
04-Dec-03 23:33:38 27478 00:00:26
04-Dec-03 23:34:00 27478 00:00:22
04-Dec-03 23:34:21 27478 00:00:21
04-Dec-03 23:35:11 27478 00:00:50

Nov 12 '05 #2
Thanks for that Allen,
I've tried what you have suggest but I think I am doing something wrong.
I'll give you a little more background to my problem as I think that may be
the cause.

The database gets it's inputs from a txt file recovered from the works
management system when I run a SQL within it. Towards the end of shift,
there could be anything up to 18,000 entries as the WMS SQL asks for every
single scan during a set shift. The results of that SQL are then pasted into
a single field of my Access database.
Example of txt result is:

04-DEC-2003 22:21:09 Chowdry, Halim 06313 988558 -6 N5608D CCS
LOCA 1 H Picking 14310 725997 Conv
Conv -6 04.12.03H 3

04-DEC-2003 22:22:16 Phillips, Lillian 46517H 988567 -1 N6909A CCS
LOCA 1 H Picking 14362 681311 Conv
Conv -1 04.12.03H 3

My first query ([decode1]) breaks the txt file into relevant fields. The
second ([decode2]) then changes the some of the fields into values and time
rather than just text.

SELECT DISTINCT DateValue([decode].[Date]) AS actdate,
TimeValue([Decode]![Time]) AS acttime, [Name query].[First Name], [Name
query].Surname, Decode.Operator , Decode.[Pic Route], Val([Decode]![QTY]) AS
Qty, Val([Decode]![IP Qty]) AS [IP Qty], Decode.Location , Decode.[To
Location], Decode.Cartons, Decode.PT, Decode.Job, Val([Decode]![RDT]) AS
RDT, Val([Decode]![Trolley]) AS Trolley, Decode.Fromst, Decode.Tost,
Decode.Schedule , Decode.Wv
FROM [Name query] INNER JOIN Decode ON [Name query].Login = Decode.Operator
GROUP BY DateValue([decode].[Date]), TimeValue([Decode]![Time]), [Name
query].[First Name], [Name query].Surname, Decode.Operator , Decode.[Pic
Route], Val([Decode]![QTY]), Val([Decode]![IP Qty]), Decode.Location ,
Decode.[To Location], Decode.Cartons, Decode.PT, Decode.Job,
Val([Decode]![RDT]), Val([Decode]![Trolley]), Decode.Fromst, Decode.Tost,
Decode.Schedule , Decode.Wv
WITH OWNERACCESS OPTION;

All of the other querys in the DB are based on the decode2 query.

How would you write the query, given the above information to show the time
difference between each record?

I really appreciate your help with this.

Regards,
Mark

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:40******** **************@ freenews.iinet. net.au...
Use a subquery to get the most recent login date and time for the same login number.

That's a matter of typing something like this into the Field row of your
query to create the calculated field:

Seconds: DateDiff("s", (MyTable.Date + MyTable.Time),
(SELECT TOP 1 (Dupe.Date + Dupe.Time) AS PriorTime
FROM MyTable AS Dupe
WHERE ((Dupe.Login = MyTable.Login) AND ((Dupe.Date + Dupe.Time) <
(Mytable.Date + MyTable.Time)))
ORDER BY Dupe.Date DESC; Dupe.Time DESC ) )

This would be considerably more efficient if the date and time were stored
in the one field. Hopefully your fields are not actually called Date and
Time as theser are reserved words in VBA.

Consider adding the primary key to the ORDER BY clause as well, so Access
can distinguish between 2 records that have exactly the same date and time.
If you want the time as a string instead of a number of seconds, see:
http://members.rogers.com/douglas.j....iff2Dates.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark Reed" <ma*********@nt lworld.com> wrote in message
news:hN******** ****@newsfep3-gui.server.ntli .net...
Hi all,
I have a query (query1) which shows scan date, scan time & operator.

One
scan = 1 record. What I want to do is create a report based on query 2

from
query1 which shows all the scans AND the difference between each scan.
Something like below. I have spent days on this and can't figure it out.

Date Time Login Diff
04-Dec-03 23:33:12 27478
04-Dec-03 23:33:38 27478 00:00:26
04-Dec-03 23:34:00 27478 00:00:22
04-Dec-03 23:34:21 27478 00:00:21
04-Dec-03 23:35:11 27478 00:00:50


Nov 12 '05 #3
Hi Mark.

It makes sense to import the text file into an Access table.
Combine the date and time into one Date/Time field, and index it.
Then the subquery should be able to find the correct prior date/time value
from the index (instead of having to scan the 18k records), so it should
perform acceptably.

For more info on how to build the subquery, see:
How to Compare a Field to a Field in a Prior Record
at:

http://support.microsoft.com/default...roduct=acc2000

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark Reed" <ma*********@nt lworld.com> wrote in message
news:jX******** ******@newsfep3-gui.server.ntli .net...
Thanks for that Allen,
I've tried what you have suggest but I think I am doing something wrong. I'll give you a little more background to my problem as I think that may be the cause.

The database gets it's inputs from a txt file recovered from the works
management system when I run a SQL within it. Towards the end of shift,
there could be anything up to 18,000 entries as the WMS SQL asks for every
single scan during a set shift. The results of that SQL are then pasted into a single field of my Access database.
Example of txt result is:

04-DEC-2003 22:21:09 Chowdry, Halim 06313 988558 -6 N5608D CCS
LOCA 1 H Picking 14310 725997 Conv
Conv -6 04.12.03H 3

04-DEC-2003 22:22:16 Phillips, Lillian 46517H 988567 -1 N6909A CCS
LOCA 1 H Picking 14362 681311 Conv
Conv -1 04.12.03H 3

My first query ([decode1]) breaks the txt file into relevant fields. The
second ([decode2]) then changes the some of the fields into values and time rather than just text.

SELECT DISTINCT DateValue([decode].[Date]) AS actdate,
TimeValue([Decode]![Time]) AS acttime, [Name query].[First Name], [Name
query].Surname, Decode.Operator , Decode.[Pic Route], Val([Decode]![QTY]) AS Qty, Val([Decode]![IP Qty]) AS [IP Qty], Decode.Location , Decode.[To
Location], Decode.Cartons, Decode.PT, Decode.Job, Val([Decode]![RDT]) AS
RDT, Val([Decode]![Trolley]) AS Trolley, Decode.Fromst, Decode.Tost,
Decode.Schedule , Decode.Wv
FROM [Name query] INNER JOIN Decode ON [Name query].Login = Decode.Operator GROUP BY DateValue([decode].[Date]), TimeValue([Decode]![Time]), [Name
query].[First Name], [Name query].Surname, Decode.Operator , Decode.[Pic
Route], Val([Decode]![QTY]), Val([Decode]![IP Qty]), Decode.Location ,
Decode.[To Location], Decode.Cartons, Decode.PT, Decode.Job,
Val([Decode]![RDT]), Val([Decode]![Trolley]), Decode.Fromst, Decode.Tost,
Decode.Schedule , Decode.Wv
WITH OWNERACCESS OPTION;

All of the other querys in the DB are based on the decode2 query.

How would you write the query, given the above information to show the time difference between each record?

I really appreciate your help with this.

Regards,
Mark

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:40******** **************@ freenews.iinet. net.au...
Use a subquery to get the most recent login date and time for the same

login
number.

That's a matter of typing something like this into the Field row of your
query to create the calculated field:

Seconds: DateDiff("s", (MyTable.Date + MyTable.Time),
(SELECT TOP 1 (Dupe.Date + Dupe.Time) AS PriorTime
FROM MyTable AS Dupe
WHERE ((Dupe.Login = MyTable.Login) AND ((Dupe.Date + Dupe.Time) <
(Mytable.Date + MyTable.Time)))
ORDER BY Dupe.Date DESC; Dupe.Time DESC ) )

This would be considerably more efficient if the date and time were stored in the one field. Hopefully your fields are not actually called Date and
Time as theser are reserved words in VBA.

Consider adding the primary key to the ORDER BY clause as well, so Access can distinguish between 2 records that have exactly the same date and

time.

If you want the time as a string instead of a number of seconds, see:
http://members.rogers.com/douglas.j....iff2Dates.html
"Mark Reed" <ma*********@nt lworld.com> wrote in message
news:hN******** ****@newsfep3-gui.server.ntli .net...
Hi all,
I have a query (query1) which shows scan date, scan time & operator.
One
scan = 1 record. What I want to do is create a report based on query 2

from
query1 which shows all the scans AND the difference between each scan.
Something like below. I have spent days on this and can't figure it

out.
Date Time Login Diff
04-Dec-03 23:33:12 27478
04-Dec-03 23:33:38 27478 00:00:26
04-Dec-03 23:34:00 27478 00:00:22
04-Dec-03 23:34:21 27478 00:00:21
04-Dec-03 23:35:11 27478 00:00:50

Nov 12 '05 #4
Fantastic!!!! Thankyou very much for your help.

Mark
"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:40******** **************@ freenews.iinet. net.au...
Hi Mark.

It makes sense to import the text file into an Access table.
Combine the date and time into one Date/Time field, and index it.
Then the subquery should be able to find the correct prior date/time value
from the index (instead of having to scan the 18k records), so it should
perform acceptably.

For more info on how to build the subquery, see:
How to Compare a Field to a Field in a Prior Record
at:

http://support.microsoft.com/default...roduct=acc2000
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark Reed" <ma*********@nt lworld.com> wrote in message
news:jX******** ******@newsfep3-gui.server.ntli .net...
Thanks for that Allen,
I've tried what you have suggest but I think I am doing something

wrong.
I'll give you a little more background to my problem as I think that may

be
the cause.

The database gets it's inputs from a txt file recovered from the works
management system when I run a SQL within it. Towards the end of shift,
there could be anything up to 18,000 entries as the WMS SQL asks for every
single scan during a set shift. The results of that SQL are then pasted

into
a single field of my Access database.
Example of txt result is:

04-DEC-2003 22:21:09 Chowdry, Halim 06313 988558 -6 N5608D CCS LOCA 1 H Picking 14310 725997 Conv
Conv -6 04.12.03H 3

04-DEC-2003 22:22:16 Phillips, Lillian 46517H 988567 -1 N6909A CCS LOCA 1 H Picking 14362 681311 Conv
Conv -1 04.12.03H 3

My first query ([decode1]) breaks the txt file into relevant fields. The
second ([decode2]) then changes the some of the fields into values and

time
rather than just text.

SELECT DISTINCT DateValue([decode].[Date]) AS actdate,
TimeValue([Decode]![Time]) AS acttime, [Name query].[First Name], [Name
query].Surname, Decode.Operator , Decode.[Pic Route], Val([Decode]![QTY])

AS
Qty, Val([Decode]![IP Qty]) AS [IP Qty], Decode.Location , Decode.[To
Location], Decode.Cartons, Decode.PT, Decode.Job, Val([Decode]![RDT]) AS
RDT, Val([Decode]![Trolley]) AS Trolley, Decode.Fromst, Decode.Tost,
Decode.Schedule , Decode.Wv
FROM [Name query] INNER JOIN Decode ON [Name query].Login =

Decode.Operator
GROUP BY DateValue([decode].[Date]), TimeValue([Decode]![Time]), [Name
query].[First Name], [Name query].Surname, Decode.Operator , Decode.[Pic
Route], Val([Decode]![QTY]), Val([Decode]![IP Qty]), Decode.Location ,
Decode.[To Location], Decode.Cartons, Decode.PT, Decode.Job,
Val([Decode]![RDT]), Val([Decode]![Trolley]), Decode.Fromst, Decode.Tost, Decode.Schedule , Decode.Wv
WITH OWNERACCESS OPTION;

All of the other querys in the DB are based on the decode2 query.

How would you write the query, given the above information to show the

time
difference between each record?

I really appreciate your help with this.

Regards,
Mark

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:40******** **************@ freenews.iinet. net.au...
Use a subquery to get the most recent login date and time for the same

login
number.

That's a matter of typing something like this into the Field row of your query to create the calculated field:

Seconds: DateDiff("s", (MyTable.Date + MyTable.Time),
(SELECT TOP 1 (Dupe.Date + Dupe.Time) AS PriorTime
FROM MyTable AS Dupe
WHERE ((Dupe.Login = MyTable.Login) AND ((Dupe.Date + Dupe.Time) <
(Mytable.Date + MyTable.Time)))
ORDER BY Dupe.Date DESC; Dupe.Time DESC ) )

This would be considerably more efficient if the date and time were

stored in the one field. Hopefully your fields are not actually called Date and Time as theser are reserved words in VBA.

Consider adding the primary key to the ORDER BY clause as well, so Access can distinguish between 2 records that have exactly the same date and

time.

If you want the time as a string instead of a number of seconds, see:
http://members.rogers.com/douglas.j....iff2Dates.html
"Mark Reed" <ma*********@nt lworld.com> wrote in message
news:hN******** ****@newsfep3-gui.server.ntli .net...
> Hi all,
> I have a query (query1) which shows scan date, scan time & operator. One
> scan = 1 record. What I want to do is create a report based on query 2 from
> query1 which shows all the scans AND the difference between each scan. > Something like below. I have spent days on this and can't figure it out. >
> Date Time Login Diff
> 04-Dec-03 23:33:12 27478
> 04-Dec-03 23:33:38 27478 00:00:26
> 04-Dec-03 23:34:00 27478 00:00:22
> 04-Dec-03 23:34:21 27478 00:00:21
> 04-Dec-03 23:35:11 27478 00:00:50


Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
12320
by: NotGiven | last post by:
Below is a good elapsed time function I found. However, I'd like to return total seconds instead of broken down into days, hours, minutes & seconds. In other words, I want "125" instead of "2 minutes 5 seconds". Any ideas? Thanks very much! function calcElapsedTime($time) { // calculate elapsed time (in seconds!) $diff =...
6
4645
by: Stefan Behnel | last post by:
Hi! The logging module nicely prepends each line with a formatted date. However, I'm not interested in the actual date but only in the number of milliseconds that passed since the start of the program. What is the best way of doing that? Thanks, Stefan
2
3695
by: Alberto Santini | last post by:
I ported a Jos Stam's demo about Fluid mech to check the difference of speed between C implementation and Python. I think I achieved good results with Python and there is space to improve, without to extend the program with C routine, I mean. -- Good hack, Alberto Santini (http://www.albertosantini.it/)
3
1803
by: Chanchito | last post by:
hi there, I am seeking some guidance in regards to creating a query. I would like to be able to have the query display records that have had a certain amount of time pass since the time that is listed a particular field of the record. There is a field labeld DispTime. This field is updated with the current time whenever data is typed into...
9
2864
by: MLH | last post by:
I have a database (datatrek.mdb) with a table named DATA. The table has a date/time field with default value = Now(). It has 100 records in it entered over a 50-minute period. I would like the query to display 100 records with a new, calculated field showing timelapse between time of record entry of current record and time of entry of...
2
2745
by: Viviana R via AccessMonster.com | last post by:
I'm tryin to calculate de difference of time between different records and fields. I have a Report with date, time In, and Time Out fields. EX: Date Time IN Time Out 4/12/05 12:10 PM 12:40 PM 4/12/05 12:50 PM 1:20 PM 4/12/05 1:15 PM 1:45 PM
3
14878
by: Richard | last post by:
Hi, Is there any way to get the time difference between Central(US) and GMT in vb.net. I'll appreciate your help/suggestion. Thanks RC
6
2422
by: Jeremy Sanders | last post by:
Hi - I need to add support to a program for dates and times. The built-in Python library seems to be okay for many purposes, but what I would like would be Unix epoch style times (seconds relative to some date), covering a large period from the past to the future. What would be nice would be a library which can take floating point seconds from...
9
8284
by: Ron Adam | last post by:
I'm having some cross platform issues with timing loops. It seems time.time is better for some computers/platforms and time.clock others, but it's not always clear which, so I came up with the following to try to determine which. import time # Determine if time.time is better than time.clock # The one with better resolution should be...
0
7365
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7607
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7772
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7709
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5909
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
4918
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3415
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1841
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
661
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.