Help Needed for a Novice.

[LutonBarry]

Well I've tried and failed using Excel but I wonder if any of you on the forum can help with the query creation to crack this problem.

On the attached spreadsheet I need to calculate elapsed times between certain activities. They are in Column C and are labelled HARDWARE-XX-02 and HARDWARE-XX-04. So it is a calculation of 04 minus 02. We have similar for other calls which are HARDWARE-UK-03 and HARDWARE-UK-05, so 05 minus 03. These activities may or may not exist or perhaps exist in multiples within the same ticket, the ticket ref is in Col A.

The principles of the start of the time measure are:
The Start date/time in Col E of the earliest if multiples exist of HARDWARE-XX-02 or HARDWARE-UK-03.
If those activities do not exist the Start Date/Time in Col D of the first line of the Ticket ID (Col A).

The principles of the Closing time are:
The Resolve date/time in Col E of the earliest if multiples exist of HARDWARE-XX-04 or HARDWARE-UK-05.
If those activities do not exist the Resolve Date/Time in Col E of the last line of the Ticket ID (Col A).

I'd like to result to look something like the example on the spreadsheet on line 41.

I think it is fairly straight forward but I cannot fathom out how to deal with the error situation if one or both activities do not exist and I have to tell Access to return the Earliest Start Date/Time or the oldest Resolve/Date time. It might be a iif statement or similar but the syntax at the moment is beyond me.

[SamT]

Thread: Formula Conumdrum Is Excel up to it (http://www.vbaexpress.com/forum/showthread.php?54260-Formula-Conumdrum-Is-Excel-up-to-it)Thread: Help with an If and OR Statement (http://www.vbaexpress.com/forum/showthread.php?54001-Help-with-an-If-and-OR-Statement)
Thread: Nested Statement Help for Time Calculation. (http://www.vbaexpress.com/forum/showthread.php?53828-Nested-Statement-Help-for-Time-Calculation)

[LutonBarry]

Sam Trouble is while the excel formula works if the Name Activities are present. I cannot select the earliest Start Date and Time or the oldest Resolved Date/Time if one or both of the Activity Types are not present in the Overall ticket data. It is that I need some help with. The syntax if Activity HARDWARE-XX-02" is not present how do I tell Access to look for the earliest Start Date and Time.

Hope you can advise. I would upload my Access file but for some reason it says it is invalid.

[SamT]

how do I tell Access to look for the earliest Start Date and Time.
The problem is that you have been describing a Spreadsheet by using "Col B," "Col C," etc.

You need to describe the Access Table by using Field names.


Can you use a Query that only returned the start and end times per ticket?


Ticket Id

Activity Type Id
Start Datetime

Resolved Datetime
SLA Time
Fix Time


1
HARDWARE-XX-02
30/06/2015 09:26:25
12/07/2015 09:20:00




2
HARDWARE-XX-02
27/08/2015 13:48:09
23/10/2015 10:07:00




3
LEVEL1
27/08/2015 14:39:27
18/09/2015 11:36:00




4
LEVEL1
31/08/2015 05:56:41
21/09/2015 09:46:00




5
Level 1
02/09/2015 11:30:33
21/09/2015 15:22:00




6
HARDWARE-UK-01
15/04/2015 15:00:04
17/09/2015 15:09:00





I'm sure that one of our Access experts can do that.

[LutonBarry]

Sam, Thanks for your time here. I've uploaded a simplified spreadsheet file on which I've highligthed in Yellow the DateTimes that I would like to be applicable within the criteria.

The principles of the start of the time measure are:
The Resolve Datetime of the preceding Activity of the earliest if multiples exist, of HARDWARE-XX-02 or HARDWARE-UK-03. See Ticket ID 1 on Spreadsheet.
If HARDWARE-XX-02 or HARDWARE-UK-03 do not exist the Start Datetime of the first Activity Type ID of the Ticket ID. See Ticket ID 3 on spreadsheet.

The principles of the Closing time are:
The Resolve Datetime of the earliest if multiples exist of HARDWARE-XX-04 or HARDWARE-UK-05. See Ticket ID 1 on spreadsheet
If those activities do not exist the Resolve Datetime of the last Activity Type ID of the Ticket ID. See Tickets ID's 2 and 3 on the spreadsheet.

I can't seem to upload my Access file where I've attempted a shot at it. But needless to say I've not got anywhere near cracking it.

[SamT]

Sam, Thanks for your time here. I've uploaded a simplified spreadsheet file on which I've highligthed in Yellow the DateTimes that I would like to be applicable within the criteria.

The principles of the start of the time measure are:
The Resolve Datetime of the preceding Activity of the earliest if multiples exist, of HARDWARE-XX-02 or HARDWARE-UK-03. See Ticket ID 1 on Spreadsheet.
If HARDWARE-XX-02 or HARDWARE-UK-03 do not exist the Start Datetime of the first Activity Type ID of the Ticket ID. See Ticket ID 3 on spreadsheet.

The principles of the Closing time are:
The Resolve Datetime of the earliest if multiples exist of HARDWARE-XX-04 or HARDWARE-UK-05. See Ticket ID 1 on spreadsheet
If those activities do not exist the Resolve Datetime of the last Activity Type ID of the Ticket ID. See Tickets ID's 2 and 3 on the spreadsheet.
you have said that several times and the previous file you uploaded showed the same thing. We pay attention to details like that.

Why don't you pay attention in return?

[LutonBarry]

The problem is that you have been describing a Spreadsheet by using "Col B," "Col C," etc.

You need to describe the Access Table by using Field names.


Can you use a Query that only returned the start and end times per ticket?


Ticket Id

Activity Type Id

Start Datetime

Resolved Datetime

SLA Time

Fix Time



1

HARDWARE-XX-02

30/06/2015 09:26:25

12/07/2015 09:20:00





2

HARDWARE-XX-02

27/08/2015 13:48:09

23/10/2015 10:07:00





3

LEVEL1

27/08/2015 14:39:27

18/09/2015 11:36:00





4

LEVEL1

31/08/2015 05:56:41

21/09/2015 09:46:00





5

Level 1

02/09/2015 11:30:33

21/09/2015 15:22:00





6

HARDWARE-UK-01

15/04/2015 15:00:04

17/09/2015 15:09:00






I'm sure that one of our Access experts can do that.

Sam that would be an improvement on the current data I'm getting. I'm going to have a look today to see if I can get there. Not being too used to Access I have it in mind to try several queries then query those queries which might get me what I require. I'm struggling with handling the exception if one of the activity ID's I look for is missing.

Apologise for the earlier repeat, tired and my grandaughter had been taken ill.

[SamT]

In 30 words or less, you need an Access Query that :
With each Ticket ID (FieldName), for each Activity Type ID (FieldName), return the earliest Start DateTime (FieldName) and latest Resolved DateTime (FieldName)

Now I am going to lock this thread and you are going to open a new thread with the Title "Need Help With Query"

You will phrase your question similar to what I said in the first para, but using the actual Access Table Field Names instead of the Excel Column Labels you have been using.

You will also add a ling to reference this thread so everyone can see all the background to the question.

Hopefully, we can quickly solve your problem and put this whole string of threads to rest.