Suggestion for Calculating data 2

[ptrifile]

I have a spreadsheet that holds 4 fields. date, time, action and ID#. I will do my best to try and explain my need. I am willing to do this in excel with VBA or in access as well, just not sure the best approach.

I am trying to get the total time elapsed for each ID# entry. Each ID# entry may have multiple actions as well as duplicate actions. I know when an entry starts as the action will contain the word "start" and I know when it ends as the action will have the word "end" in it. There may be actions in between those but i am not concerned about those as I am only looking for total time elapsed. Each action is in its own row. So I simply need to calculate the time it took from started to end for each ID#.

Can someone suggest either excel or Access and give some suggestions on how to accomplish this?

Thank you,

Paul

 

[ptrifile]

Andy, that is absolutely correct and what I am looking for.

Paul

 

[combo]

If in access, why not simply in one query select min. time for user with Action filter 'Evolvenewstart', in the second do the same for max and "Evolve - End' Action, join queries for ID# and user and calculate duration (if joining users in necessary - doubled IDs among users)?

Any more compliations in data structure?

As I mentioned, the same can be done with excel queries if initial table is already in excel.

combo

 

[dhookom]

I would try this SQL:

SELECT tblEvolve_Triggers.Date_Time, 
Format([Date_Time]-
  (SELECT TOP 1 Date_Time
   FROM tblEvolve_Triggers T 
   WHERE T.[ID#] = tblEvolve_Triggers.[ID#] AND t.Action = "EvolveNewStart" AND  T.Date_Time < tblEvolve_triggers.Date_time ORDER BY t.Date_Time),
   "Short Time") AS Duration, 
(SELECT TOP 1 Date_Time
 FROM tblEvolve_Triggers T
 WHERE T.[ID#] = tblEvolve_Triggers.[ID#] AND t.Action = "EvolveNewStart" AND  T.Date_Time < tblEvolve_triggers.Date_time ORDER BY t.Date_Time) AS StartTime, 
tblEvolve_Triggers.Action, tblEvolve_Triggers.[ID#], tblEvolve_Triggers.UserName
FROM tblEvolve_Triggers
WHERE (((tblEvolve_Triggers.Action)="Evolve - End"));

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[ptrifile]

Combo, thank you for the suggestion, I will give that a try as well.

Duane, thank you as well. When I ran the query you provided I am getting multiple instances of an ID number for the same user. I can only assume that is because there can be multiple "end" times as well? Is there a way to modify this so that it is only showing one ID number per user using only the first "start time" and last "end" time?

I hope the above made sense.

I cannot thank you enough for your help here.

Paul

 

[dhookom]

Try my solution with the sample data you provided. It worked for me. If it doesn’t work with more data then you need to provide a better sampling of data.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SkipVought]

I am getting multiple instances of an ID number for the same user

Please post the data in your table for that ID number.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

there can be multiple "end" times as well

That's a new one....
Like Duane said: "you need to provide a better sampling of data. "
We cannot help you if we need to guess what data you have.

If you would do this from the beginning:
[ul]
[li]This is what I have[/li]
[li]This is what I've tried[/li]
[li]This is what I need as the outcome[/li]
[/ul]
This issue would be solved a long time ago.


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Well said

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016