dlookup

[Christina]

I don't know what my problem is. I post and then I can't find my answers.
Anyhow, can someone please help.

I have two tables.1 - Salaries Register witha field named Gross. Table
2..Income Tax with fields named PayRangeLow, PayRangeHigh. Tax payable.

I want to run a query to have the Tax payable is the gross falls between the
PayRangeLow and PayRangeHigh.

I need detail help with the query, as far as what needs to be in the fields
of the query..table name, criteria etc


PLEASE HELP


Thanks

 

[Ken Snell \(MVP\)]

Your question was answered in the previous thread, which you say you "cannot
find". I will repost the answer here, though I'm not sure how you'll "find"
this thread either?

This type of query is more easily built using SQL View rather than design
view. But, let me give you the full SQL statement of the query and then I'll
discuss how to build it in design view -- note that you didn't tell us the
name of the second table, so I'm using a generic name (
WhateverTheNotherTableIsNamed ) for it in this statement:

SELECT [Salaries Register].GROSS,
(SELECT T.[Tax Amount]
FROM WhateverTheNotherTableIsNamed AS T
WHERE [Salaries Register].GROSS Between
T.PayRangeLow And T.PayRangeHigh) AS TaxAmount
FROM [Salaries Register];


To do this in design view, add the [Salaries Register] table to the grid.
Put the GROSS field on the grid.
Then, in the first empty column, put this entire expression in the Field:
box (be sure to replace the generic table name with the real name of your
other table):

TaxAmount: (SELECT T.[Tax Amount] FROM WhateverTheNotherTableIsNamed AS T
WHERE [Salaries Register].GROSS Between T.PayRangeLow And T.PayRangeHigh)


An alternative way to do this query is to use the DLookup function for the
TaxAmount field value:

SELECT [Salaries Register].GROSS,
DLookup("Tax Amount", "WhateverTheNotherTableIsNamed",
[Salaries Register].GROSS & " Between
[PayRangeLow] And [PayRangeHigh]") AS TaxAmount
FROM [Salaries Register];


In this example, the second column in design view would have this expression
in the Field box (again, remember to replace the generic table name with the
real name):

TaxAmount: DLookup("Tax Amount", "WhateverTheNotherTableIsNamed", [Salaries
Register].GROSS & " Between [PayRangeLow] And [PayRangeHigh]")

 

[Christina]

Thanks. I got it to work. I have one problem, which I can't understand
why. I had dummy data in the IncomeTax TABLE, and when I switch out and put
the correct data the query does not want to work.

ALso I need to do one for Social Security...with the same field
names....Table SOCIAL SECURITY


Thanks so much for your help

Your question was answered in the previous thread, which you say you "cannot
find". I will repost the answer here, though I'm not sure how you'll "find"
this thread either?

This type of query is more easily built using SQL View rather than design
view. But, let me give you the full SQL statement of the query and then I'll
discuss how to build it in design view -- note that you didn't tell us the
name of the second table, so I'm using a generic name (
WhateverTheNotherTableIsNamed ) for it in this statement:

SELECT [Salaries Register].GROSS,
(SELECT T.[Tax Amount]
FROM WhateverTheNotherTableIsNamed AS T
WHERE [Salaries Register].GROSS Between
T.PayRangeLow And T.PayRangeHigh) AS TaxAmount
FROM [Salaries Register];


To do this in design view, add the [Salaries Register] table to the grid.
Put the GROSS field on the grid.
Then, in the first empty column, put this entire expression in the Field:
box (be sure to replace the generic table name with the real name of your
other table):

TaxAmount: (SELECT T.[Tax Amount] FROM WhateverTheNotherTableIsNamed AS T
WHERE [Salaries Register].GROSS Between T.PayRangeLow And T.PayRangeHigh)


An alternative way to do this query is to use the DLookup function for the
TaxAmount field value:

SELECT [Salaries Register].GROSS,
DLookup("Tax Amount", "WhateverTheNotherTableIsNamed",
[Salaries Register].GROSS & " Between
[PayRangeLow] And [PayRangeHigh]") AS TaxAmount
FROM [Salaries Register];


In this example, the second column in design view would have this expression
in the Field box (again, remember to replace the generic table name with the
real name):

TaxAmount: DLookup("Tax Amount", "WhateverTheNotherTableIsNamed", [Salaries
Register].GROSS & " Between [PayRangeLow] And [PayRangeHigh]")

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I don't know what my problem is. I post and then I can't find my answers.
Anyhow, can someone please help.

I have two tables.1 - Salaries Register witha field named Gross. Table
2..Income Tax with fields named PayRangeLow, PayRangeHigh. Tax payable.

I want to run a query to have the Tax payable is the gross falls between
the
PayRangeLow and PayRangeHigh.

I need detail help with the query, as far as what needs to be in the
fields
of the query..table name, criteria etc


PLEASE HELP


Thanks

 

[Ken Snell \(MVP\)]

What is the datatype for these three fields:

[Salaries Register].GROSS
IncomeTax.PayRangeLow
IncomeTax.PayRangeHigh

And show us examples of the dummy data that worked, and the real data that
don't work.


I don't understand what you want to do with the [Social Security] table with
respect to the other tables?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks. I got it to work. I have one problem, which I can't understand
why. I had dummy data in the IncomeTax TABLE, and when I switch out and
put
the correct data the query does not want to work.

ALso I need to do one for Social Security...with the same field
names....Table SOCIAL SECURITY


Thanks so much for your help

Your question was answered in the previous thread, which you say you
"cannot
find". I will repost the answer here, though I'm not sure how you'll
"find"
this thread either?

This type of query is more easily built using SQL View rather than design
view. But, let me give you the full SQL statement of the query and then
I'll
discuss how to build it in design view -- note that you didn't tell us
the
name of the second table, so I'm using a generic name (
WhateverTheNotherTableIsNamed ) for it in this statement:

SELECT [Salaries Register].GROSS,
(SELECT T.[Tax Amount]
FROM WhateverTheNotherTableIsNamed AS T
WHERE [Salaries Register].GROSS Between
T.PayRangeLow And T.PayRangeHigh) AS TaxAmount
FROM [Salaries Register];


To do this in design view, add the [Salaries Register] table to the grid.
Put the GROSS field on the grid.
Then, in the first empty column, put this entire expression in the Field:
box (be sure to replace the generic table name with the real name of your
other table):

TaxAmount: (SELECT T.[Tax Amount] FROM WhateverTheNotherTableIsNamed AS
T
WHERE [Salaries Register].GROSS Between T.PayRangeLow And T.PayRangeHigh)


An alternative way to do this query is to use the DLookup function for
the
TaxAmount field value:

SELECT [Salaries Register].GROSS,
DLookup("Tax Amount", "WhateverTheNotherTableIsNamed",
[Salaries Register].GROSS & " Between
[PayRangeLow] And [PayRangeHigh]") AS TaxAmount
FROM [Salaries Register];


In this example, the second column in design view would have this
expression
in the Field box (again, remember to replace the generic table name with
the
real name):

TaxAmount: DLookup("Tax Amount", "WhateverTheNotherTableIsNamed",
[Salaries
Register].GROSS & " Between [PayRangeLow] And [PayRangeHigh]")

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I don't know what my problem is. I post and then I can't find my
answers.
Anyhow, can someone please help.

I have two tables.1 - Salaries Register witha field named Gross. Table
2..Income Tax with fields named PayRangeLow, PayRangeHigh. Tax payable.

I want to run a query to have the Tax payable is the gross falls
between
the
PayRangeLow and PayRangeHigh.

I need detail help with the query, as far as what needs to be in the
fields
of the query..table name, criteria etc


PLEASE HELP


Thanks