dlookup in query

[Guest]

I need help, I have a query where I rounded some numbers with:
Margin:Round([Sales info.Salespgactual]+0.0001,2) this rounds my number to
whole numbers. So if I have 40.27 % it rounded it to 40.00% so on and so
forth. My question is this I need to do a dlookup that looks at the 40.00%
in my query and looks up in a table called [Margin Acc table 1] the 40.00%
which is [Marginrate] and returns the value of .68% [Marginincentive] which
is in column 3 of the Margin Acc table 1. I am having problems getting it
look at the query for the answer. Please help

 

[Tom Ellison]

Dear Pbb:

Does your SalesInfo table have a row for every integer value of Margin? Or
does it have some other structure? Please show how this must work, with
examples. If the value of Salespgactual is 40.5 with which value in
[Margin Acc table 1].Marginrate does that align. How about 40.99?

Please show some of what is in [Margin Acc table 1] and explain the rule you
use in looking up a value. There are a lot of potential alternatives.

Tom Ellison

 

[Guest]

Dear Pbb:

Does your SalesInfo table have a row for every integer value of Margin? Or
does it have some other structure? Please show how this must work, with
examples. If the value of Salespgactual is 40.5 with which value in
[Margin Acc table 1].Marginrate does that align. How about 40.99?

Please show some of what is in [Margin Acc table 1] and explain the rule you
use in looking up a value. There are a lot of potential alternatives.

Tom Ellison

I need help, I have a query where I rounded some numbers with:
Margin:Round([Sales info.Salespgactual]+0.0001,2) this rounds my number to
whole numbers. So if I have 40.27 % it rounded it to 40.00% so on and so
forth. My question is this I need to do a dlookup that looks at the
40.00%
in my query and looks up in a table called [Margin Acc table 1] the 40.00%
which is [Marginrate] and returns the value of .68% [Marginincentive]
which
is in column 3 of the Margin Acc table 1. I am having problems getting it
look at the query for the answer. Please help

Tom,

The salesifno table has the "actual" margin rates, I then round that number
to the nearest whole number in the query. So if the Margin is 40.5 it will
round to 41.00, iif it is 41.99 it will round to 42.00, if it is 42.27 it
will round to 42.00 and in the [Marin Acc table 1] there is a list of numbers
all whole numbers and next to the whole number is the lookup value.

For example, The table looks like this:
Marginacctable
141.00 = 0.68
42.00 = 0.75
43.00 = 0.80
44.00 = 0.81So I want the 41.00 to match to 41.00 and return the value of 0.68 and if
the value is 42.00 than I want the 0.75 value returned. There are only whole
numbers in the table.

 

[Guest]

Dear Pbb:

Does your SalesInfo table have a row for every integer value of Margin? Or
does it have some other structure? Please show how this must work, with
examples. If the value of Salespgactual is 40.5 with which value in
[Margin Acc table 1].Marginrate does that align. How about 40.99?

Please show some of what is in [Margin Acc table 1] and explain the rule you
use in looking up a value. There are a lot of potential alternatives.

Tom Ellison

I need help, I have a query where I rounded some numbers with:
Margin:Round([Sales info.Salespgactual]+0.0001,2) this rounds my number to
whole numbers. So if I have 40.27 % it rounded it to 40.00% so on and so
forth. My question is this I need to do a dlookup that looks at the
40.00%
in my query and looks up in a table called [Margin Acc table 1] the 40.00%
which is [Marginrate] and returns the value of .68% [Marginincentive]
which
is in column 3 of the Margin Acc table 1. I am having problems getting it
look at the query for the answer. Please help

Continue from last post:
I did not get to finish it, it posted, so I hope that answered your questions. Thanks for your help.....

Pbb

This is how the table looks,
Marginacctable Marginrate Marginincentive
1 41.00 0.68
1 42.00 0.75
1 43.00 0.80
1 44.00 0.90

Etc, So I want to lookup the 41 ,42, 43 etc and return the value
0.68,0.75,0.80 etc for each person margin. One this number is returned I want
to use the 0.68 or 0.75 or 0.80 to calculate that percentage number against
actual sales to return a commission due.

Thanks again...

 

[Tom Ellison]

Dear Pbb:

If you want 40.5 to round down to 40.0 you are going to need a custom
rounding routine.

Once you have it rounded, you can retrieve the Marginrate with a JOIN on
that value, but I'm a bit leary of that. There much can go wrong there, and
can cause whole rows of data to disappear. So, I'd recommend a subquery
where you can control the NULL values that could result from a failed
lookup.

I offer this query as a starting point:

SELECT Salespgactual, Round(Salespgactual) AS LookupSales,
Nz((SELECT Marginincentive
FROM [Margin Acc table 1] M
WHERE M.Marginrate = I.Round(Salespgactual)), 99)
AS Marginincentive
FROM [Sales info] I

If the margin is not found, it will return a rate of 99. Choose any other
value you like for this. Hopefully, you won't need this value anyway.

You can use the value from lines 2-4 above inside a calculation if you like.

Please let me know if this helped, and if I can be of any further
assistance.

Tom Ellison

Dear Pbb:

Does your SalesInfo table have a row for every integer value of Margin?
Or
does it have some other structure? Please show how this must work, with
examples. If the value of Salespgactual is 40.5 with which value in
[Margin Acc table 1].Marginrate does that align. How about 40.99?

Please show some of what is in [Margin Acc table 1] and explain the rule
you
use in looking up a value. There are a lot of potential alternatives.

Tom Ellison

I need help, I have a query where I rounded some numbers with:
Margin:Round([Sales info.Salespgactual]+0.0001,2) this rounds my number
to
whole numbers. So if I have 40.27 % it rounded it to 40.00% so on and
so
forth. My question is this I need to do a dlookup that looks at the
40.00%
in my query and looks up in a table called [Margin Acc table 1] the
40.00%
which is [Marginrate] and returns the value of .68% [Marginincentive]
which
is in column 3 of the Margin Acc table 1. I am having problems getting
it
look at the query for the answer. Please help

Continue from last post:
I did not get to finish it, it posted, so I hope that answered your
questions. Thanks for your help.....

Pbb

This is how the table looks,
Marginacctable Marginrate Marginincentive
1 41.00 0.68
1 42.00 0.75
1 43.00 0.80
1 44.00 0.90

Etc, So I want to lookup the 41 ,42, 43 etc and return the value
0.68,0.75,0.80 etc for each person margin. One this number is returned I
want
to use the 0.68 or 0.75 or 0.80 to calculate that percentage number
against
actual sales to return a commission due.

Thanks again...

 

[Guest]

Thanks I will give that a try tommorrow and let you know...
Pbb

Dear Pbb:

If you want 40.5 to round down to 40.0 you are going to need a custom
rounding routine.

Once you have it rounded, you can retrieve the Marginrate with a JOIN on
that value, but I'm a bit leary of that. There much can go wrong there, and
can cause whole rows of data to disappear. So, I'd recommend a subquery
where you can control the NULL values that could result from a failed
lookup.

I offer this query as a starting point:

SELECT Salespgactual, Round(Salespgactual) AS LookupSales,
Nz((SELECT Marginincentive
FROM [Margin Acc table 1] M
WHERE M.Marginrate = I.Round(Salespgactual)), 99)
AS Marginincentive
FROM [Sales info] I

If the margin is not found, it will return a rate of 99. Choose any other
value you like for this. Hopefully, you won't need this value anyway.

You can use the value from lines 2-4 above inside a calculation if you like.

Please let me know if this helped, and if I can be of any further
assistance.

Tom Ellison

Dear Pbb:

Does your SalesInfo table have a row for every integer value of Margin?
Or
does it have some other structure? Please show how this must work, with
examples. If the value of Salespgactual is 40.5 with which value in
[Margin Acc table 1].Marginrate does that align. How about 40.99?

Please show some of what is in [Margin Acc table 1] and explain the rule
you
use in looking up a value. There are a lot of potential alternatives.

Tom Ellison


I need help, I have a query where I rounded some numbers with:
Margin:Round([Sales info.Salespgactual]+0.0001,2) this rounds my number
to
whole numbers. So if I have 40.27 % it rounded it to 40.00% so on and
so
forth. My question is this I need to do a dlookup that looks at the
40.00%
in my query and looks up in a table called [Margin Acc table 1] the
40.00%
which is [Marginrate] and returns the value of .68% [Marginincentive]
which
is in column 3 of the Margin Acc table 1. I am having problems getting
it
look at the query for the answer. Please help

Continue from last post:
I did not get to finish it, it posted, so I hope that answered your
questions. Thanks for your help.....

Pbb

This is how the table looks,
Marginacctable Marginrate Marginincentive
1 41.00 0.68
1 42.00 0.75
1 43.00 0.80
1 44.00 0.90

Etc, So I want to lookup the 41 ,42, 43 etc and return the value
0.68,0.75,0.80 etc for each person margin. One this number is returned I
want
to use the 0.68 or 0.75 or 0.80 to calculate that percentage number
against
actual sales to return a commission due.

Thanks again...

 

[Guest]

Tom,
Where do I put this, in its on column or in creteria? I could not get yours
to work so I put the below infomation in a column and it work (1), however,
when I try to run a report from the query it tells me I this.(2)

(1)
Split Mar 4: (SELECT[Marginincentive]FROM[Margin Acc table 1]WHERE [Margin
Acc table 1].[Marginrate]=Round([Sales info_3.Salesgpactual]+0.0001,2))

(2)
Problems:
Objects that I depend on:
- Missing - Subquery
Objects that depend on me:
- Query: Sales

There seems to a problem can you offer any advice.

Please let me know as soon as you can, Thank you so much. Pbb

Thanks I will give that a try tommorrow and let you know...
Pbb

Dear Pbb:

If you want 40.5 to round down to 40.0 you are going to need a custom
rounding routine.

Once you have it rounded, you can retrieve the Marginrate with a JOIN on
that value, but I'm a bit leary of that. There much can go wrong there, and
can cause whole rows of data to disappear. So, I'd recommend a subquery
where you can control the NULL values that could result from a failed
lookup.

I offer this query as a starting point:

SELECT Salespgactual, Round(Salespgactual) AS LookupSales,
Nz((SELECT Marginincentive
FROM [Margin Acc table 1] M
WHERE M.Marginrate = I.Round(Salespgactual)), 99)
AS Marginincentive
FROM [Sales info] I

If the margin is not found, it will return a rate of 99. Choose any other
value you like for this. Hopefully, you won't need this value anyway.

You can use the value from lines 2-4 above inside a calculation if you like.

Please let me know if this helped, and if I can be of any further
assistance.

Tom Ellison

:

Dear Pbb:

Does your SalesInfo table have a row for every integer value of Margin?
Or
does it have some other structure? Please show how this must work, with
examples. If the value of Salespgactual is 40.5 with which value in
[Margin Acc table 1].Marginrate does that align. How about 40.99?

Please show some of what is in [Margin Acc table 1] and explain the rule
you
use in looking up a value. There are a lot of potential alternatives.

Tom Ellison


I need help, I have a query where I rounded some numbers with:
Margin:Round([Sales info.Salespgactual]+0.0001,2) this rounds my number
to
whole numbers. So if I have 40.27 % it rounded it to 40.00% so on and
so
forth. My question is this I need to do a dlookup that looks at the
40.00%
in my query and looks up in a table called [Margin Acc table 1] the
40.00%
which is [Marginrate] and returns the value of .68% [Marginincentive]
which
is in column 3 of the Margin Acc table 1. I am having problems getting
it
look at the query for the answer. Please help

Continue from last post:
I did not get to finish it, it posted, so I hope that answered your
questions. Thanks for your help.....
Pbb

This is how the table looks,
Marginacctable Marginrate Marginincentive
1 41.00 0.68
1 42.00 0.75
1 43.00 0.80
1 44.00 0.90

Etc, So I want to lookup the 41 ,42, 43 etc and return the value
0.68,0.75,0.80 etc for each person margin. One this number is returned I
want
to use the 0.68 or 0.75 or 0.80 to calculate that percentage number
against
actual sales to return a commission due.

Thanks again...

 

[Tom Ellison]

Dear Pbb:

What I provided is a complete query. Paste it into the SQL View of a new
query. SQL View is accessible using the "drop down" of the control that
looks like a rectangle representing a datasheet, found at the upper left of
the screen under "File" (if you haven't moved things around!) and has a down
arrow to the right of it. Click on the down arrow, and select SQL View.

Tom Ellison

Tom,
Where do I put this, in its on column or in creteria? I could not get
yours
to work so I put the below infomation in a column and it work (1),
however,
when I try to run a report from the query it tells me I this.(2)

(1)
Split Mar 4: (SELECT[Marginincentive]FROM[Margin Acc table 1]WHERE [Margin
Acc table 1].[Marginrate]=Round([Sales info_3.Salesgpactual]+0.0001,2))

(2)
Problems:
Objects that I depend on:
- Missing - Subquery
Objects that depend on me:
- Query: Sales

There seems to a problem can you offer any advice.

Please let me know as soon as you can, Thank you so much. Pbb

Thanks I will give that a try tommorrow and let you know...
Pbb

Dear Pbb:

If you want 40.5 to round down to 40.0 you are going to need a custom
rounding routine.

Once you have it rounded, you can retrieve the Marginrate with a JOIN
on
that value, but I'm a bit leary of that. There much can go wrong
there, and
can cause whole rows of data to disappear. So, I'd recommend a
subquery
where you can control the NULL values that could result from a failed
lookup.

I offer this query as a starting point:

SELECT Salespgactual, Round(Salespgactual) AS LookupSales,
Nz((SELECT Marginincentive
FROM [Margin Acc table 1] M
WHERE M.Marginrate = I.Round(Salespgactual)), 99)
AS Marginincentive
FROM [Sales info] I

If the margin is not found, it will return a rate of 99. Choose any
other
value you like for this. Hopefully, you won't need this value anyway.

You can use the value from lines 2-4 above inside a calculation if you
like.

Please let me know if this helped, and if I can be of any further
assistance.

Tom Ellison




:

Dear Pbb:

Does your SalesInfo table have a row for every integer value of
Margin?
Or
does it have some other structure? Please show how this must work,
with
examples. If the value of Salespgactual is 40.5 with which value
in
[Margin Acc table 1].Marginrate does that align. How about 40.99?

Please show some of what is in [Margin Acc table 1] and explain the
rule
you
use in looking up a value. There are a lot of potential
alternatives.

Tom Ellison


I need help, I have a query where I rounded some numbers with:
Margin:Round([Sales info.Salespgactual]+0.0001,2) this rounds my
number
to
whole numbers. So if I have 40.27 % it rounded it to 40.00% so on
and
so
forth. My question is this I need to do a dlookup that looks at
the
40.00%
in my query and looks up in a table called [Margin Acc table 1]
the
40.00%
which is [Marginrate] and returns the value of .68%
[Marginincentive]
which
is in column 3 of the Margin Acc table 1. I am having problems
getting
it
look at the query for the answer. Please help

Continue from last post:
I did not get to finish it, it posted, so I hope that answered your
questions. Thanks for your help.....
Pbb

This is how the table looks,
Marginacctable Marginrate Marginincentive
1 41.00 0.68
1 42.00 0.75
1 43.00 0.80
1 44.00 0.90

Etc, So I want to lookup the 41 ,42, 43 etc and return the value
0.68,0.75,0.80 etc for each person margin. One this number is
returned I
want
to use the 0.68 or 0.75 or 0.80 to calculate that percentage number
against
actual sales to return a commission due.

Thanks again...

 

[Guest]

Dear Tom,

I know this is going to sound stupid however, being new to Access, what do I
name the new query and how does it know it goes with the other query. I
pasted this information but it did not like the "I.Round" can you help?
Thank you so much for your time.

Pbb

Dear Pbb:

What I provided is a complete query. Paste it into the SQL View of a new
query. SQL View is accessible using the "drop down" of the control that
looks like a rectangle representing a datasheet, found at the upper left of
the screen under "File" (if you haven't moved things around!) and has a down
arrow to the right of it. Click on the down arrow, and select SQL View.

Tom Ellison

Tom,
Where do I put this, in its on column or in creteria? I could not get
yours
to work so I put the below infomation in a column and it work (1),
however,
when I try to run a report from the query it tells me I this.(2)

(1)
Split Mar 4: (SELECT[Marginincentive]FROM[Margin Acc table 1]WHERE [Margin
Acc table 1].[Marginrate]=Round([Sales info_3.Salesgpactual]+0.0001,2))

(2)
Problems:
Objects that I depend on:
- Missing - Subquery
Objects that depend on me:
- Query: Sales

There seems to a problem can you offer any advice.

Please let me know as soon as you can, Thank you so much. Pbb

Thanks I will give that a try tommorrow and let you know...
Pbb

:

Dear Pbb:

If you want 40.5 to round down to 40.0 you are going to need a custom
rounding routine.

Once you have it rounded, you can retrieve the Marginrate with a JOIN
on
that value, but I'm a bit leary of that. There much can go wrong
there, and
can cause whole rows of data to disappear. So, I'd recommend a
subquery
where you can control the NULL values that could result from a failed
lookup.

I offer this query as a starting point:

SELECT Salespgactual, Round(Salespgactual) AS LookupSales,
Nz((SELECT Marginincentive
FROM [Margin Acc table 1] M
WHERE M.Marginrate = I.Round(Salespgactual)), 99)
AS Marginincentive
FROM [Sales info] I

If the margin is not found, it will return a rate of 99. Choose any
other
value you like for this. Hopefully, you won't need this value anyway.

You can use the value from lines 2-4 above inside a calculation if you
like.

Please let me know if this helped, and if I can be of any further
assistance.

Tom Ellison




:

Dear Pbb:

Does your SalesInfo table have a row for every integer value of
Margin?
Or
does it have some other structure? Please show how this must work,
with
examples. If the value of Salespgactual is 40.5 with which value
in
[Margin Acc table 1].Marginrate does that align. How about 40.99?

Please show some of what is in [Margin Acc table 1] and explain the
rule
you
use in looking up a value. There are a lot of potential
alternatives.

Tom Ellison


I need help, I have a query where I rounded some numbers with:
Margin:Round([Sales info.Salespgactual]+0.0001,2) this rounds my
number
to
whole numbers. So if I have 40.27 % it rounded it to 40.00% so on
and
so
forth. My question is this I need to do a dlookup that looks at
the
40.00%
in my query and looks up in a table called [Margin Acc table 1]
the
40.00%
which is [Marginrate] and returns the value of .68%
[Marginincentive]
which
is in column 3 of the Margin Acc table 1. I am having problems
getting
it
look at the query for the answer. Please help

Continue from last post:
I did not get to finish it, it posted, so I hope that answered your
questions. Thanks for your help.....
Pbb

This is how the table looks,
Marginacctable Marginrate Marginincentive
1 41.00 0.68
1 42.00 0.75
1 43.00 0.80
1 44.00 0.90

Etc, So I want to lookup the 41 ,42, 43 etc and return the value
0.68,0.75,0.80 etc for each person margin. One this number is
returned I
want
to use the 0.68 or 0.75 or 0.80 to calculate that percentage number
against
actual sales to return a commission due.

Thanks again...

 

[Tom Ellison]

Dear Pbb:

First, you can run and test the query without saving it. If you do want to
save it, use any name you wish, but it must not be the name of any existing
query or table.

As to how "it know(s) it goes with the other query" it is the case (as
indicated above) that the set of all queries and tables have unique names.
So, when you reference a name, it knows which query or table is referenced.

Tom Ellison

Dear Tom,

I know this is going to sound stupid however, being new to Access, what do
I
name the new query and how does it know it goes with the other query. I
pasted this information but it did not like the "I.Round" can you help?
Thank you so much for your time.

Pbb

Dear Pbb:

What I provided is a complete query. Paste it into the SQL View of a new
query. SQL View is accessible using the "drop down" of the control that
looks like a rectangle representing a datasheet, found at the upper left
of
the screen under "File" (if you haven't moved things around!) and has a
down
arrow to the right of it. Click on the down arrow, and select SQL View.

Tom Ellison

Tom,
Where do I put this, in its on column or in creteria? I could not get
yours
to work so I put the below infomation in a column and it work (1),
however,
when I try to run a report from the query it tells me I this.(2)

(1)
Split Mar 4: (SELECT[Marginincentive]FROM[Margin Acc table 1]WHERE
[Margin
Acc table 1].[Marginrate]=Round([Sales info_3.Salesgpactual]+0.0001,2))

(2)
Problems:
Objects that I depend on:
- Missing - Subquery
Objects that depend on me:
- Query: Sales

There seems to a problem can you offer any advice.

Please let me know as soon as you can, Thank you so much. Pbb

:

Thanks I will give that a try tommorrow and let you know...
Pbb

:

Dear Pbb:

If you want 40.5 to round down to 40.0 you are going to need a
custom
rounding routine.

Once you have it rounded, you can retrieve the Marginrate with a
JOIN
on
that value, but I'm a bit leary of that. There much can go wrong
there, and
can cause whole rows of data to disappear. So, I'd recommend a
subquery
where you can control the NULL values that could result from a
failed
lookup.

I offer this query as a starting point:

SELECT Salespgactual, Round(Salespgactual) AS LookupSales,
Nz((SELECT Marginincentive
FROM [Margin Acc table 1] M
WHERE M.Marginrate = I.Round(Salespgactual)), 99)
AS Marginincentive
FROM [Sales info] I

If the margin is not found, it will return a rate of 99. Choose any
other
value you like for this. Hopefully, you won't need this value
anyway.

You can use the value from lines 2-4 above inside a calculation if
you
like.

Please let me know if this helped, and if I can be of any further
assistance.

Tom Ellison




:

Dear Pbb:

Does your SalesInfo table have a row for every integer value of
Margin?
Or
does it have some other structure? Please show how this must
work,
with
examples. If the value of Salespgactual is 40.5 with which
value
in
[Margin Acc table 1].Marginrate does that align. How about
40.99?

Please show some of what is in [Margin Acc table 1] and explain
the
rule
you
use in looking up a value. There are a lot of potential
alternatives.

Tom Ellison


I need help, I have a query where I rounded some numbers with:
Margin:Round([Sales info.Salespgactual]+0.0001,2) this rounds
my
number
to
whole numbers. So if I have 40.27 % it rounded it to 40.00% so
on
and
so
forth. My question is this I need to do a dlookup that looks
at
the
40.00%
in my query and looks up in a table called [Margin Acc table 1]
the
40.00%
which is [Marginrate] and returns the value of .68%
[Marginincentive]
which
is in column 3 of the Margin Acc table 1. I am having problems
getting
it
look at the query for the answer. Please help

Continue from last post:
I did not get to finish it, it posted, so I hope that answered
your
questions. Thanks for your help.....
Pbb

This is how the table looks,
Marginacctable Marginrate Marginincentive
1 41.00 0.68
1 42.00 0.75
1 43.00 0.80
1 44.00 0.90

Etc, So I want to lookup the 41 ,42, 43 etc and return the value
0.68,0.75,0.80 etc for each person margin. One this number is
returned I
want
to use the 0.68 or 0.75 or 0.80 to calculate that percentage
number
against
actual sales to return a commission due.

Thanks again...