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]")
