DLookup a range in another table?

[Guest]

Hi,
I'm not a newbie to Access, as I have been using it for years but only in
it's most basic form with simple expressions etc. I want to develop an
existing 60mb database and my first task is to calculate employee time on
jobs...

So, an employee is called out to a job and spends say 161 minutes on site.
I can produce this with two 'Date and Time' fields (from and to) and
multiply the funny number by 1440.

I want to convert the 161 minutes into hours but not so that it displays '2
hours, 41 minutes' as we bill by the hour. I've tried using 'DLookup' but I'm
struggling to get the required value. I can do it in Excel using a nested
'=IF' formula or a '=VLookup' formula.

I've set up another table with the following values
Minutes Hours
1 1
2 1
3 1
(4 to 60) 1
(61 to 120) 2
(121 to 180) 3 etc. etc.

How can I query this table so that it finds '161' in the list of 'Minutes'
and then calculates the '3' to end up in the original table?

Is this the right way to do it or is there a much easier way?

Thanks.

 

[Allen Browne]

You already have the 161 minutes, so do integer division by 60 to get the
hours, and use Mod to get the left over mintues, with Format() to make the
results look right:

=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")

Explanation of that:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

 

[Guest]

Hmm, that works but it doesn't give the result I'm looking for as it just
converts 161 minutes to 2:41 and I need to round it up to 3 hours. If I
delete the 00 at the end of the expression it rounds it up or down depending
on the nearest hour...

Any more suggestions?
Thanks.

You already have the 161 minutes, so do integer division by 60 to get the
hours, and use Mod to get the left over mintues, with Format() to make the
results look right:

=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")

Explanation of that:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
I'm not a newbie to Access, as I have been using it for years but only in
it's most basic form with simple expressions etc. I want to develop an
existing 60mb database and my first task is to calculate employee time on
jobs...

So, an employee is called out to a job and spends say 161 minutes on site.
I can produce this with two 'Date and Time' fields (from and to) and
multiply the funny number by 1440.

I want to convert the 161 minutes into hours but not so that it displays
'2
hours, 41 minutes' as we bill by the hour. I've tried using 'DLookup' but
I'm
struggling to get the required value. I can do it in Excel using a nested
'=IF' formula or a '=VLookup' formula.

I've set up another table with the following values
Minutes Hours
1 1
2 1
3 1
(4 to 60) 1
(61 to 120) 2
(121 to 180) 3 etc. etc.

How can I query this table so that it finds '161' in the list of 'Minutes'
and then calculates the '3' to end up in the original table?

Is this the right way to do it or is there a much easier way?

Thanks.

 

[Allen Browne]

Easier still:
Round([Minutes],0)

If you really do need to work with ranges of number from a talble, Tom
Ellison's article might help you do that efficiently:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hmm, that works but it doesn't give the result I'm looking for as it just
converts 161 minutes to 2:41 and I need to round it up to 3 hours. If I
delete the 00 at the end of the expression it rounds it up or down
depending
on the nearest hour...

Any more suggestions?
Thanks.

You already have the 161 minutes, so do integer division by 60 to get the
hours, and use Mod to get the left over mintues, with Format() to make
the
results look right:

=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")

Explanation of that:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

Hi,
I'm not a newbie to Access, as I have been using it for years but only
in
it's most basic form with simple expressions etc. I want to develop an
existing 60mb database and my first task is to calculate employee time
on
jobs...

So, an employee is called out to a job and spends say 161 minutes on
site.
I can produce this with two 'Date and Time' fields (from and to) and
multiply the funny number by 1440.

I want to convert the 161 minutes into hours but not so that it
displays
'2
hours, 41 minutes' as we bill by the hour. I've tried using 'DLookup'
but
I'm
struggling to get the required value. I can do it in Excel using a
nested
'=IF' formula or a '=VLookup' formula.

I've set up another table with the following values
Minutes Hours
1 1
2 1
3 1
(4 to 60) 1
(61 to 120) 2
(121 to 180) 3 etc. etc.

How can I query this table so that it finds '161' in the list of
'Minutes'
and then calculates the '3' to end up in the original table?

 

[Guest]

Thanks for your help so far Allen but I think the range of numbers would be
the best solution for us.

I had a look at Tom's article and it melted my brain

Thanks.

Easier still:
Round([Minutes],0)

If you really do need to work with ranges of number from a talble, Tom
Ellison's article might help you do that efficiently:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hmm, that works but it doesn't give the result I'm looking for as it just
converts 161 minutes to 2:41 and I need to round it up to 3 hours. If I
delete the 00 at the end of the expression it rounds it up or down
depending
on the nearest hour...

Any more suggestions?
Thanks.

You already have the 161 minutes, so do integer division by 60 to get the
hours, and use Mod to get the left over mintues, with Format() to make
the
results look right:

=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")

Explanation of that:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

Hi,
I'm not a newbie to Access, as I have been using it for years but only
in
it's most basic form with simple expressions etc. I want to develop an
existing 60mb database and my first task is to calculate employee time
on
jobs...

So, an employee is called out to a job and spends say 161 minutes on
site.
I can produce this with two 'Date and Time' fields (from and to) and
multiply the funny number by 1440.

I want to convert the 161 minutes into hours but not so that it
displays
'2
hours, 41 minutes' as we bill by the hour. I've tried using 'DLookup'
but
I'm
struggling to get the required value. I can do it in Excel using a
nested
'=IF' formula or a '=VLookup' formula.

I've set up another table with the following values
Minutes Hours
1 1
2 1
3 1
(4 to 60) 1
(61 to 120) 2
(121 to 180) 3 etc. etc.

How can I query this table so that it finds '161' in the list of
'Minutes'
and then calculates the '3' to end up in the original table?

 

[Guest]

I think I will start again, as my original posting was a bit confusing!

I’ve created a Table called ‘BillableMinutes’ and entered the following
details: -
From To Hours
1 60 1
61 75 1.25
76 90 1.5
91 105 1.75
106 120 2 (etc.)

I have a query called ‘Alarm Response – Data Entry – Query’ that displays
‘Time on Site’ in minutes. I am happy with the calculations that produce
these using the ‘datediff’ expression.

What I would like to do is create another field in this query that searches
the ‘From’ and ‘To’ columns in ‘BillableMinutes’ to find the row that matches
the ‘Time on Site’ value. Then, it would return the ‘Hours’ value in the
query.

So, another field called ‘BillableTime’ in the Query would detail “1†in the
first five rows, “2.75†in the 6th row and “1.5†in the 7th row.

Time on Site
6
40
25
16
60
161
83

Thanks.

Easier still:
Round([Minutes],0)

If you really do need to work with ranges of number from a talble, Tom
Ellison's article might help you do that efficiently:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hmm, that works but it doesn't give the result I'm looking for as it just
converts 161 minutes to 2:41 and I need to round it up to 3 hours. If I
delete the 00 at the end of the expression it rounds it up or down
depending
on the nearest hour...

Any more suggestions?
Thanks.

You already have the 161 minutes, so do integer division by 60 to get the
hours, and use Mod to get the left over mintues, with Format() to make
the
results look right:

=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")

Explanation of that:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

Hi,
I'm not a newbie to Access, as I have been using it for years but only
in
it's most basic form with simple expressions etc. I want to develop an
existing 60mb database and my first task is to calculate employee time
on
jobs...

So, an employee is called out to a job and spends say 161 minutes on
site.
I can produce this with two 'Date and Time' fields (from and to) and
multiply the funny number by 1440.

I want to convert the 161 minutes into hours but not so that it
displays
'2
hours, 41 minutes' as we bill by the hour. I've tried using 'DLookup'
but
I'm
struggling to get the required value. I can do it in Excel using a
nested
'=IF' formula or a '=VLookup' formula.

I've set up another table with the following values
Minutes Hours
1 1
2 1
3 1
(4 to 60) 1
(61 to 120) 2
(121 to 180) 3 etc. etc.

How can I query this table so that it finds '161' in the list of
'Minutes'
and then calculates the '3' to end up in the original table?

 

[Allen Browne]

If you don't want to follow Tom's sage advice, you could:
DLookup("Hours", "BillableMinutes", [Minutes] & " Between [From] And
[To]")

This assumes you have a control named Minutes on your form, and it contains
a number.

BTW, From and To are both reserved words in JET, so that could cause
problems. There's a list of reserved words here:
http://allenbrowne.com/AppIssueChecker.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I think I will start again, as my original posting was a bit confusing!

I've created a Table called 'BillableMinutes' and entered the following
details: -
From To Hours
1 60 1
61 75 1.25
76 90 1.5
91 105 1.75
106 120 2 (etc.)

I have a query called 'Alarm Response - Data Entry - Query' that displays
'Time on Site' in minutes. I am happy with the calculations that produce
these using the 'datediff' expression.

What I would like to do is create another field in this query that
searches
the 'From' and 'To' columns in 'BillableMinutes' to find the row that
matches
the 'Time on Site' value. Then, it would return the 'Hours' value in the
query.

So, another field called 'BillableTime' in the Query would detail "1" in
the
first five rows, "2.75" in the 6th row and "1.5" in the 7th row.

Time on Site
6
40
25
16
60
161
83

Thanks.

Easier still:
Round([Minutes],0)

If you really do need to work with ranges of number from a talble, Tom
Ellison's article might help you do that efficiently:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hmm, that works but it doesn't give the result I'm looking for as it
just
converts 161 minutes to 2:41 and I need to round it up to 3 hours. If I
delete the 00 at the end of the expression it rounds it up or down
depending
on the nearest hour...

Any more suggestions?
Thanks.

:

You already have the 161 minutes, so do integer division by 60 to get
the
hours, and use Mod to get the left over mintues, with Format() to make
the
results look right:

=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")

Explanation of that:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

Hi,
I'm not a newbie to Access, as I have been using it for years but
only
in
it's most basic form with simple expressions etc. I want to develop
an
existing 60mb database and my first task is to calculate employee
time
on
jobs...

So, an employee is called out to a job and spends say 161 minutes on
site.
I can produce this with two 'Date and Time' fields (from and to) and
multiply the funny number by 1440.

I want to convert the 161 minutes into hours but not so that it
displays
'2
hours, 41 minutes' as we bill by the hour. I've tried using
'DLookup'
but
I'm
struggling to get the required value. I can do it in Excel using a
nested
'=IF' formula or a '=VLookup' formula.

I've set up another table with the following values
Minutes Hours
1 1
2 1
3 1
(4 to 60) 1
(61 to 120) 2
(121 to 180) 3 etc. etc.

How can I query this table so that it finds '161' in the list of
'Minutes'
and then calculates the '3' to end up in the original table?

 

[Guest]

Excellent, that works perfectly!
It's not that I didn't want to follow Tom's advice, more a case of I
couldn't follow it

I've changed the field names to 'RangeFrom' and 'RangeTo' to try and prevent
any reserved word conflicts.

Thanks once again...

If you don't want to follow Tom's sage advice, you could:
DLookup("Hours", "BillableMinutes", [Minutes] & " Between [From] And
[To]")

This assumes you have a control named Minutes on your form, and it contains
a number.

BTW, From and To are both reserved words in JET, so that could cause
problems. There's a list of reserved words here:
http://allenbrowne.com/AppIssueChecker.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I think I will start again, as my original posting was a bit confusing!

I've created a Table called 'BillableMinutes' and entered the following
details: -
From To Hours
1 60 1
61 75 1.25
76 90 1.5
91 105 1.75
106 120 2 (etc.)

I have a query called 'Alarm Response - Data Entry - Query' that displays
'Time on Site' in minutes. I am happy with the calculations that produce
these using the 'datediff' expression.

What I would like to do is create another field in this query that
searches
the 'From' and 'To' columns in 'BillableMinutes' to find the row that
matches
the 'Time on Site' value. Then, it would return the 'Hours' value in the
query.

So, another field called 'BillableTime' in the Query would detail "1" in
the
first five rows, "2.75" in the 6th row and "1.5" in the 7th row.

Time on Site
6
40
25
16
60
161
83

Thanks.

Easier still:
Round([Minutes],0)

If you really do need to work with ranges of number from a talble, Tom
Ellison's article might help you do that efficiently:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hmm, that works but it doesn't give the result I'm looking for as it
just
converts 161 minutes to 2:41 and I need to round it up to 3 hours. If I
delete the 00 at the end of the expression it rounds it up or down
depending
on the nearest hour...

Any more suggestions?
Thanks.

:

You already have the 161 minutes, so do integer division by 60 to get
the
hours, and use Mod to get the left over mintues, with Format() to make
the
results look right:

=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")

Explanation of that:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

Hi,
I'm not a newbie to Access, as I have been using it for years but
only
in
it's most basic form with simple expressions etc. I want to develop
an
existing 60mb database and my first task is to calculate employee
time
on
jobs...

So, an employee is called out to a job and spends say 161 minutes on
site.
I can produce this with two 'Date and Time' fields (from and to) and
multiply the funny number by 1440.

I want to convert the 161 minutes into hours but not so that it
displays
'2
hours, 41 minutes' as we bill by the hour. I've tried using
'DLookup'
but
I'm
struggling to get the required value. I can do it in Excel using a
nested
'=IF' formula or a '=VLookup' formula.

I've set up another table with the following values
Minutes Hours
1 1
2 1
3 1
(4 to 60) 1
(61 to 120) 2
(121 to 180) 3 etc. etc.

How can I query this table so that it finds '161' in the list of
'Minutes'
and then calculates the '3' to end up in the original table?