By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

Time diff between records

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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*********@ntlworld.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

P: n/a
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*********@SeeSig.Invalid> wrote in message
news:40**********************@freenews.iinet.net.a u...
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*********@ntlworld.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

P: n/a
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*********@ntlworld.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*********@SeeSig.Invalid> wrote in message
news:40**********************@freenews.iinet.net.a u...
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*********@ntlworld.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

P: n/a
Fantastic!!!! Thankyou very much for your help.

Mark
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40**********************@freenews.iinet.net.a u...
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*********@ntlworld.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*********@SeeSig.Invalid> wrote in message
news:40**********************@freenews.iinet.net.a u...
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*********@ntlworld.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 discussion thread is closed

Replies have been disabled for this discussion.