Find Start and End Times

  • Thread starter Thread starter gcutter
  • Start date Start date
G

gcutter

I have a web page form that Employees enter when they enter a Weld Booth and
when they leave a Weld Booth. I need to get:
Emp# Booth Stat Timestamp ID
608 Booth10 INBooth 10/21/09 11:24 831
608 Booth11 OutofBooth 10/21/09 11:23 830
483 Booth08 OutofBooth 10/21/09 11:23 829
608 Booth11 INBooth 10/21/09 7:51 828
608 Booth12 OutofBooth 10/21/09 7:51 827
608 Booth12 INBooth 10/21/09 7:50 826
414 Booth05 INBooth 10/21/09 7:50 825
483 Booth08 INBooth 10/21/09 7:50 824
413 Booth13 INBooth 10/21/09 7:49 823
300 Booth01 INBooth 10/21/09 7:49 822
413 Booth09 OutofBooth 10/21/09 7:03 821
483 Booth07 OutofBooth 10/21/09 7:02 820
300 Booth04 OutofBooth 10/21/09 7:02 819
300 Booth04 INBooth 10/21/09 6:58 818
300 Booth01 OutofBooth 10/21/09 6:58 817
413 Booth09 INBooth 10/21/09 6:58 816
483 Booth07 INBooth 10/21/09 6:57 815
300 Booth01 INBooth 10/21/09 6:57 814

Into the format below using a query:
Emp# Booth Start End
300 Booth01 10/21/09 6:57 10/21/09 6:58
300 Booth04 10/21/09 6:58 10/21/09 7:02
300 Booth01 10/21/09 7:49 10/21/09 15:15
483 Booth07 10/21/09 6:57 10/21/09 7:02
413 Booth09 10/21/09 6:58 10/21/09 7:03
413 Booth13 10/21/09 7:49 10/21/09 12:58
413 Booth13 10/21/09 12:58 10/21/09 15:15
483 Booth08 10/21/09 7:50 10/21/09 11:23
483 Booth11 10/21/09 11:24 10/21/09 15:15
414 Booth05 10/21/09 7:50 10/21/09 13:30
414 Booth09 10/21/09 13:30 10/21/09 15:15
608 Booth12 10/21/09 7:50 10/21/09 7:51
608 Booth11 10/21/09 7:51 10/21/09 11:23
608 Booth10 10/21/09 11:24 10/21/09 15:15
597 Booth07 10/21/09 11:24 10/21/09 15:15

Any ideas how I do this. I haven't worked with a date time and getting the
ending times usually the databases I worked with have Start and End times in
the same record.

Thanks,
Greg
 
Where do you get the 10/21/09 15:15 times when there are no records with it?
Is this just some sort of default? What happens on other shifts or when
overtime is authorized?
 
This work but I am getting error message on save 'Record is deleted' --
SELECT gcutter.[Emp#], gcutter.Booth, gcutter.Timestamp AS Start,
gcutter_1.Timestamp AS [End],
DateDiff("n",[gcutter].[Timestamp],[gcutter_1].[Timestamp])\60 & ":" &
Format(DateDiff("n",[gcutter].[Timestamp],[gcutter_1].[Timestamp])-DateDiff("n",[gcutter].[Timestamp],[gcutter_1].[Timestamp])\60,"00") AS Booth_Times
FROM gcutter INNER JOIN gcutter AS gcutter_1 ON (gcutter.[Emp#] =
gcutter_1.[Emp#]) AND (gcutter.Booth = gcutter_1.Booth)
WHERE (((gcutter.Stat)="INBooth") AND ((gcutter_1.Stat)="OutofBooth") AND
((gcutter_1.ID)>[gcutter].[ID]));
 
I got based on what you gave me Karl. Thanks turns out it is fairly easy.

Thanks,
Greg

KARL DEWEY said:
This work but I am getting error message on save 'Record is deleted' --
SELECT gcutter.[Emp#], gcutter.Booth, gcutter.Timestamp AS Start,
gcutter_1.Timestamp AS [End],
DateDiff("n",[gcutter].[Timestamp],[gcutter_1].[Timestamp])\60 & ":" &
Format(DateDiff("n",[gcutter].[Timestamp],[gcutter_1].[Timestamp])-DateDiff("n",[gcutter].[Timestamp],[gcutter_1].[Timestamp])\60,"00") AS Booth_Times
FROM gcutter INNER JOIN gcutter AS gcutter_1 ON (gcutter.[Emp#] =
gcutter_1.[Emp#]) AND (gcutter.Booth = gcutter_1.Booth)
WHERE (((gcutter.Stat)="INBooth") AND ((gcutter_1.Stat)="OutofBooth") AND
((gcutter_1.ID)>[gcutter].[ID]));

--
Build a little, test a little.


gcutter said:
I have a web page form that Employees enter when they enter a Weld Booth and
when they leave a Weld Booth. I need to get:
Emp# Booth Stat Timestamp ID
608 Booth10 INBooth 10/21/09 11:24 831
608 Booth11 OutofBooth 10/21/09 11:23 830
483 Booth08 OutofBooth 10/21/09 11:23 829
608 Booth11 INBooth 10/21/09 7:51 828
608 Booth12 OutofBooth 10/21/09 7:51 827
608 Booth12 INBooth 10/21/09 7:50 826
414 Booth05 INBooth 10/21/09 7:50 825
483 Booth08 INBooth 10/21/09 7:50 824
413 Booth13 INBooth 10/21/09 7:49 823
300 Booth01 INBooth 10/21/09 7:49 822
413 Booth09 OutofBooth 10/21/09 7:03 821
483 Booth07 OutofBooth 10/21/09 7:02 820
300 Booth04 OutofBooth 10/21/09 7:02 819
300 Booth04 INBooth 10/21/09 6:58 818
300 Booth01 OutofBooth 10/21/09 6:58 817
413 Booth09 INBooth 10/21/09 6:58 816
483 Booth07 INBooth 10/21/09 6:57 815
300 Booth01 INBooth 10/21/09 6:57 814

Into the format below using a query:
Emp# Booth Start End
300 Booth01 10/21/09 6:57 10/21/09 6:58
300 Booth04 10/21/09 6:58 10/21/09 7:02
300 Booth01 10/21/09 7:49 10/21/09 15:15
483 Booth07 10/21/09 6:57 10/21/09 7:02
413 Booth09 10/21/09 6:58 10/21/09 7:03
413 Booth13 10/21/09 7:49 10/21/09 12:58
413 Booth13 10/21/09 12:58 10/21/09 15:15
483 Booth08 10/21/09 7:50 10/21/09 11:23
483 Booth11 10/21/09 11:24 10/21/09 15:15
414 Booth05 10/21/09 7:50 10/21/09 13:30
414 Booth09 10/21/09 13:30 10/21/09 15:15
608 Booth12 10/21/09 7:50 10/21/09 7:51
608 Booth11 10/21/09 7:51 10/21/09 11:23
608 Booth10 10/21/09 11:24 10/21/09 15:15
597 Booth07 10/21/09 11:24 10/21/09 15:15

Any ideas how I do this. I haven't worked with a date time and getting the
ending times usually the databases I worked with have Start and End times in
the same record.

Thanks,
Greg
Click to expand...
Click to expand...
 
Back
Top