Query help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am wanting to calculate time in Query 1 I need the [Stop time] and [Start
time] difference in Employee table. In Query 2 I need the [Time] in the T1
Standard Time table, and the differenct of [Stop time] and [Start time] in
the Employee table .

Query1 Stop Time – Start Time = X
Query 2 (Stop Time –Start Time = X) – T1 Standard Time = X
 
The following expression will return the number of minutes since the new
year:

DateDiff("n", #1/1/2005 0:00:00am#, now() )

q1: Select EmpID, DateDiff("n", StartTime, StopTime-) as X FROM tblStuff
q2: Select EmpID, X - [T1 Standard Time] as Y From q1

I don't understand the [T1 Standard Time], so you may need to put it into a
DateDiff() as well.
 
That link worked great on the stop, start time! But unfortunally I still
need help with the [start time - stop time] - [T1 Standard. Time]
The start and stop time are in the Employee Table and the other Time is in
the T1 Standard table.
Is there any way I can just move over next to the TIME: DateDiff("n",[START
TIME],[STOP TIME]) and put the other time?

Allen Browne said:
See:
Calculating elapsed time
at:
http://members.iinet.net.au/~allenbrowne/casu-13.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Donna said:
I am wanting to calculate time in Query 1 I need the [Stop time] and [Start
time] difference in Employee table. In Query 2 I need the [Time] in the
T1
Standard Time table, and the differenct of [Stop time] and [Start time] in
the Employee table .

Query1 Stop Time - Start Time = X
Query 2 (Stop Time -Start Time = X) - T1 Standard Time = X
Click to expand...
Click to expand...
 
No that did not work. Let me explain

Employee Time Sheet Table: T1 Standard
Time Table:
[Work Cell]
[Time]
T1 Codes T1
Code: 
Start Time
Stop Time

I need in Query 1:
Fields:   [Work Cell]       [Code]       [Time]      TOTAL TIME:
DateDiff("n",[START TIME],[STOP TIME],?????????)

were do you put the [Time] in the function?

[QUOTE="[MVP] S.Clark"]
The following expression will return the number of minutes since the new
year:

DateDiff("n",  #1/1/2005 0:00:00am#, now() )

q1: Select EmpID, DateDiff("n", StartTime, StopTime-) as X FROM tblStuff
q2: Select EmpID, X - [T1 Standard Time] as Y From q1

I don't understand the [T1 Standard Time], so you may need to put it into a
DateDiff() as well.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

[QUOTE="Donna"]
I am wanting to calculate time in Query 1 I need the [Stop time] and [Start
time] difference in Employee table.   In Query 2 I need the [Time] in the
T1
Standard Time table, and the differenct of [Stop time] and [Start time] in
the Employee table .

Query1   Stop Time - Start Time = X
Query 2  (Stop Time -Start Time = X) - T1 Standard Time = X[/QUOTE]
[/QUOTE]
 
Sorry, Donna, I don't understand about the [T1 Standard].[Time].

Is it a date/time field? A Number Field?

Does it represent the normal number of seconds you expect a task to take, so
you are trying to calculate the difference from the expected amount of time?
If so and it is a date/time, Steve Clark's answer should work. If it's a
Number field, you should be able to subtract it from the result of the first
calculation that you now have working.

Does it represent a timezone, where you have people starting work in one
time zone, and ending their day in a different time zone (e.g. after flying
east)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Donna said:
That link worked great on the stop, start time! But unfortunally I still
need help with the [start time - stop time] - [T1 Standard. Time]
The start and stop time are in the Employee Table and the other Time is in
the T1 Standard table.
Is there any way I can just move over next to the TIME:
DateDiff("n",[START
TIME],[STOP TIME]) and put the other time?

Allen Browne said:
See:
Calculating elapsed time
at:
http://members.iinet.net.au/~allenbrowne/casu-13.html


Donna said:
I am wanting to calculate time in Query 1 I need the [Stop time] and
[Start
time] difference in Employee table. In Query 2 I need the [Time] in
the
T1
Standard Time table, and the differenct of [Stop time] and [Start time]
in
the Employee table .

Query1 Stop Time - Start Time = X
Query 2 (Stop Time -Start Time = X) - T1 Standard Time = X
Click to expand...
Click to expand...
Click to expand...
 
Back
Top