I need to Integrate or Segregate based on time lapsed

[TraciAnnNeedsHelp]

I use a query to calculate a minimum service time for customers using the
following expression:

Hours: IIf([DurationHours]<1.5 And [DurationHours]>0,1.5,[DurationHours])

On occasion the service technician visits a location multiple times in a
day. So, for a given day there may be three records as such:

Date StartD/T EndD/T
3/6/09 9:00:00 9:30:00
3/6/09 10:00:00 10:15:00
3/6/09 14:00:00 15:00:00

Using this scenario, since the time between the EndD/T of the first record
and the StartD/T of the second record is less than 2 hours, I need to SUM the
durations from both records and apply the minimum, if the minimum isn't met
by the total.

Since the StartD/T of the third record is greater than 2 hours from the
previous record, then I need to count that as a separate service record,
applying the minimum if necessary.

The output is only in the query. I do not want to update existing data.

Thank you for your help!
TraciAnn

 

[Jeff Boyce]

I think I'm missing something...

Aren't your records indicating how much time was spent on the service call?
Aren't your records in your example indicating that the tech spent 1.5 hours
on the first call, .25 hours on the second, and 1.0 hours for the third?

It sounds like you are saying that the duration is the difference BETWEEN
records, not the difference from [Start] to [End].

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[TraciAnnNeedsHelp]

We have a minimum service duration for some customers. So, if customer A has
a minimum service call of 1.5 hours, we respond to a service call and spend
..25, that automatically adjusts to 1.5 hours.

On occassion, the service tech breaks from the service call to handle
something unrelated to the customer she is servicing. Using the example
below, the svc.tech spent half an hour on the "ticket" but then spent half an
hour on the phone with his boss. When he returned to servicing the customer
it took another 15 minutes to finish the job.

The third entry, because of the time laps, is obviously a re-visit (customer
needed svc.tech to come back out).

Because there are 3 time entries on the ticket that are less than the
minimum, the system is designed to apply the minimum to each time entry (1.5
hours).

Without exception, anytime the lapse between two time entries on the same
ticket is less than 2 hours, they should be counted as a single visit.
Therefore, for the first two time entries, they should be summed (NOT from
the beginning of the first entry to the end of the second entry) and then
evaluated against the minimum. If the sum is less than the minimum, then the
minimum (1.5) should be charged just once for both time entries.

Because the last time entry is over 2 hours since the end of the previous
time entry. It would count as a separate visit. It would be evaluated against
the "minimum" rule separately.

I hope this makes it clearer.
Thanks!

I think I'm missing something...

Aren't your records indicating how much time was spent on the service call?
Aren't your records in your example indicating that the tech spent 1.5 hours
on the first call, .25 hours on the second, and 1.0 hours for the third?

It sounds like you are saying that the duration is the difference BETWEEN
records, not the difference from [Start] to [End].

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I use a query to calculate a minimum service time for customers using the
following expression:

Hours: IIf([DurationHours]<1.5 And [DurationHours]>0,1.5,[DurationHours])

On occasion the service technician visits a location multiple times in a
day. So, for a given day there may be three records as such:

Date StartD/T EndD/T
3/6/09 9:00:00 9:30:00
3/6/09 10:00:00 10:15:00
3/6/09 14:00:00 15:00:00

Using this scenario, since the time between the EndD/T of the first record
and the StartD/T of the second record is less than 2 hours, I need to SUM
the
durations from both records and apply the minimum, if the minimum isn't
met
by the total.

Since the StartD/T of the third record is greater than 2 hours from the
previous record, then I need to count that as a separate service record,
applying the minimum if necessary.

The output is only in the query. I do not want to update existing data.

Thank you for your help!
TraciAnn

 

[Jeff Boyce]

Sorry, I'm more confused, not less...<g>

I get the concept of 'minimum service duration'. So what is it that the
record is measuring with the [Start...] and [End...] fields?

And how, from the example data you provided, can you (or I) tell that all
three Start/End records are for the same customer? I only saw three records
on the same date.

(still) More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

We have a minimum service duration for some customers. So, if customer A
has
a minimum service call of 1.5 hours, we respond to a service call and
spend
.25, that automatically adjusts to 1.5 hours.

On occassion, the service tech breaks from the service call to handle
something unrelated to the customer she is servicing. Using the example
below, the svc.tech spent half an hour on the "ticket" but then spent half
an
hour on the phone with his boss. When he returned to servicing the
customer
it took another 15 minutes to finish the job.

The third entry, because of the time laps, is obviously a re-visit
(customer
needed svc.tech to come back out).

Because there are 3 time entries on the ticket that are less than the
minimum, the system is designed to apply the minimum to each time entry
(1.5
hours).

Without exception, anytime the lapse between two time entries on the same
ticket is less than 2 hours, they should be counted as a single visit.
Therefore, for the first two time entries, they should be summed (NOT from
the beginning of the first entry to the end of the second entry) and then
evaluated against the minimum. If the sum is less than the minimum, then
the
minimum (1.5) should be charged just once for both time entries.

Because the last time entry is over 2 hours since the end of the previous
time entry. It would count as a separate visit. It would be evaluated
against
the "minimum" rule separately.

I hope this makes it clearer.
Thanks!

I think I'm missing something...

Aren't your records indicating how much time was spent on the service
call?
Aren't your records in your example indicating that the tech spent 1.5
hours
on the first call, .25 hours on the second, and 1.0 hours for the third?

It sounds like you are saying that the duration is the difference BETWEEN
records, not the difference from [Start] to [End].

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

in
message news:[email protected]...

I use a query to calculate a minimum service time for customers using
the
following expression:

Hours: IIf([DurationHours]<1.5 And
[DurationHours]>0,1.5,[DurationHours])

On occasion the service technician visits a location multiple times in
a
day. So, for a given day there may be three records as such:

Date StartD/T EndD/T
3/6/09 9:00:00 9:30:00
3/6/09 10:00:00 10:15:00
3/6/09 14:00:00 15:00:00

Using this scenario, since the time between the EndD/T of the first
record
and the StartD/T of the second record is less than 2 hours, I need to
SUM
the
durations from both records and apply the minimum, if the minimum isn't
met
by the total.

Since the StartD/T of the third record is greater than 2 hours from the
previous record, then I need to count that as a separate service
record,
applying the minimum if necessary.

The output is only in the query. I do not want to update existing data.

Thank you for your help!
TraciAnn

 

[TraciAnnNeedsHelp]

I'm sorry Jeff. I'm trying....

Thanks!
TraciAnn

Sorry, I'm more confused, not less...<g>

I get the concept of 'minimum service duration'. So what is it that the
record is measuring with the [Start...] and [End...] fields?

This is a SQL database that records Service Tickets: I can only query the
tables I cannot update the tables from Access.

The records in my sample are showing the TimeEntries records for one SvcTech
for a single customer. What doesn't appear in the sample are the other
TimeEntries records on that day for that SvcTech (or any other svc techs).

The Start/End fields are actual times spent on that ticket for that customer.

Tickets can be broad in scope which is why there may be multiple service
calls against any given Ticket (as opposed to creating a new ticket for each
service call).

And how, from the example data you provided, can you (or I) tell that all
three Start/End records are for the same customer? I only saw three records
on the same date.

The TimeEntries table is linked to the Tickets table which is linked to the
Customer table. (Actual names not used to avoid confusion, the SQL developer
used names that makes it pretty ambiguous).

Customer.CustomerID is a foreign key in Tickets
Tickets.TicketID is a foreign key in TimeEntries

(still) More info, please...

I hope this makes it clearer.
TraciAnn

We have a minimum service duration for some customers. So, if customer A
has
a minimum service call of 1.5 hours, we respond to a service call and
spend
.25, that automatically adjusts to 1.5 hours.

On occassion, the service tech breaks from the service call to handle
something unrelated to the customer she is servicing. Using the example
below, the svc.tech spent half an hour on the "ticket" but then spent half
an
hour on the phone with his boss. When he returned to servicing the
customer
it took another 15 minutes to finish the job.

The third entry, because of the time laps, is obviously a re-visit
(customer
needed svc.tech to come back out).

Because there are 3 time entries on the ticket that are less than the
minimum, the system is designed to apply the minimum to each time entry
(1.5
hours).

Without exception, anytime the lapse between two time entries on the same
ticket is less than 2 hours, they should be counted as a single visit.
Therefore, for the first two time entries, they should be summed (NOT from
the beginning of the first entry to the end of the second entry) and then
evaluated against the minimum. If the sum is less than the minimum, then
the
minimum (1.5) should be charged just once for both time entries.

Because the last time entry is over 2 hours since the end of the previous
time entry. It would count as a separate visit. It would be evaluated
against
the "minimum" rule separately.

I hope this makes it clearer.
Thanks!

I think I'm missing something...

Aren't your records indicating how much time was spent on the service
call?
Aren't your records in your example indicating that the tech spent 1.5
hours
on the first call, .25 hours on the second, and 1.0 hours for the third?

It sounds like you are saying that the duration is the difference BETWEEN
records, not the difference from [Start] to [End].

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

in
message I use a query to calculate a minimum service time for customers using
the
following expression:

Hours: IIf([DurationHours]<1.5 And
[DurationHours]>0,1.5,[DurationHours])

On occasion the service technician visits a location multiple times in
a
day. So, for a given day there may be three records as such:

Date StartD/T EndD/T
3/6/09 9:00:00 9:30:00
3/6/09 10:00:00 10:15:00
3/6/09 14:00:00 15:00:00

Using this scenario, since the time between the EndD/T of the first
record
and the StartD/T of the second record is less than 2 hours, I need to
SUM
the
durations from both records and apply the minimum, if the minimum isn't
met
by the total.

Since the StartD/T of the third record is greater than 2 hours from the
previous record, then I need to count that as a separate service
record,
applying the minimum if necessary.

The output is only in the query. I do not want to update existing data.

Thank you for your help!
TraciAnn

 

[Jeff Boyce]

No need to apologize, we're both learning how...<g>

If there's a chance you can post back with the table structure, that might
help me visualize. For example, if we were talking about student
enrollments, the table structure might look like (greatly simplified):

tblPerson
PersonID
FirstName
LastName
DOB

tblClass
ClassID
ClassTitle

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate

Can you describe your tables in a similar manner?

Thanks

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm sorry Jeff. I'm trying....

Thanks!
TraciAnn

Sorry, I'm more confused, not less...<g>

I get the concept of 'minimum service duration'. So what is it that the
record is measuring with the [Start...] and [End...] fields?

This is a SQL database that records Service Tickets: I can only query the
tables I cannot update the tables from Access.

The records in my sample are showing the TimeEntries records for one
SvcTech
for a single customer. What doesn't appear in the sample are the other
TimeEntries records on that day for that SvcTech (or any other svc techs).

The Start/End fields are actual times spent on that ticket for that
customer.

Tickets can be broad in scope which is why there may be multiple service
calls against any given Ticket (as opposed to creating a new ticket for
each
service call).

And how, from the example data you provided, can you (or I) tell that all
three Start/End records are for the same customer? I only saw three
records
on the same date.

The TimeEntries table is linked to the Tickets table which is linked to
the
Customer table. (Actual names not used to avoid confusion, the SQL
developer
used names that makes it pretty ambiguous).

Customer.CustomerID is a foreign key in Tickets
Tickets.TicketID is a foreign key in TimeEntries

(still) More info, please...

I hope this makes it clearer.
TraciAnn

in
message news[email protected]...

We have a minimum service duration for some customers. So, if customer
A
has
a minimum service call of 1.5 hours, we respond to a service call and
spend
.25, that automatically adjusts to 1.5 hours.

On occassion, the service tech breaks from the service call to handle
something unrelated to the customer she is servicing. Using the example
below, the svc.tech spent half an hour on the "ticket" but then spent
half
an
hour on the phone with his boss. When he returned to servicing the
customer
it took another 15 minutes to finish the job.

The third entry, because of the time laps, is obviously a re-visit
(customer
needed svc.tech to come back out).

Because there are 3 time entries on the ticket that are less than the
minimum, the system is designed to apply the minimum to each time entry
(1.5
hours).

Without exception, anytime the lapse between two time entries on the
same
ticket is less than 2 hours, they should be counted as a single visit.
Therefore, for the first two time entries, they should be summed (NOT
from
the beginning of the first entry to the end of the second entry) and
then
evaluated against the minimum. If the sum is less than the minimum,
then
the
minimum (1.5) should be charged just once for both time entries.

Because the last time entry is over 2 hours since the end of the
previous
time entry. It would count as a separate visit. It would be evaluated
against
the "minimum" rule separately.

I hope this makes it clearer.
Thanks!

:

I think I'm missing something...

Aren't your records indicating how much time was spent on the service
call?
Aren't your records in your example indicating that the tech spent 1.5
hours
on the first call, .25 hours on the second, and 1.0 hours for the
third?

It sounds like you are saying that the duration is the difference
BETWEEN
records, not the difference from [Start] to [End].

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"TraciAnnNeedsHelp" <[email protected]>
wrote
in
message I use a query to calculate a minimum service time for customers using
the
following expression:

Hours: IIf([DurationHours]<1.5 And
[DurationHours]>0,1.5,[DurationHours])

On occasion the service technician visits a location multiple times
in
a
day. So, for a given day there may be three records as such:

Date StartD/T EndD/T
3/6/09 9:00:00 9:30:00
3/6/09 10:00:00 10:15:00
3/6/09 14:00:00 15:00:00

Using this scenario, since the time between the EndD/T of the first
record
and the StartD/T of the second record is less than 2 hours, I need
to
SUM
the
durations from both records and apply the minimum, if the minimum
isn't
met
by the total.

Since the StartD/T of the third record is greater than 2 hours from
the
previous record, then I need to count that as a separate service
record,
applying the minimum if necessary.

The output is only in the query. I do not want to update existing
data.

Thank you for your help!
TraciAnn

 

[TraciAnnNeedsHelp]

Jeff, there are almost a hundred tables and "views" in the SQL database, so I
will try to keep descriptions limited to just the tables and fields that are
applicable.

tblCustomers
CustID - Primary
CustName - Text

tblTechs
TechID - Primary
TechName - Text
BillRate - Currency

tblTickets
TicketID - Primary
CustID - Foreign
Created - Date/Time
Assigned - Foreign (TechID)

tblTimeEntries
EntryID - Primary
TicketID - Foreign
TechID - Foreign
StartDateTime - Date/Time
StopDateTime - Date/Time
Duration - Calculated ([StopDateTime]-[StartDateTime])
Mileage - Number
Expenses - Currency

 

[Jeff Boyce]

First of all, there's one field in your description that stands out as
different from the rest ... the calculated value.

It is quite rare to need to store a calculated value, and deciding to do so
requires that you also create procedures for keeping the calculated value
and its underlying components in sync ... not for the faint-hearted!

Instead, consider using a query to derive (calculate) that value whenever
you need it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[TraciAnnNeedsHelp]

I agree with you and suspected that you would have a comment about that.
However, that is the case with the SQL database. I don't have any control
over it. I'm not sure that it is an actual field in the SQL table or if it is
based on the "View". I'm not familiar with the concept of "SQL Views" but I'm
told there are both in the application.

In my queries I normally do my own calculations on Start and Stop Date/Times
rather than using the Duration from the table. The existence of the field has
not created any issues so far - - all of my date/time calculations have been
correct.

First of all, there's one field in your description that stands out as
different from the rest ... the calculated value.

It is quite rare to need to store a calculated value, and deciding to do so
requires that you also create procedures for keeping the calculated value
and its underlying components in sync ... not for the faint-hearted!

Instead, consider using a query to derive (calculate) that value whenever
you need it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff, there are almost a hundred tables and "views" in the SQL database,
so I
will try to keep descriptions limited to just the tables and fields that
are
applicable.

tblCustomers
CustID - Primary
CustName - Text

tblTechs
TechID - Primary
TechName - Text
BillRate - Currency

tblTickets
TicketID - Primary
CustID - Foreign
Created - Date/Time
Assigned - Foreign (TechID)

tblTimeEntries
EntryID - Primary
TicketID - Foreign
TechID - Foreign
StartDateTime - Date/Time
StopDateTime - Date/Time
Duration - Calculated ([StopDateTime]-[StartDateTime])
Mileage - Number
Expenses - Currency

 

[Jeff Boyce]

Those wild and crazy SQL-Server guys! Aren't they just a riot?!

If you use a query in which you calculate Duration, then use a second query
that uses that calculated (in the first query) Duration as part of an IIF()
statement, I believe you could determine the "billable hours" (for lack of a
better term).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I agree with you and suspected that you would have a comment about that.
However, that is the case with the SQL database. I don't have any control
over it. I'm not sure that it is an actual field in the SQL table or if it
is
based on the "View". I'm not familiar with the concept of "SQL Views" but
I'm
told there are both in the application.

In my queries I normally do my own calculations on Start and Stop
Date/Times
rather than using the Duration from the table. The existence of the field
has
not created any issues so far - - all of my date/time calculations have
been
correct.

First of all, there's one field in your description that stands out as
different from the rest ... the calculated value.

It is quite rare to need to store a calculated value, and deciding to do
so
requires that you also create procedures for keeping the calculated value
and its underlying components in sync ... not for the faint-hearted!

Instead, consider using a query to derive (calculate) that value whenever
you need it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

in
message news:[email protected]...

Jeff, there are almost a hundred tables and "views" in the SQL
database,
so I
will try to keep descriptions limited to just the tables and fields
that
are
applicable.

tblCustomers
CustID - Primary
CustName - Text

tblTechs
TechID - Primary
TechName - Text
BillRate - Currency

tblTickets
TicketID - Primary
CustID - Foreign
Created - Date/Time
Assigned - Foreign (TechID)

tblTimeEntries
EntryID - Primary
TicketID - Foreign
TechID - Foreign
StartDateTime - Date/Time
StopDateTime - Date/Time
Duration - Calculated ([StopDateTime]-[StartDateTime])
Mileage - Number
Expenses - Currency

 

[John Spencer]

RE: MS SQL SERVER calculated fields:

Although there is often no need for a calculated field there are times
when it helps with performance. SQL SERVER has calculated fields and
those fields are automatically calculated when the record is created or
modified. One place I have used it is to create the Soundex code for
names. The performance when I am trying to match on Soundex codes is
much enhanced since the code does not have to be calculated for each
pair of records at execution.

Tradeoff - one small bit of time to calculate the value when the record
is created and modified or lots of bits of time when the value is used
in a where clause or a join or a sort.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I agree with you and suspected that you would have a comment about that.
However, that is the case with the SQL database. I don't have any control
over it. I'm not sure that it is an actual field in the SQL table or if it is
based on the "View". I'm not familiar with the concept of "SQL Views" but I'm
told there are both in the application.

In my queries I normally do my own calculations on Start and Stop Date/Times
rather than using the Duration from the table. The existence of the field has
not created any issues so far - - all of my date/time calculations have been
correct.

First of all, there's one field in your description that stands out as
different from the rest ... the calculated value.

It is quite rare to need to store a calculated value, and deciding to do so
requires that you also create procedures for keeping the calculated value
and its underlying components in sync ... not for the faint-hearted!

Instead, consider using a query to derive (calculate) that value whenever
you need it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff, there are almost a hundred tables and "views" in the SQL database,
so I
will try to keep descriptions limited to just the tables and fields that
are
applicable.

tblCustomers
CustID - Primary
CustName - Text

tblTechs
TechID - Primary
TechName - Text
BillRate - Currency

tblTickets
TicketID - Primary
CustID - Foreign
Created - Date/Time
Assigned - Foreign (TechID)

tblTimeEntries
EntryID - Primary
TicketID - Foreign
TechID - Foreign
StartDateTime - Date/Time
StopDateTime - Date/Time
Duration - Calculated ([StopDateTime]-[StartDateTime])
Mileage - Number
Expenses - Currency

 

[TraciAnnNeedsHelp]

Ok, either you are still working on it or I may be missing the answer here.

My original request:
------------------------------------------------------
I use a query to calculate a minimum service time for customers using the
following expression:

Hours: IIf([DurationHours]<1.5 And [DurationHours]>0,1.5,[DurationHours])

On occasion the service technician visits a location multiple times in a
day. So, for a given day there may be three records as such:

Date StartD/T EndD/T
3/6/09 9:00:00 9:30:00
3/6/09 10:00:00 10:15:00
3/6/09 14:00:00 15:00:00

Using this scenario, since the time between the EndD/T of the first record
and the StartD/T of the second record is less than 2 hours, I need to SUM the
durations from both records and apply the minimum, if the minimum isn't met
by the total.

Since the StartD/T of the third record is greater than 2 hours from the
previous record, then I need to count that as a separate service record,
applying the minimum if necessary.

The output is only in the query. I do not want to update existing data.
------------------------------------------------------
End Original Request

The answer I need would Sum Durations of the first two records and leave the
third as a separate record, prior to running the IIf() statement mentioned
above, which applies minimums to the service tickets.

I assume you are saying use an IIf() statement to combine, but I don't know
the syntax that looks for another service ticket and, if it exists, determine
if the StopDateTime of the previous record is less than 2 hours of the
StartDateTime of the current record and, if so, add the durations together.

Am I understanding everything correctly?

Those wild and crazy SQL-Server guys! Aren't they just a riot?!

If you use a query in which you calculate Duration, then use a second query
that uses that calculated (in the first query) Duration as part of an IIF()
statement, I believe you could determine the "billable hours" (for lack of a
better term).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I agree with you and suspected that you would have a comment about that.
However, that is the case with the SQL database. I don't have any control
over it. I'm not sure that it is an actual field in the SQL table or if it
is
based on the "View". I'm not familiar with the concept of "SQL Views" but
I'm
told there are both in the application.

In my queries I normally do my own calculations on Start and Stop
Date/Times
rather than using the Duration from the table. The existence of the field
has
not created any issues so far - - all of my date/time calculations have
been
correct.

First of all, there's one field in your description that stands out as
different from the rest ... the calculated value.

It is quite rare to need to store a calculated value, and deciding to do
so
requires that you also create procedures for keeping the calculated value
and its underlying components in sync ... not for the faint-hearted!

Instead, consider using a query to derive (calculate) that value whenever
you need it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

in
message Jeff, there are almost a hundred tables and "views" in the SQL
database,
so I
will try to keep descriptions limited to just the tables and fields
that
are
applicable.

tblCustomers
CustID - Primary
CustName - Text

tblTechs
TechID - Primary
TechName - Text
BillRate - Currency

tblTickets
TicketID - Primary
CustID - Foreign
Created - Date/Time
Assigned - Foreign (TechID)

tblTimeEntries
EntryID - Primary
TicketID - Foreign
TechID - Foreign
StartDateTime - Date/Time
StopDateTime - Date/Time
Duration - Calculated ([StopDateTime]-[StartDateTime])
Mileage - Number
Expenses - Currency

 

[Michael Gramelspacher]

My original request:
------------------------------------------------------
I use a query to calculate a minimum service time for customers using the
following expression:

Hours: IIf([DurationHours]<1.5 And [DurationHours]>0,1.5,[DurationHours])

On occasion the service technician visits a location multiple times in a
day. So, for a given day there may be three records as such:

Date StartD/T EndD/T
3/6/09 9:00:00 9:30:00
3/6/09 10:00:00 10:15:00
3/6/09 14:00:00 15:00:00

Using this scenario, since the time between the EndD/T of the first record
and the StartD/T of the second record is less than 2 hours, I need to SUM the
durations from both records and apply the minimum, if the minimum isn't met
by the total.

Since the StartD/T of the third record is greater than 2 hours from the
previous record, then I need to count that as a separate service record,
applying the minimum if necessary.

The output is only in the query. I do not want to update existing data.
------------------------------------------------------
End Original Request

The answer I need would Sum Durations of the first two records and leave the
third as a separate record, prior to running the IIf() statement mentioned
above, which applies minimums to the service tickets.

I assume you are saying use an IIf() statement to combine, but I don't know
the syntax that looks for another service ticket and, if it exists, determine
if the StopDateTime of the previous record is less than 2 hours of the
StartDateTime of the current record and, if so, add the durations together.

Am I understanding everything correctly?

Here is an example, but maybe someone has an easier method:

CREATE TABLE Services (
customer_id LONG NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
PRIMARY KEY (customer_id, start_date)
);
INSERT INTO Services VALUES (1, #03/06/2009 08:00:00#, #03/06/2009 08:30:00#);
INSERT INTO Services VALUES (1, #03/06/2009 09:00:00#, #03/06/2009 09:30:00#);
INSERT INTO Services VALUES (1, #03/06/2009 10:00:00#, #03/06/2009 10:15:00#);
INSERT INTO Services VALUES (1, #03/06/2009 14:00:00#, #03/06/2009 15:00:00#);
INSERT INTO Services VALUES (2, #03/09/2009 10:00:00#, #03/09/2009 11:30:00#);
INSERT INTO Services VALUES (2, #03/09/2009 14:00:00#, #03/09/2009 14:30:00#);
INSERT INTO Services VALUES (2, #03/11/2009 15:30:00#, #03/11/2009 16:00:00#);

SELECT customer_id,
DATEVALUE([start_date]) AS [Service Date],
Rank,
SUM(DATEDIFF("n",[start_date],[end_date])) AS [Service Minutes]
FROM (SELECT s.customer_id,
s.start_date,
s.end_date,
COUNT(* ) AS Rank
FROM Services AS s
INNER JOIN Services AS s1
ON (s.customer_id = s1.customer_id)
AND (DATEVALUE(s.start_date) = DATEVALUE(s1.start_date))
WHERE (((DATEDIFF("n",s.end_date,s1.start_date)) < 120))
GROUP BY s.customer_id,s.start_date,s.end_date) AS t
GROUP BY customer_id,DATEVALUE([start_date]),Rank;

customer_id Service Date Rank Service Minutes
1 3/6/2009 3 75
1 3/6/2009 4 60
2 3/9/2009 1 90
2 3/9/2009 2 30
2 3/11/2009 1 30

 

[Jeff Boyce]

If the "minimum time" pertains to all durations for a tech for a customer on
a given day, your query would first have to calculate the duration of each
visit/session, then add those up for a tech for a customer for a day.

If you are saying that each of those three records has a minimum of 1.5
hours, do the IIF() on the duration of EACH record.

Is this what you were looking for?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Ok, either you are still working on it or I may be missing the answer
here.

My original request:
------------------------------------------------------
I use a query to calculate a minimum service time for customers using the
following expression:

Hours: IIf([DurationHours]<1.5 And [DurationHours]>0,1.5,[DurationHours])

On occasion the service technician visits a location multiple times in a
day. So, for a given day there may be three records as such:

Date StartD/T EndD/T
3/6/09 9:00:00 9:30:00
3/6/09 10:00:00 10:15:00
3/6/09 14:00:00 15:00:00

Using this scenario, since the time between the EndD/T of the first record
and the StartD/T of the second record is less than 2 hours, I need to SUM
the
durations from both records and apply the minimum, if the minimum isn't
met
by the total.

Since the StartD/T of the third record is greater than 2 hours from the
previous record, then I need to count that as a separate service record,
applying the minimum if necessary.

The output is only in the query. I do not want to update existing data.
------------------------------------------------------
End Original Request

The answer I need would Sum Durations of the first two records and leave
the
third as a separate record, prior to running the IIf() statement mentioned
above, which applies minimums to the service tickets.

I assume you are saying use an IIf() statement to combine, but I don't
know
the syntax that looks for another service ticket and, if it exists,
determine
if the StopDateTime of the previous record is less than 2 hours of the
StartDateTime of the current record and, if so, add the durations
together.

Am I understanding everything correctly?

Those wild and crazy SQL-Server guys! Aren't they just a riot?!

If you use a query in which you calculate Duration, then use a second
query
that uses that calculated (in the first query) Duration as part of an
IIF()
statement, I believe you could determine the "billable hours" (for lack
of a
better term).

Regards

Jeff Boyce
Microsoft Office/Access MVP


in
message news:[email protected]...

I agree with you and suspected that you would have a comment about that.
However, that is the case with the SQL database. I don't have any
control
over it. I'm not sure that it is an actual field in the SQL table or if
it
is
based on the "View". I'm not familiar with the concept of "SQL Views"
but
I'm
told there are both in the application.

In my queries I normally do my own calculations on Start and Stop
Date/Times
rather than using the Duration from the table. The existence of the
field
has
not created any issues so far - - all of my date/time calculations have
been
correct.


:

First of all, there's one field in your description that stands out as
different from the rest ... the calculated value.

It is quite rare to need to store a calculated value, and deciding to
do
so
requires that you also create procedures for keeping the calculated
value
and its underlying components in sync ... not for the faint-hearted!

Instead, consider using a query to derive (calculate) that value
whenever
you need it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"TraciAnnNeedsHelp" <[email protected]>
wrote
in
message Jeff, there are almost a hundred tables and "views" in the SQL
database,
so I
will try to keep descriptions limited to just the tables and fields
that
are
applicable.

tblCustomers
CustID - Primary
CustName - Text

tblTechs
TechID - Primary
TechName - Text
BillRate - Currency

tblTickets
TicketID - Primary
CustID - Foreign
Created - Date/Time
Assigned - Foreign (TechID)

tblTimeEntries
EntryID - Primary
TicketID - Foreign
TechID - Foreign
StartDateTime - Date/Time
StopDateTime - Date/Time
Duration - Calculated ([StopDateTime]-[StartDateTime])
Mileage - Number
Expenses - Currency

 

[TraciAnn]

Jeff,
Sorry for the delayed reply. I'm getting frustrated trying to work with this
newsgroup. I tried to set it up using OE but for some reason OE doesn't allow
me to reply to newsgroups, hopefully I can get it figured out soon.

You are correct in your first statement with one exception. "Minimum time"
pertains to the duration BETWEEN any two visits/sessions.

Let’s start with some conventions:
DVisit = The Duration of time from the beginning of a visit to the end of
a visit.
LVisit = The Duration from the end of one visit to the beginning of the
next visit (the Lapse).
CVisit = The result of combining two visits using the summed duration of
both, and using the End Time of the last visit for further comparison.
MaxLapse = the maximum duration between visits in order for them to be
combined.
MinVisit = the minimum duration to be applied to any given visit.

Variables to apply:
MaxLapse = 2
MinVisit = 1.5

In the example,
Date StartD/T EndD/T
3/6/09 9:00:00 9:30:00
3/6/09 10:00:00 10:15:00
3/6/09 14:00:00 15:00:00

We have 3 DVisits and 2 LVisits.
DVisit1 = 30 mins
LVisit1 = 30 mins
DVisit2 = 15 mins
LVisit2 = 3 hrs 45 mins (or 225 mins)
DVisit3 = 60 mins

Problem 1: How to find and calculate LVisit

Once LVisit can be found, then determine if the first 2 visits are combined
or do they stand alone. The following expression assumes we know the value of
LVisit1 (the time between the first two visits). If LVisit1 is less than
MaxLapse the first two visits are combined (becoming CVisit1), otherwise
DVisit1 is tested against the MinVisit so the minimum of at least 1.5 hours
is applied.

IIf(LVisit1 < MaxLapse, DVisit1 + DVisit2, IIf(DVisit1 < MinVisit, 1.5,
DVisit1))

The result of the first IIf expression is True, so the two visits are summed
DVisit1 + DVisit2 = 45 minutes and the result of the sum becomes the variable
for CVisit1.

There are two things we should know about CVisit1; Total Duration (45
minutes) and End Time (the ending time of DVisit2).

Problem 2: How to transition CVisit1 into the next comparison

Now that we have combined the first two visits (CVisit1) we have to
determine if there is another visit to compare it to. There is another visit
on the same day so we find the result (LVisit2) of the lapse between the end
of CVisit1 (or DVisit2) and the beginning of DVisit3.

IIf(LVisit2 < MaxLapse, CVisit1 + DVisit2, IIf(CVisit1 < MinVisit, 1.5,
CVisit1))

The result of the first IIf statement is False, so CVisit1 is passed to the
MinVisit comparison. Since the total duration of CVisit1 is 45 minutes, the
result of the second IIf statement is True and the minimum of 1.5 hours is
applied.

If the duration between Visit2 and Visit3 is less than 2 hours then all
three visits would be summed before a minimum is applied.

In this example the first two visits were combined and the minimum was
applied.

Problem 3: How to continue the process to DVisit3

Both LVisits were compared to the maximum so all that is left is to compare
DVisit3 to the minimum

IIf(DVisit3 < MinVisit, 1.5, DVisit3)

Finally, the end result of the whole process would produce two time entries
for 3/6/09.

Date Duration
3/6/09 1.5
3/6/09 1.5

Thanks so much for your consideration!!!

 

[TraciAnn]

Michael,

I haven't been ignoring your post. I have been having all kinds of trouble
viewing posts on this newsgroup and I can't seem to get other options working
either. So I have been at the mercy of when I can actually get the post to
appear.

I believe I can completely think through the logic of this process (see my
other post to Jeff today) but my logic doesn't combine with "Know how".

I will try to decipher the code you have provided but I already know that I
will not know where/how to apply it to my database.

Would you be willing to walk me through the details?

Thank you!!!
--
TraciAnn

My original request:
------------------------------------------------------
I use a query to calculate a minimum service time for customers using the
following expression:

Hours: IIf([DurationHours]<1.5 And [DurationHours]>0,1.5,[DurationHours])

On occasion the service technician visits a location multiple times in a
day. So, for a given day there may be three records as such:

Date StartD/T EndD/T
3/6/09 9:00:00 9:30:00
3/6/09 10:00:00 10:15:00
3/6/09 14:00:00 15:00:00

Using this scenario, since the time between the EndD/T of the first record
and the StartD/T of the second record is less than 2 hours, I need to SUM the
durations from both records and apply the minimum, if the minimum isn't met
by the total.

Since the StartD/T of the third record is greater than 2 hours from the
previous record, then I need to count that as a separate service record,
applying the minimum if necessary.

The output is only in the query. I do not want to update existing data.
------------------------------------------------------
End Original Request

The answer I need would Sum Durations of the first two records and leave the
third as a separate record, prior to running the IIf() statement mentioned
above, which applies minimums to the service tickets.

I assume you are saying use an IIf() statement to combine, but I don't know
the syntax that looks for another service ticket and, if it exists, determine
if the StopDateTime of the previous record is less than 2 hours of the
StartDateTime of the current record and, if so, add the durations together.

Am I understanding everything correctly?

Here is an example, but maybe someone has an easier method:

CREATE TABLE Services (
customer_id LONG NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
PRIMARY KEY (customer_id, start_date)
);
INSERT INTO Services VALUES (1, #03/06/2009 08:00:00#, #03/06/2009 08:30:00#);
INSERT INTO Services VALUES (1, #03/06/2009 09:00:00#, #03/06/2009 09:30:00#);
INSERT INTO Services VALUES (1, #03/06/2009 10:00:00#, #03/06/2009 10:15:00#);
INSERT INTO Services VALUES (1, #03/06/2009 14:00:00#, #03/06/2009 15:00:00#);
INSERT INTO Services VALUES (2, #03/09/2009 10:00:00#, #03/09/2009 11:30:00#);
INSERT INTO Services VALUES (2, #03/09/2009 14:00:00#, #03/09/2009 14:30:00#);
INSERT INTO Services VALUES (2, #03/11/2009 15:30:00#, #03/11/2009 16:00:00#);

SELECT customer_id,
DATEVALUE([start_date]) AS [Service Date],
Rank,
SUM(DATEDIFF("n",[start_date],[end_date])) AS [Service Minutes]
FROM (SELECT s.customer_id,
s.start_date,
s.end_date,
COUNT(* ) AS Rank
FROM Services AS s
INNER JOIN Services AS s1
ON (s.customer_id = s1.customer_id)
AND (DATEVALUE(s.start_date) = DATEVALUE(s1.start_date))
WHERE (((DATEDIFF("n",s.end_date,s1.start_date)) < 120))
GROUP BY s.customer_id,s.start_date,s.end_date) AS t
GROUP BY customer_id,DATEVALUE([start_date]),Rank;

customer_id Service Date Rank Service Minutes
1 3/6/2009 3 75
1 3/6/2009 4 60
2 3/9/2009 1 90
2 3/9/2009 2 30
2 3/11/2009 1 30

 

[Clif McIrvin]

Michael,

I haven't been ignoring your post. I have been having all kinds of
trouble
viewing posts on this newsgroup and I can't seem to get other options
working
either. So I have been at the mercy of when I can actually get the
post to
appear.

<snip>

TraciAnn -- another option is Access Monster
http://www.accessmonster.com/

I'm not real familiar with it; but after several posts re: trouble with
MS's website last week I visited it to at least see what others are
talking about.

It took a bit of digging around, because it is set up differently than
I'm used to; but it appears that microsoft.public.access posts appear in
either the General1 or General2 discussion groups.

Also, Google Groups are often recommended as a very good site for
searching these groups; though there have been several recent posts to
the effect that Google Groups are not a good avenue for posting -- why,
I really don't know.


I'd be happy to share my OE settings if you think that will help you
any.

 

[Jeff Boyce]

I suspect you'll need to "tighten down" your definitions even further, if
you're going to be able to tell Access how to do what you want it to do.

For example, your LVisit variable you describe as the amount of time
(duration) between the end of one visit and the beginning of the next. I'm
guessing you mean for the same customer, for the same tech, on the same day.
This might be an "of course!" moment, but Access will NOT know that until
you tell it.

Generically, one approach might be to build a function that cycles through
all visit info for the same customer, for the same tech, on the same day,
and calculates AS MANY visit & lapse durations as there are (I doubt very
much there will always be three billed periods as in the sample/example you
provided). So your function will have to accommodate an indeterminant
number of records ... got VBA?! If you don't have experience developing
procedures in VBA, this would be a very good time to start learning!

Once you have your 'visit' and 'lapse' variables, you might need another
procedure to do the type of comparison you are describing (or it might work
to roll it into the first function).

Again, this is only a generic look. Your actual mileage (and code) will
vary.

And if you don't feel you have the time and/or resources and/or experience
to build this kind of function, you may be able to find volunteers at
this/other newsgroups, or you may need to hire someone.

Best of luck on your project!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[TraciAnn]

Thanks Jeff!

I've been toying with Michael's SQL and it appears like it is going to work
once I work through some bugs.

I appreciate your time.
--
TraciAnn

I suspect you'll need to "tighten down" your definitions even further, if
you're going to be able to tell Access how to do what you want it to do.

For example, your LVisit variable you describe as the amount of time
(duration) between the end of one visit and the beginning of the next. I'm
guessing you mean for the same customer, for the same tech, on the same day.
This might be an "of course!" moment, but Access will NOT know that until
you tell it.

Generically, one approach might be to build a function that cycles through
all visit info for the same customer, for the same tech, on the same day,
and calculates AS MANY visit & lapse durations as there are (I doubt very
much there will always be three billed periods as in the sample/example you
provided). So your function will have to accommodate an indeterminant
number of records ... got VBA?! If you don't have experience developing
procedures in VBA, this would be a very good time to start learning!

Once you have your 'visit' and 'lapse' variables, you might need another
procedure to do the type of comparison you are describing (or it might work
to roll it into the first function).

Again, this is only a generic look. Your actual mileage (and code) will
vary.

And if you don't feel you have the time and/or resources and/or experience
to build this kind of function, you may be able to find volunteers at
this/other newsgroups, or you may need to hire someone.

Best of luck on your project!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[TraciAnn]

There you are!!!

You know what's funny is that YOUR posts are the ones I have the most
difficulty with. LOL

Just like with this one, I had to hit "Reply" 3 times before I didn't get a
"Service is Temporarily Unavailable".

I will try your suggestions and see how they work. Alternatively, someone
here in the office said they might be able to help me get OE setup.

Thanks Clif!!!