Time difference in Query

cl159037

Registered User.
Local time
Tomorrow, 02:49
Joined
Nov 22, 2011
Messages
13
i want to resolve difference in time in query when the autonumbers are not in order. i have attached the word file with details. please go thru the attached word file and give me the solution.
 

Attachments

Please clarify 'not in order' your example shows they are in order, just there are some missing ones - is this what you mean?
 
dear CJ_london... tnks 4 ur reply....
You have meant it correctly itz the missing records. Before reaching any conclusion please note that my order sequence is based upon start_time. I would appreciate if i can have an early solution to my problem.
 
You could use SQL to write a solution however this would be very slow.

Suggest that you write a UDF in code. (User Designed Function)

The attached may be of some help.

Not Tested.
 

Attachments

A SQL solution would be

Code:
 SELECT myTable.*, myTable.startTime-T.endTime as TimeDiff
 FROM myTable INNER JOIN myTable AS T ON myTable.Location=T.Location
 WHERE T.StartTime=(SELECT MAX(StartTime) FROM myTable as Tmp WHERE StartTime<myTable.StartTime And Location=myTable.Location)
 ORDER BY myTable.StartTime

substitute myTable with the name of your table
 
CJ London

Have you tried to test this to see if your code works.

159037

In your attached you have end time - end time.

value would be End_time of present record minus End_time of present record.

Is this what you really want.
 
@Rainlover. I actually reinterpreted the OP's requirement - I thought he was looking difference where the start time 'predates' the previous end time.

however the sql finds the previous record (aliased as T) so the difference calculation is easily changed

But agree, this does not make sense
value would be End_time of present record minus End_time of present record
 
I would appreciate if i can have an early solution to my problem.
Yeah - I work half the night to come up with a solution - and then - nothing
 
sorry all.
due to my hectic work commitment i didnt have time to go through your suggestion. I will try your suggestion/ codes the moment i m free. plz hold down a while...
 
Dear CJ_London
I tried your sql code but couldn't achieve the desired results. In order to have a better view of my problem i have attached an excel file that gives me the desired result. Please check the formula in the excel file and try to achieve the same using access file.
tnks in advance.
 

Attachments

due to my hectic work commitment
I also have a hectic work commitment so with regards
I tried your sql code but couldn't achieve the desired results
refer to this
however the sql finds the previous record (aliased as T) so the difference calculation is easily changed
and change this
SELECT myTable.*, myTable.startTime-T.endTime as TimeDiff
 
sorry all.
due to my hectic work commitment i didnt have time to go through your suggestion. I will try your suggestion/ codes the moment i m free. plz hold down a while...

Seriously,

I think that if you are going to ask someone to give up their time for you then give them the respect they deserve.

First of all use proper "English" not that stuff children use on their phones.

Acknowledge some ones attempted help ASAP.

I gave you a sample and I doubt that you have given it a second thought.

For something that requires urgent help I think you have missed the mark.

PS How was the party on the week end.
 
This attachment in Excel

Do you think you could make some notes against each line explaining what the formula is doing.

In fact write a proper formula in Excel so we can understand.
 
Come on guys lighten up, yes the poster is very remiss to say the need is urgent and then not spend the time to reply ASAP and to test the answers immediately even if it means working all hours. The tone of his posts suggests that he is young or English is not his first language.

What his requirement is is obvious in his first post not withstanding his written error so let's not make mountains out of mole hills.

Why haven't I replied ? ,because I no longer have Access to test any air code I might be foolish enough to try, but CJ seems to have it covered if the poster would make the effort to understand it.

Brian
 
hey guys...
my case is exactly what Brianwarnock as stated. I solemnly apologize for the language i have used. Now i would like to explain my situation as below.

i have a query as shown below.
SrNo startTime endTime
1 31/01/2015 01:00 31/01/2015 02:00 (a)
3 31/01/2015 01:45 (b) 31/01/2015 03:00 (c)
4 31/01/2015 03:15 31/01/2015 04:15

now the formula stands as below

=if(a>b, c-a ,c-b)
if a>b, "then the result must be" c-a and if a<b "then the result must be" c-b)
Please also note that SrNo 2 is missing from the table because it is a filtered query.

Guys i am aware that you all are taking the pains to reach to a conclusion and i appreciate it. Now my request is not to be in a hurry and burden yourself. please try and work out the solution to my query as and when your priority work is fulfilled.
thanks & Regards
CL159037
 
Come on guys lighten up, yes the poster is very remiss to say the need is urgent and then not spend the time to reply ASAP and to test the answers immediately even if it means working all hours. The tone of his posts suggests that he is young or English is not his first language.

What his requirement is is obvious in his first post not withstanding his written error so let's not make mountains out of mole hills.

Why haven't I replied ? ,because I no longer have Access to test any air code I might be foolish enough to try, but CJ seems to have it covered if the poster would make the effort to understand it.

Brian

His requirements are far from neither obvious nor easy. The first problem is that we have to look at the previous record. In this situation we have no previous record. This causes an error.

Access is not a toy for young boys and girls to play with after school and it can be expensive. People need to know that while we have some fun here this is serious business.

I will help anyone if I can and at no cost. While others stop work at the end of each day's pay period, and never give it a second thought until the next day.

As long as I am not Mean, Rude or Uncalled. I believe I can have certain expectations. Baby text talk and a 9 to 5 attitude simply do not wash. If you are going to be unable to get back because of other commitments then that is fine. Just let everyone know because there is also someone eles out there who has been waiting for weeks for me to help with his project.

Is the OP truly upset with anything I wrote or is it just Brian. Let them speak up so as not to nurture any bad feelings.

To the OP “Cl159037”. If I have offended, I do apologise.

Brian,
Would this not be better as a PM.
 
Last edited:
Just a quick post before I go out for the day.
I wanted the poster to know that his behaviour was not what was expected, that is why I would not PM you and CJ which in my opinion would have meant that I would have been severely criticising you, which I wasn't doing.

I never said his problem was easy , only that what he wanted was obvious.

Sorry if I have offended anybody.

Brian
 
Brian

With your charm and good looks you could never offend.

Where are you going?
 
please try and work out the solution to my query as and when your priority work is fulfilled
Your required solution has already been provided.

refer to this
Quote:
however the sql finds the previous record (aliased as T) so the difference calculation is easily changed
and change this

Quote:
SELECT myTable.*, myTable.startTime-T.endTime as TimeDiff
all you have to do is substitute the bit in red with your formula

iif(a>b, c-a ,c-b) (iif is the access equivalent of excel if)
and replace the a, b and c with the appropriate mytable/T (current/previous) and starttime/endtime

I will now drop off this thread since you have been advised a number of times of the solution and seem unprepared to think for yourself or even try the solutions provided.
 

Users who are viewing this thread

Back
Top Bottom