Calculate the time difference where a number of users are involved

[JohnLee]

Good day Experts,

I hope I've put this in the right place. I require to calculate the time difference where a number of people have been involved in the process, the time difference being that of the first person and that of the last person in the process.

I have a query which has the following fields:

strManufacturer
strScanBatchNo
strSplitBatchNo
dtmDateIn
dtmTimeIn
dtmTimeOut
strUserID
strFormCount
dtmTimeTaken
strThroughput

I have the following three records, whereby users have accessed the same batch on the same date at different times in and out, with a form count of 100.

The form count whilst shows 100 against each UserID is in fact the total for all the UserID's collectively.

here's the data for each UserID:

strManufacturer HOTPOINT HOTPOINT HOTPOINT
strScanBatchNo 00000008 000000008 000000008
strSplitBatchNo 001 001 001
dtmDateIn 13/11/2008 13/11/2008 13/11/2008
dtmTimeIn 07:25:11 09:25:13 09:39:53
dtmTimeOut 08:40:10 09:39:53 10:15:43
strUserID carterk hirsts bloxhac
strFormCount 100 100 100
dtmTimeTaken 01:14:59 00:14:40 00:35:50
strThroughput 80 400 167

The Question

Can a query be created that calculates the difference between the dtmTimeIn for the first person in the batch of work and the dtmTimeOut for the last person out of the batch?

i.e. the difference in time between 07:25:11 and 10:15:43 which would be 02:50:32.

Your assistance would be most appreciated.

John Lee

 

[Singh400]

Yes it can.

TimeDiff: [dtmTimeOut] - [dtmTimeIn]

Although you might need to tell it to select the first and last values in each respective record.

 

[JohnLee]

Hi,

Thanks for your response, But you have mis-understood my question. I already have that calculation for each record, as can be seen from the dtmTimeTaken field, what I need to find out, is how can I only get the time in for the first userID and the time out for the last user ID, ignoring all the other time in's and out's for all other userID's, and weather it can be done in a query.

Thanks in advance.

John

 

[DCrake]

First time = left(dtmTimeIn,8)
Last Time = right(dtmTimeOut,8)

However what happens if it takes longer than one day?

 

[JohnLee]

Hi,

Thanks for your response and observation. I had already thought about if it took longer than a day and I have worked out what I would need to do in respect of that, but for the most part data shouldn't be in the system more than a day, but it is possible, by the same token, data can be in the system over two days but not in the system more than one day, so again I have put in place a method of dealing with that.

I will try your solution out and let you know, as this was were I was getting stuck.

Thanks once again.

John

 

[JohnLee]

Hi,

I tried your suggestion, however, what I got was the exact same time in every case!

I created a new field in my query and called it "First Time" as follows:

First Time: Left([dtmTimeIn,8)

The result I got was the exact same time in the dtmTimeIn field for each and every user. The same happend when I created a new field called "Last Time"

Last Time: Right([dtmTimeOut],8)

So I'm no further forward, any suggestions would be most appreciated.

John

 

[DCrake]

Are dtmTimeIn dtmTimeOut actual field names?

Can you post the sample table?

Have attached a sample of what you arer asking for

 

[JohnLee]

Hi,

Thanks for your response. Yes they are fields in the table. please find attached an exmaple db containing both the table and the query.

If you look at the query, you will see that I have restricted it to a specific date and you will see 3 records with the same scanned batch No but with different Time In's and Time Out's. I need create a query where there are records of this nature that will give me only the first time in and the last time out covering all users who have been involved in processing that particular batch.

Your assistance is most appreciated. I am using Access 2000

John

 

[DCrake]

Ok
You need to create a new query with the following sql

SELECT strScanBatchNo, Min(dtmTimeIn) AS FirstTimeIn, Max(dtmTimeOut) AS LastTimeOut
FROM tblFlowStats2
WHERE (((dtmCreationDate)=#11/14/2008#))
GROUP BY strScanBatchNo;

Then design you original query and add the above query to the top pane and create a join between the strScanBatchNo in both the table and the query.

Drop in the FirstTimeIn and LastTimeOut into the grid and remove the dtmTimeIn and dtmTimeOut.

 

[JohnLee]

Hi again,

I have tried out your SQL and it works fine, but the problem I have now is that the "WHERE" claus restricts it to a specific date, which is an americanised date. What do I need to do to change it to cater for any date and for it to be in UK date format.

Thanks once again

John

 

[Brianwarnock]

If you want all dates leave out the Where clause, if you want to prompt for a date and don't use a form then WHERE (((dtmCreationDate)=[Enter Date])) will provoke a prompt, I don't use prompts so not sure if that still requires # # around it, hm after have a play.

Brian

Edit no it doesn't

 

[JohnLee]

Hi Brian,

I've worked it out and got it working how I need it to.

Thanks once again

John