IIf statement Query

Evan · Dec 14, 2008

I have created a query on a table: USFed2008TaxRate&Amt and created the
following IIf statement expression for the field: TaxRate in that table based
on the table's Income field. But, I get a syntax error. The data type for
the Income field is currency and the data type for the TaxRate field is a
long integer number. Any ideas what I'm doing wrong?

TaxRate:
IIf([USFed2008TaxRate&Amt].[Income]>"$0"<="$8,025.00",[USFed2008TaxRate&Amt].[TaxRate]=10,
IIf([USFed2008TaxRate&Amt].[Income]>"$8,025.00"<="$32,550.00",[USFed2008TaxRate&Amt].[TaxRate]=15,
IIf([USFed2008TaxRate&Amt].[Income]>"$32,550.00"<="$78,850.00",[USFed2008TaxRate&Amt].[TaxRate]=25,
IIf([USFed2008TaxRate&Amt].[Income]>"$78,850.00"<="$164,550.00",[USFed2008TaxRate&Amt].[TaxRate]=28,
IIf([USFed2008TaxRate&Amt].[Income]>"$164,550.00"<="$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=33,
IIf([USFed2008TaxRate&Amt].[Income]>"$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=35))))))

John W. Vinson · Dec 14, 2008

I have created a query on a table: USFed2008TaxRate&Amt and created the
following IIf statement expression for the field: TaxRate in that table based
on the table's Income field. But, I get a syntax error. The data type for
the Income field is currency and the data type for the TaxRate field is a
long integer number. Any ideas what I'm doing wrong?

TaxRate:
IIf([USFed2008TaxRate&Amt].[Income]>"$0"<="$8,025.00",[USFed2008TaxRate&Amt].[TaxRate]=10,
IIf([USFed2008TaxRate&Amt].[Income]>"$8,025.00"<="$32,550.00",[USFed2008TaxRate&Amt].[TaxRate]=15,
IIf([USFed2008TaxRate&Amt].[Income]>"$32,550.00"<="$78,850.00",[USFed2008TaxRate&Amt].[TaxRate]=25,
IIf([USFed2008TaxRate&Amt].[Income]>"$78,850.00"<="$164,550.00",[USFed2008TaxRate&Amt].[TaxRate]=28,
IIf([USFed2008TaxRate&Amt].[Income]>"$164,550.00"<="$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=33,
IIf([USFed2008TaxRate&Amt].[Income]>"$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=35))))))

Several things. Currency values are not text strings; they are numbers. Use
just 8025 rather than "$8,025.00". Secondly, though it's legal, it's better
not to use deeply nested IIF's - the expression can become too complex to
handle, and will not be efficient. Thirdly, IIF has three operands not two.
Your final IIF doesn't have a False operand. Finally, if you embed this
calculation in a complex expression, it'll be really hard to update when the
rates change (which they do every year).

Better would be to have a TaxRates table with three fields - Low, High,
TaxRate. Use a Query joining this table to your income table to determine the
rate:

SELECT [USFed2008TaxRate&Amt].[Income], [TaxRates].[TaxRate], <other fields>
FROM ([USFed2008TaxRate&Amt] INNER JOIN [TaxRates]
ON ([USFed2008TaxRate&Amt].[Income] >= [TaxRates].[Low]
AND ([USFed2008TaxRate&Amt].[Income] < [TaxRates].[High]);

Evan · Dec 15, 2008

Thanks so much, but when I ran this I got a syntax error to enclose the
subquery in parenthesis.

John W. Vinson said:
I have created a query on a table: USFed2008TaxRate&Amt and created the
following IIf statement expression for the field: TaxRate in that table based
on the table's Income field. But, I get a syntax error. The data type for
the Income field is currency and the data type for the TaxRate field is a
long integer number. Any ideas what I'm doing wrong?

TaxRate:
IIf([USFed2008TaxRate&Amt].[Income]>"$0"<="$8,025.00",[USFed2008TaxRate&Amt].[TaxRate]=10,
IIf([USFed2008TaxRate&Amt].[Income]>"$8,025.00"<="$32,550.00",[USFed2008TaxRate&Amt].[TaxRate]=15,
IIf([USFed2008TaxRate&Amt].[Income]>"$32,550.00"<="$78,850.00",[USFed2008TaxRate&Amt].[TaxRate]=25,
IIf([USFed2008TaxRate&Amt].[Income]>"$78,850.00"<="$164,550.00",[USFed2008TaxRate&Amt].[TaxRate]=28,
IIf([USFed2008TaxRate&Amt].[Income]>"$164,550.00"<="$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=33,
IIf([USFed2008TaxRate&Amt].[Income]>"$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=35))))))

Click to expand...

Several things. Currency values are not text strings; they are numbers. Use
just 8025 rather than "$8,025.00". Secondly, though it's legal, it's better
not to use deeply nested IIF's - the expression can become too complex to
handle, and will not be efficient. Thirdly, IIF has three operands not two.
Your final IIF doesn't have a False operand. Finally, if you embed this
calculation in a complex expression, it'll be really hard to update when the
rates change (which they do every year).

Better would be to have a TaxRates table with three fields - Low, High,
TaxRate. Use a Query joining this table to your income table to determine the
rate:

SELECT [USFed2008TaxRate&Amt].[Income], [TaxRates].[TaxRate], <other fields>
FROM ([USFed2008TaxRate&Amt] INNER JOIN [TaxRates]
ON ([USFed2008TaxRate&Amt].[Income] >= [TaxRates].[Low]
AND ([USFed2008TaxRate&Amt].[Income] < [TaxRates].[High]);

John W. Vinson · Dec 15, 2008

Thanks so much, but when I ran this I got a syntax error to enclose the
subquery in parenthesis.

Looks like I put in extra parenthises. There's no subquery involved here, just
a non-equi join:

SELECT [USFed2008TaxRate&Amt].[Income], [TaxRates].[TaxRate], <other fields>
FROM [USFed2008TaxRate&Amt] INNER JOIN [TaxRates]
ON [USFed2008TaxRate&Amt].[Income] >= [TaxRates].[Low]
AND [USFed2008TaxRate&Amt].[Income] < [TaxRates].[High];

Evan · Dec 15, 2008

John once again thanks for your help, but I still get the same error message:
"check the subquery syntax and enclose the subquery in parenthesis." Any
idea what the matter may be?
Please note that I have put in the query field "TaxRate:" in front of
your SELECT statement.

John W. Vinson said:
Thanks so much, but when I ran this I got a syntax error to enclose the
subquery in parenthesis.

Click to expand...

Looks like I put in extra parenthises. There's no subquery involved here, just
a non-equi join:

SELECT [USFed2008TaxRate&Amt].[Income], [TaxRates].[TaxRate], <other fields>
FROM [USFed2008TaxRate&Amt] INNER JOIN [TaxRates]
ON [USFed2008TaxRate&Amt].[Income] >= [TaxRates].[Low]
AND [USFed2008TaxRate&Amt].[Income] < [TaxRates].[High];

Evan · Dec 15, 2008

John do I need the brackets? Saw several other select statements without
the use of brackets.

Evan said:
John once again thanks for your help, but I still get the same error message:
"check the subquery syntax and enclose the subquery in parenthesis." Any
idea what the matter may be?
Please note that I have put in the query field "TaxRate:" in front of
your SELECT statement.

John W. Vinson said:

Thanks so much, but when I ran this I got a syntax error to enclose the
subquery in parenthesis.

Click to expand...

Looks like I put in extra parenthises. There's no subquery involved here, just
a non-equi join:

SELECT [USFed2008TaxRate&Amt].[Income], [TaxRates].[TaxRate], <other fields>
FROM [USFed2008TaxRate&Amt] INNER JOIN [TaxRates]
ON [USFed2008TaxRate&Amt].[Income] >= [TaxRates].[Low]
AND [USFed2008TaxRate&Amt].[Income] < [TaxRates].[High];

Click to expand...

John W. Vinson · Dec 15, 2008

John once again thanks for your help, but I still get the same error message:
"check the subquery syntax and enclose the subquery in parenthesis." Any
idea what the matter may be?
Please note that I have put in the query field "TaxRate:" in front of
your SELECT statement.

Please post your entire SQL and the definitions of your tables (particulary
the TaxRate table); indicate each table's primary key. I don't know what might
be wrong given what you've posted.

John Spencer · Dec 15, 2008

John (and Evan),

I believe Evan is putting the SQL you have posted into a calculated column and
that is causing the problems.

Field: TaxRate: (SELECT [USFed2008TaxRate&Amt].[Income], [TaxRates].[TaxRate],
<other fields>
FROM [USFed2008TaxRate&Amt] INNER JOIN [TaxRates]
ON [USFed2008TaxRate&Amt].[Income] >= [TaxRates].[Low]
AND [USFed2008TaxRate&Amt].[Income] < [TaxRates].[High])

Evan, if the above is the case then you need a query more like the following.

Field: TaxRate: (SELECT First([TaxRate])
FROM [TaxRates]
WHERE [USFed2008TaxRate&Amt].[Income] >= [TaxRates].[Low]
AND [USFed2008TaxRate&Amt].[Income] < [TaxRates].[High])

Or you could use the DLookup function (probably slower than the above):

Field: TaxRate: DLookup("TaxRate","TaxRates",[income] & ">=Low and " &
[Income] & "<High")

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

Dale Fye · Dec 15, 2008

Although John's method provides more long-term maintainability (much easier
to change values in a table than in code), another way to address the issue
of multiple IIF statements is with the Switch( ) function. This function
accepts an array of values, where the odd elements of the array are
expressions to be evaluated, and the even elements of the array are the value
to be returned if the previous expression evaluates to true. The function
returns the value associated with the first expression that is true. Your
example assumes that [Income] will never be less than zero, but doesn't
account for that instance, so I've added a row to acccomodate that
possibility. In your case, you could write this something like:

TaxRate: Switch([Income] <= 0, 0,
[Income] <= 8025, 10,
[Income] <= 32550, 15,
[Income] <= 78855, 25,
[Income] <= 164550, 28,
[Income] <= 357000, 33,
[Income] > 357000, 35,
True, 99)

I usually use the final set of parameters to make sure that a specific value
is returned. To do this, I set the last expression to be evaluated to be
True, ensuring that a value is returned. This gives me the ability to test
to see whether [Income] was actually evaluated properly.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Dale Fye · Dec 15, 2008

Another method, this one uses Johns TaxRates table, but doesn't require his
JOIN would be to use the DMIN domain function to get the minimum tax rate
from the tax rate table where the value of the ranges upper bound ([High]) is
greater than the persons [Income].

TaxRate: DMIN("TaxRate", "TaxRates", "[High] > " & [Income])

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Evan · Dec 15, 2008

Thanks so much. The DMIN function in my query works well. I also need to
include the calculated tax amount based on the Income & Tax Rate fields in
the query and a BaseTax field from a table: USFedTaxSchedule that includes
the Low, High, BaseTax and TaxRate fields.

I tried to use the DMIN function to bring the Base Tax into the query just
as was done with the TaxRate and then nest it in a Sum() function s such:

TaxAmount: SUM(DMIN("BaseTax", "USFedTaxSchedule", "[High] > " & [Income]) +
([TaxRate] * [Income] - [USFedTaxSchedule].[Low]))

I get an error: you tried to execute a query that does not include the
specified expression 'Income' as part of an aggregate function. Can someone
help with this please. Thanks

Dale Fye said:
Another method, this one uses Johns TaxRates table, but doesn't require his
JOIN would be to use the DMIN domain function to get the minimum tax rate
from the tax rate table where the value of the ranges upper bound ([High]) is
greater than the persons [Income].

TaxRate: DMIN("TaxRate", "TaxRates", "[High] > " & [Income])

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Evan said:

I have created a query on a table: USFed2008TaxRate&Amt and created the
following IIf statement expression for the field: TaxRate in that table based
on the table's Income field. But, I get a syntax error. The data type for
the Income field is currency and the data type for the TaxRate field is a
long integer number. Any ideas what I'm doing wrong?

TaxRate:
IIf([USFed2008TaxRate&Amt].[Income]>"$0"<="$8,025.00",[USFed2008TaxRate&Amt].[TaxRate]=10,
IIf([USFed2008TaxRate&Amt].[Income]>"$8,025.00"<="$32,550.00",[USFed2008TaxRate&Amt].[TaxRate]=15,
IIf([USFed2008TaxRate&Amt].[Income]>"$32,550.00"<="$78,850.00",[USFed2008TaxRate&Amt].[TaxRate]=25,
IIf([USFed2008TaxRate&Amt].[Income]>"$78,850.00"<="$164,550.00",[USFed2008TaxRate&Amt].[TaxRate]=28,
IIf([USFed2008TaxRate&Amt].[Income]>"$164,550.00"<="$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=33,
IIf([USFed2008TaxRate&Amt].[Income]>"$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=35))))))

Click to expand...

Dale Fye · Dec 16, 2008

Evan,

Would need to see the entire SQL statement to make sense out of this.

Dale

Evan said:
Thanks so much. The DMIN function in my query works well. I also need
to
include the calculated tax amount based on the Income & Tax Rate fields in
the query and a BaseTax field from a table: USFedTaxSchedule that includes
the Low, High, BaseTax and TaxRate fields.

I tried to use the DMIN function to bring the Base Tax into the query
just
as was done with the TaxRate and then nest it in a Sum() function s such:

TaxAmount: SUM(DMIN("BaseTax", "USFedTaxSchedule", "[High] > " & [Income])
+
([TaxRate] * [Income] - [USFedTaxSchedule].[Low]))

I get an error: you tried to execute a query that does not include the
specified expression 'Income' as part of an aggregate function. Can
someone
help with this please. Thanks

Dale Fye said:

Another method, this one uses Johns TaxRates table, but doesn't require
his
JOIN would be to use the DMIN domain function to get the minimum tax rate
from the tax rate table where the value of the ranges upper bound
([High]) is
greater than the persons [Income].

TaxRate: DMIN("TaxRate", "TaxRates", "[High] > " & [Income])

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Evan said:

I have created a query on a table: USFed2008TaxRate&Amt and created the
following IIf statement expression for the field: TaxRate in that table
based
on the table's Income field. But, I get a syntax error. The data type
for
the Income field is currency and the data type for the TaxRate field is
a
long integer number. Any ideas what I'm doing wrong?

TaxRate:
IIf([USFed2008TaxRate&Amt].[Income]>"$0"<="$8,025.00",[USFed2008TaxRate&Amt].[TaxRate]=10,
IIf([USFed2008TaxRate&Amt].[Income]>"$8,025.00"<="$32,550.00",[USFed2008TaxRate&Amt].[TaxRate]=15,
IIf([USFed2008TaxRate&Amt].[Income]>"$32,550.00"<="$78,850.00",[USFed2008TaxRate&Amt].[TaxRate]=25,
IIf([USFed2008TaxRate&Amt].[Income]>"$78,850.00"<="$164,550.00",[USFed2008TaxRate&Amt].[TaxRate]=28,
IIf([USFed2008TaxRate&Amt].[Income]>"$164,550.00"<="$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=33,
IIf([USFed2008TaxRate&Amt].[Income]>"$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=35))))))

Click to expand...

Click to expand...

Evan · Dec 16, 2008

Dale,

The SQL statement for the query is:

SELECT TaxPayerIncome.Income, DMin("TaxRate","USFedTaxSchedule","[High] > "
& [Income]) AS TaxRate, DMin("BaseTax","USFedTaxSchedule","[High] > " &
[Income]) AS BaseTax
FROM TaxPayerIncome, USFedTaxSchedule
ORDER BY TaxPayerIncome.Income, DMin("TaxRate","USFedTaxSchedule","[High] >
" & [Income]);

Evan

Dale Fye said:
Evan,

Would need to see the entire SQL statement to make sense out of this.

Dale

Evan said:

Thanks so much. The DMIN function in my query works well. I also need
to
include the calculated tax amount based on the Income & Tax Rate fields in
the query and a BaseTax field from a table: USFedTaxSchedule that includes
the Low, High, BaseTax and TaxRate fields.

I tried to use the DMIN function to bring the Base Tax into the query
just
as was done with the TaxRate and then nest it in a Sum() function s such:

TaxAmount: SUM(DMIN("BaseTax", "USFedTaxSchedule", "[High] > " & [Income])
+
([TaxRate] * [Income] - [USFedTaxSchedule].[Low]))

I get an error: you tried to execute a query that does not include the
specified expression 'Income' as part of an aggregate function. Can
someone
help with this please. Thanks

Dale Fye said:

Another method, this one uses Johns TaxRates table, but doesn't require
his
JOIN would be to use the DMIN domain function to get the minimum tax rate
from the tax rate table where the value of the ranges upper bound
([High]) is
greater than the persons [Income].

TaxRate: DMIN("TaxRate", "TaxRates", "[High] > " & [Income])

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

:

I have created a query on a table: USFed2008TaxRate&Amt and created the
following IIf statement expression for the field: TaxRate in that table
based
on the table's Income field. But, I get a syntax error. The data type
for
the Income field is currency and the data type for the TaxRate field is
a
long integer number. Any ideas what I'm doing wrong?

TaxRate:
IIf([USFed2008TaxRate&Amt].[Income]>"$0"<="$8,025.00",[USFed2008TaxRate&Amt].[TaxRate]=10,
IIf([USFed2008TaxRate&Amt].[Income]>"$8,025.00"<="$32,550.00",[USFed2008TaxRate&Amt].[TaxRate]=15,
IIf([USFed2008TaxRate&Amt].[Income]>"$32,550.00"<="$78,850.00",[USFed2008TaxRate&Amt].[TaxRate]=25,
IIf([USFed2008TaxRate&Amt].[Income]>"$78,850.00"<="$164,550.00",[USFed2008TaxRate&Amt].[TaxRate]=28,
IIf([USFed2008TaxRate&Amt].[Income]>"$164,550.00"<="$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=33,
IIf([USFed2008TaxRate&Amt].[Income]>"$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=35))))))

Click to expand...

Click to expand...

Dale Fye · Dec 16, 2008

Evan,

A couple of problems with this.

1. When you include the TaxPayerIncome and USFedTaxSchedule tables in your
query, without a JOIN clause, you get what is known as a Cartesian Join
(every record in Table1 mapped to every record in Table2). You can restrict
that by adding a JOIN clause, to "join" the two tables on a common field, or
in a more complex join (as in John's example) in a non-equi join. This
latter type of join must be created in the SQL view, as it cannot be depicted
in the query design grid. Another way to restrict the cartesian joins result
set is to include a WHERE clause that limits the result set to those where a
relationship of interest exists between the two tables.

2. You don't need to use the SUM( ) because you are trying to add the
values from multiple fields in the same record, not across multiple records.

Assuming that the table structure fo your [USFedTaxSchedule] table looks
something like: [BaseTax], [Low], [High], [TaxRate], I would recommend
getting rid of the DMIN( ) domain functions, and going with something like
the following (which probably looks very similar to John's original post).

SELECT TPI.Income,
TaxSch.TaxRate,
TaxSch.BaseTax,
TaxSch.BaseTax + (TaxSch.TaxRate * (TPI.Income - TaxSch.Low)) as
TaxAmount
FROM TaxPayerIncome as TPI, USFedTaxSched as TaxSch
WHERE TaxSch.High > TPI.[Income]
AND TaxSch.Low <= TPI.[Income]
ORDER BY TPI.Income, TaxSch.TaxRate

You could also write this as shown below, but as I indicated above, this
type of join can only be written in the SQL view.

SELECT TPI.Income,
TaxSch.TaxRate,
TaxSch.BaseTax,
TaxSch.BaseTax + (TaxSch.TaxRate * (TPI.Income - TaxSch.Low)) as
TaxAmount
FROM TaxPayerIncome as TPI
INNER JOIN USFedTaxSched as TaxSch
ON TaxSch.High > TPI.[Income] AND TaxSch.Low <= TPI.[Income]
ORDER BY TPI.Income, TaxSch.TaxRate

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Evan said:
Dale,

The SQL statement for the query is:

SELECT TaxPayerIncome.Income, DMin("TaxRate","USFedTaxSchedule","[High] > "
& [Income]) AS TaxRate, DMin("BaseTax","USFedTaxSchedule","[High] > " &
[Income]) AS BaseTax
FROM TaxPayerIncome, USFedTaxSchedule
ORDER BY TaxPayerIncome.Income, DMin("TaxRate","USFedTaxSchedule","[High] >
" & [Income]);

Evan

Dale Fye said:

Evan,

Would need to see the entire SQL statement to make sense out of this.

Dale

Evan said:

Thanks so much. The DMIN function in my query works well. I also need
to
include the calculated tax amount based on the Income & Tax Rate fields in
the query and a BaseTax field from a table: USFedTaxSchedule that includes
the Low, High, BaseTax and TaxRate fields.

I tried to use the DMIN function to bring the Base Tax into the query
just
as was done with the TaxRate and then nest it in a Sum() function s such:

TaxAmount: SUM(DMIN("BaseTax", "USFedTaxSchedule", "[High] > " & [Income])
+
([TaxRate] * [Income] - [USFedTaxSchedule].[Low]))

I get an error: you tried to execute a query that does not include the
specified expression 'Income' as part of an aggregate function. Can
someone
help with this please. Thanks

:

Another method, this one uses Johns TaxRates table, but doesn't require
his
JOIN would be to use the DMIN domain function to get the minimum tax rate
from the tax rate table where the value of the ranges upper bound
([High]) is
greater than the persons [Income].

TaxRate: DMIN("TaxRate", "TaxRates", "[High] > " & [Income])

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

:

I have created a query on a table: USFed2008TaxRate&Amt and created the
following IIf statement expression for the field: TaxRate in that table
based
on the table's Income field. But, I get a syntax error. The data type
for
the Income field is currency and the data type for the TaxRate field is
a
long integer number. Any ideas what I'm doing wrong?

TaxRate:
IIf([USFed2008TaxRate&Amt].[Income]>"$0"<="$8,025.00",[USFed2008TaxRate&Amt].[TaxRate]=10,
IIf([USFed2008TaxRate&Amt].[Income]>"$8,025.00"<="$32,550.00",[USFed2008TaxRate&Amt].[TaxRate]=15,
IIf([USFed2008TaxRate&Amt].[Income]>"$32,550.00"<="$78,850.00",[USFed2008TaxRate&Amt].[TaxRate]=25,
IIf([USFed2008TaxRate&Amt].[Income]>"$78,850.00"<="$164,550.00",[USFed2008TaxRate&Amt].[TaxRate]=28,
IIf([USFed2008TaxRate&Amt].[Income]>"$164,550.00"<="$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=33,
IIf([USFed2008TaxRate&Amt].[Income]>"$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=35))))))

Click to expand...

Click to expand...

Evan · Dec 16, 2008

Dale, thanks so much. Everything works great!!

Evan

Dale Fye said:
Evan,

A couple of problems with this.

1. When you include the TaxPayerIncome and USFedTaxSchedule tables in your
query, without a JOIN clause, you get what is known as a Cartesian Join
(every record in Table1 mapped to every record in Table2). You can restrict
that by adding a JOIN clause, to "join" the two tables on a common field, or
in a more complex join (as in John's example) in a non-equi join. This
latter type of join must be created in the SQL view, as it cannot be depicted
in the query design grid. Another way to restrict the cartesian joins result
set is to include a WHERE clause that limits the result set to those where a
relationship of interest exists between the two tables.

2. You don't need to use the SUM( ) because you are trying to add the
values from multiple fields in the same record, not across multiple records.

Assuming that the table structure fo your [USFedTaxSchedule] table looks
something like: [BaseTax], [Low], [High], [TaxRate], I would recommend
getting rid of the DMIN( ) domain functions, and going with something like
the following (which probably looks very similar to John's original post).

SELECT TPI.Income,
TaxSch.TaxRate,
TaxSch.BaseTax,
TaxSch.BaseTax + (TaxSch.TaxRate * (TPI.Income - TaxSch.Low)) as
TaxAmount
FROM TaxPayerIncome as TPI, USFedTaxSched as TaxSch
WHERE TaxSch.High > TPI.[Income]
AND TaxSch.Low <= TPI.[Income]
ORDER BY TPI.Income, TaxSch.TaxRate

You could also write this as shown below, but as I indicated above, this
type of join can only be written in the SQL view.

SELECT TPI.Income,
TaxSch.TaxRate,
TaxSch.BaseTax,
TaxSch.BaseTax + (TaxSch.TaxRate * (TPI.Income - TaxSch.Low)) as
TaxAmount
FROM TaxPayerIncome as TPI
INNER JOIN USFedTaxSched as TaxSch
ON TaxSch.High > TPI.[Income] AND TaxSch.Low <= TPI.[Income]
ORDER BY TPI.Income, TaxSch.TaxRate

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Evan said:

Dale,

The SQL statement for the query is:

SELECT TaxPayerIncome.Income, DMin("TaxRate","USFedTaxSchedule","[High] > "
& [Income]) AS TaxRate, DMin("BaseTax","USFedTaxSchedule","[High] > " &
[Income]) AS BaseTax
FROM TaxPayerIncome, USFedTaxSchedule
ORDER BY TaxPayerIncome.Income, DMin("TaxRate","USFedTaxSchedule","[High] >
" & [Income]);

Evan

Dale Fye said:

Evan,

Would need to see the entire SQL statement to make sense out of this.

Dale

Thanks so much. The DMIN function in my query works well. I also need
to
include the calculated tax amount based on the Income & Tax Rate fields in
the query and a BaseTax field from a table: USFedTaxSchedule that includes
the Low, High, BaseTax and TaxRate fields.

I tried to use the DMIN function to bring the Base Tax into the query
just
as was done with the TaxRate and then nest it in a Sum() function s such:

TaxAmount: SUM(DMIN("BaseTax", "USFedTaxSchedule", "[High] > " & [Income])
+
([TaxRate] * [Income] - [USFedTaxSchedule].[Low]))

I get an error: you tried to execute a query that does not include the
specified expression 'Income' as part of an aggregate function. Can
someone
help with this please. Thanks

:

Another method, this one uses Johns TaxRates table, but doesn't require
his
JOIN would be to use the DMIN domain function to get the minimum tax rate
from the tax rate table where the value of the ranges upper bound
([High]) is
greater than the persons [Income].

TaxRate: DMIN("TaxRate", "TaxRates", "[High] > " & [Income])

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

:

I have created a query on a table: USFed2008TaxRate&Amt and created the
following IIf statement expression for the field: TaxRate in that table
based
on the table's Income field. But, I get a syntax error. The data type
for
the Income field is currency and the data type for the TaxRate field is
a
long integer number. Any ideas what I'm doing wrong?

TaxRate:
IIf([USFed2008TaxRate&Amt].[Income]>"$0"<="$8,025.00",[USFed2008TaxRate&Amt].[TaxRate]=10,
IIf([USFed2008TaxRate&Amt].[Income]>"$8,025.00"<="$32,550.00",[USFed2008TaxRate&Amt].[TaxRate]=15,
IIf([USFed2008TaxRate&Amt].[Income]>"$32,550.00"<="$78,850.00",[USFed2008TaxRate&Amt].[TaxRate]=25,
IIf([USFed2008TaxRate&Amt].[Income]>"$78,850.00"<="$164,550.00",[USFed2008TaxRate&Amt].[TaxRate]=28,
IIf([USFed2008TaxRate&Amt].[Income]>"$164,550.00"<="$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=33,
IIf([USFed2008TaxRate&Amt].[Income]>"$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=35))))))

Click to expand...

Click to expand...

Evan · Dec 19, 2008

I have constructed 6 queries of various country's tax schedules. The queries
include the fields: CountryID as FK, Income, TaxRate, TaxAmount. I would
like to create another query that combines these queries with a Country table
with fields: CountryID as PK and CountryName. Here's what I have but getting
error messages:

SELECT C.CountryName, TaxRate, TaxAmount
FROM Country as C,
[BRFdRate&Amt].TaxRate as TaxRate,
[CAFdRate&Amt].TaxRate as TaxRate,
[GEFdRate&Amt].TaxRate as TaxRate,
[SPFdRate&Amt].TaxRate as TaxRate,
[UKFdRate&Amt].TaxRate as TaxRate,
[USFdRate&Amt].TaxRate as TaxRate,
[BRFdRate&Amt].TaxAmount as TaxAmount,
[CAFdRate&Amt].TaxAmount as TaxAmount,
[GEFdRate&Amt].TaxAmount as TaxAmount,
[SPFdRate&Amt].TaxAmount as TaxAmount,
[UKFdRate&Amt].TaxAmount as TaxAmount,
[USFdRate&Amt].TaxAmount as TaxAmount,
((((( Country INNER JOIN [BRFdRate&Amt] ON Country.CountryID =
[BRFdRate&Amt].CountryID) INNER JOIN [CAFdRate&Amt] ON Country.CountryID =
[CAFdRate&Amt].CountryID) INNER JOIN [GEFdRate&Amt] ON Country.CountryID =
[GEFdRate&Amt].CountryID) INNER JOIN [SPFdRate&Amt] ON Country.CountryID =
[SPFdRate&Amt].CountryID) INNER JOIN [UKFdRate&Amt] ON Country.CountryID =
[UKFdRate&Amt].CountryID) INNER JOIN [USFdRate&Amt] ON Country.CountryID =
[USFdRate&Amt].CountryID),
WHERE (([Income]=75000));
ORDER BY TaxAmount;

Any help is greatly appreciated. Thanks Evan

Dale Fye said:
Evan,

A couple of problems with this.

1. When you include the TaxPayerIncome and USFedTaxSchedule tables in your
query, without a JOIN clause, you get what is known as a Cartesian Join
(every record in Table1 mapped to every record in Table2). You can restrict
that by adding a JOIN clause, to "join" the two tables on a common field, or
in a more complex join (as in John's example) in a non-equi join. This
latter type of join must be created in the SQL view, as it cannot be depicted
in the query design grid. Another way to restrict the cartesian joins result
set is to include a WHERE clause that limits the result set to those where a
relationship of interest exists between the two tables.

2. You don't need to use the SUM( ) because you are trying to add the
values from multiple fields in the same record, not across multiple records.

Assuming that the table structure fo your [USFedTaxSchedule] table looks
something like: [BaseTax], [Low], [High], [TaxRate], I would recommend
getting rid of the DMIN( ) domain functions, and going with something like
the following (which probably looks very similar to John's original post).

SELECT TPI.Income,
TaxSch.TaxRate,
TaxSch.BaseTax,
TaxSch.BaseTax + (TaxSch.TaxRate * (TPI.Income - TaxSch.Low)) as
TaxAmount
FROM TaxPayerIncome as TPI, USFedTaxSched as TaxSch
WHERE TaxSch.High > TPI.[Income]
AND TaxSch.Low <= TPI.[Income]
ORDER BY TPI.Income, TaxSch.TaxRate

You could also write this as shown below, but as I indicated above, this
type of join can only be written in the SQL view.

SELECT TPI.Income,
TaxSch.TaxRate,
TaxSch.BaseTax,
TaxSch.BaseTax + (TaxSch.TaxRate * (TPI.Income - TaxSch.Low)) as
TaxAmount
FROM TaxPayerIncome as TPI
INNER JOIN USFedTaxSched as TaxSch
ON TaxSch.High > TPI.[Income] AND TaxSch.Low <= TPI.[Income]
ORDER BY TPI.Income, TaxSch.TaxRate

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Evan said:

Dale,

The SQL statement for the query is:

SELECT TaxPayerIncome.Income, DMin("TaxRate","USFedTaxSchedule","[High] > "
& [Income]) AS TaxRate, DMin("BaseTax","USFedTaxSchedule","[High] > " &
[Income]) AS BaseTax
FROM TaxPayerIncome, USFedTaxSchedule
ORDER BY TaxPayerIncome.Income, DMin("TaxRate","USFedTaxSchedule","[High] >
" & [Income]);

Evan

Dale Fye said:

Evan,

Would need to see the entire SQL statement to make sense out of this.

Dale

Thanks so much. The DMIN function in my query works well. I also need
to
include the calculated tax amount based on the Income & Tax Rate fields in
the query and a BaseTax field from a table: USFedTaxSchedule that includes
the Low, High, BaseTax and TaxRate fields.

I tried to use the DMIN function to bring the Base Tax into the query
just
as was done with the TaxRate and then nest it in a Sum() function s such:

TaxAmount: SUM(DMIN("BaseTax", "USFedTaxSchedule", "[High] > " & [Income])
+
([TaxRate] * [Income] - [USFedTaxSchedule].[Low]))

I get an error: you tried to execute a query that does not include the
specified expression 'Income' as part of an aggregate function. Can
someone
help with this please. Thanks

:

Another method, this one uses Johns TaxRates table, but doesn't require
his
JOIN would be to use the DMIN domain function to get the minimum tax rate
from the tax rate table where the value of the ranges upper bound
([High]) is
greater than the persons [Income].

TaxRate: DMIN("TaxRate", "TaxRates", "[High] > " & [Income])

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

:

I have created a query on a table: USFed2008TaxRate&Amt and created the
following IIf statement expression for the field: TaxRate in that table
based
on the table's Income field. But, I get a syntax error. The data type
for
the Income field is currency and the data type for the TaxRate field is
a
long integer number. Any ideas what I'm doing wrong?

TaxRate:
IIf([USFed2008TaxRate&Amt].[Income]>"$0"<="$8,025.00",[USFed2008TaxRate&Amt].[TaxRate]=10,
IIf([USFed2008TaxRate&Amt].[Income]>"$8,025.00"<="$32,550.00",[USFed2008TaxRate&Amt].[TaxRate]=15,
IIf([USFed2008TaxRate&Amt].[Income]>"$32,550.00"<="$78,850.00",[USFed2008TaxRate&Amt].[TaxRate]=25,
IIf([USFed2008TaxRate&Amt].[Income]>"$78,850.00"<="$164,550.00",[USFed2008TaxRate&Amt].[TaxRate]=28,
IIf([USFed2008TaxRate&Amt].[Income]>"$164,550.00"<="$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=33,
IIf([USFed2008TaxRate&Amt].[Income]>"$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=35))))))

Click to expand...

Click to expand...

Dale Fye · Dec 19, 2008

Evan,

What is the purpose of this query?

If you need to create a query, that returns all of these countries data in a
single query, then I think what you need is a union query. Something like:

SELECT CountryName, TaxRate, TaxAmount
FROM [BRFdRate&Amt]
WHERE [Income] = 75000
UNION ALL
SELECT CountryName, TaxRate, TaxAmount
FROM [CAFdRate&Amt]
WHERE [Income] = 75000
UNION ALL
SELECT CountryName, TaxRate, TaxAmount
FROM [GEFdRate&Amt]
WHERE [Income] = 75000
....

BTW, I would avoid special characters in field names. The only
non-alphanumeric I ever use in a field name is an underscore.

HTH
Dale

Evan said:
I have constructed 6 queries of various country's tax schedules. The
queries
include the fields: CountryID as FK, Income, TaxRate, TaxAmount. I
would
like to create another query that combines these queries with a Country
table
with fields: CountryID as PK and CountryName. Here's what I have but
getting
error messages:

SELECT C.CountryName, TaxRate, TaxAmount
FROM Country as C,
[BRFdRate&Amt].TaxRate as TaxRate,
[CAFdRate&Amt].TaxRate as TaxRate,
[GEFdRate&Amt].TaxRate as TaxRate,
[SPFdRate&Amt].TaxRate as TaxRate,
[UKFdRate&Amt].TaxRate as TaxRate,
[USFdRate&Amt].TaxRate as TaxRate,
[BRFdRate&Amt].TaxAmount as TaxAmount,
[CAFdRate&Amt].TaxAmount as TaxAmount,
[GEFdRate&Amt].TaxAmount as TaxAmount,
[SPFdRate&Amt].TaxAmount as TaxAmount,
[UKFdRate&Amt].TaxAmount as TaxAmount,
[USFdRate&Amt].TaxAmount as TaxAmount,
((((( Country INNER JOIN [BRFdRate&Amt] ON Country.CountryID =
[BRFdRate&Amt].CountryID) INNER JOIN [CAFdRate&Amt] ON Country.CountryID =
[CAFdRate&Amt].CountryID) INNER JOIN [GEFdRate&Amt] ON Country.CountryID =
[GEFdRate&Amt].CountryID) INNER JOIN [SPFdRate&Amt] ON Country.CountryID =
[SPFdRate&Amt].CountryID) INNER JOIN [UKFdRate&Amt] ON Country.CountryID =
[UKFdRate&Amt].CountryID) INNER JOIN [USFdRate&Amt] ON Country.CountryID =
[USFdRate&Amt].CountryID),
WHERE (([Income]=75000));
ORDER BY TaxAmount;

Any help is greatly appreciated. Thanks Evan

Dale Fye said:

Evan,

A couple of problems with this.

1. When you include the TaxPayerIncome and USFedTaxSchedule tables in
your
query, without a JOIN clause, you get what is known as a Cartesian Join
(every record in Table1 mapped to every record in Table2). You can
restrict
that by adding a JOIN clause, to "join" the two tables on a common field,
or
in a more complex join (as in John's example) in a non-equi join. This
latter type of join must be created in the SQL view, as it cannot be
depicted
in the query design grid. Another way to restrict the cartesian joins
result
set is to include a WHERE clause that limits the result set to those
where a
relationship of interest exists between the two tables.

2. You don't need to use the SUM( ) because you are trying to add the
values from multiple fields in the same record, not across multiple
records.

Assuming that the table structure fo your [USFedTaxSchedule] table looks
something like: [BaseTax], [Low], [High], [TaxRate], I would recommend
getting rid of the DMIN( ) domain functions, and going with something
like
the following (which probably looks very similar to John's original
post).

SELECT TPI.Income,
TaxSch.TaxRate,
TaxSch.BaseTax,
TaxSch.BaseTax + (TaxSch.TaxRate * (TPI.Income - TaxSch.Low))
as
TaxAmount
FROM TaxPayerIncome as TPI, USFedTaxSched as TaxSch
WHERE TaxSch.High > TPI.[Income]
AND TaxSch.Low <= TPI.[Income]
ORDER BY TPI.Income, TaxSch.TaxRate

You could also write this as shown below, but as I indicated above, this
type of join can only be written in the SQL view.

SELECT TPI.Income,
TaxSch.TaxRate,
TaxSch.BaseTax,
TaxSch.BaseTax + (TaxSch.TaxRate * (TPI.Income - TaxSch.Low))
as
TaxAmount
FROM TaxPayerIncome as TPI
INNER JOIN USFedTaxSched as TaxSch
ON TaxSch.High > TPI.[Income] AND TaxSch.Low <= TPI.[Income]
ORDER BY TPI.Income, TaxSch.TaxRate

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Evan said:

Dale,

The SQL statement for the query is:

SELECT TaxPayerIncome.Income, DMin("TaxRate","USFedTaxSchedule","[High]
"
& [Income]) AS TaxRate, DMin("BaseTax","USFedTaxSchedule","[High] > " &
[Income]) AS BaseTax
FROM TaxPayerIncome, USFedTaxSchedule
ORDER BY TaxPayerIncome.Income,
DMin("TaxRate","USFedTaxSchedule","[High] >
" & [Income]);

Evan

:

Evan,

Would need to see the entire SQL statement to make sense out of this.

Dale

Thanks so much. The DMIN function in my query works well. I also
need
to
include the calculated tax amount based on the Income & Tax Rate
fields in
the query and a BaseTax field from a table: USFedTaxSchedule that
includes
the Low, High, BaseTax and TaxRate fields.

I tried to use the DMIN function to bring the Base Tax into the
query
just
as was done with the TaxRate and then nest it in a Sum() function s
such:

TaxAmount: SUM(DMIN("BaseTax", "USFedTaxSchedule", "[High] > " &
[Income])
+
([TaxRate] * [Income] - [USFedTaxSchedule].[Low]))

I get an error: you tried to execute a query that does not include
the
specified expression 'Income' as part of an aggregate function.
Can
someone
help with this please. Thanks

:

Another method, this one uses Johns TaxRates table, but doesn't
require
his
JOIN would be to use the DMIN domain function to get the minimum
tax rate
from the tax rate table where the value of the ranges upper bound
([High]) is
greater than the persons [Income].

TaxRate: DMIN("TaxRate", "TaxRates", "[High] > " & [Income])

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

:

I have created a query on a table: USFed2008TaxRate&Amt and
created the
following IIf statement expression for the field: TaxRate in
that table
based
on the table's Income field. But, I get a syntax error. The
data type
for
the Income field is currency and the data type for the TaxRate
field is
a
long integer number. Any ideas what I'm doing wrong?

TaxRate:
IIf([USFed2008TaxRate&Amt].[Income]>"$0"<="$8,025.00",[USFed2008TaxRate&Amt].[TaxRate]=10,
IIf([USFed2008TaxRate&Amt].[Income]>"$8,025.00"<="$32,550.00",[USFed2008TaxRate&Amt].[TaxRate]=15,
IIf([USFed2008TaxRate&Amt].[Income]>"$32,550.00"<="$78,850.00",[USFed2008TaxRate&Amt].[TaxRate]=25,
IIf([USFed2008TaxRate&Amt].[Income]>"$78,850.00"<="$164,550.00",[USFed2008TaxRate&Amt].[TaxRate]=28,
IIf([USFed2008TaxRate&Amt].[Income]>"$164,550.00"<="$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=33,
IIf([USFed2008TaxRate&Amt].[Income]>"$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=35))))))

Click to expand...

Click to expand...

Evan · Dec 20, 2008

The purpose is to show various country's tax rates and tax amounts due based
on $75,000 Income. The Union All worked fine, except that the [CountryName]
field is from the Table: [Country] which also includes the primary key:
[CountryID] ; The individual country rate & Amt queries contain the foreign
key [CountryID], [rate], & [Amt]. I added the the Country table to the FROM
clause but I need to add a criteria for this in the WHERE clause that
restricts only those [Country] names where there is a rate&Amt query for it.
How would such a criteria look?

Thanks Evan

Dale Fye said:
Evan,

What is the purpose of this query?

If you need to create a query, that returns all of these countries data in a
single query, then I think what you need is a union query. Something like:

SELECT CountryName, TaxRate, TaxAmount
FROM [BRFdRate&Amt]
WHERE [Income] = 75000
UNION ALL
SELECT CountryName, TaxRate, TaxAmount
FROM [CAFdRate&Amt]
WHERE [Income] = 75000
UNION ALL
SELECT CountryName, TaxRate, TaxAmount
FROM [GEFdRate&Amt]
WHERE [Income] = 75000
....

BTW, I would avoid special characters in field names. The only
non-alphanumeric I ever use in a field name is an underscore.

HTH
Dale

Evan said:

I have constructed 6 queries of various country's tax schedules. The
queries
include the fields: CountryID as FK, Income, TaxRate, TaxAmount. I
would
like to create another query that combines these queries with a Country
table
with fields: CountryID as PK and CountryName. Here's what I have but
getting
error messages:

SELECT C.CountryName, TaxRate, TaxAmount
FROM Country as C,
[BRFdRate&Amt].TaxRate as TaxRate,
[CAFdRate&Amt].TaxRate as TaxRate,
[GEFdRate&Amt].TaxRate as TaxRate,
[SPFdRate&Amt].TaxRate as TaxRate,
[UKFdRate&Amt].TaxRate as TaxRate,
[USFdRate&Amt].TaxRate as TaxRate,
[BRFdRate&Amt].TaxAmount as TaxAmount,
[CAFdRate&Amt].TaxAmount as TaxAmount,
[GEFdRate&Amt].TaxAmount as TaxAmount,
[SPFdRate&Amt].TaxAmount as TaxAmount,
[UKFdRate&Amt].TaxAmount as TaxAmount,
[USFdRate&Amt].TaxAmount as TaxAmount,
((((( Country INNER JOIN [BRFdRate&Amt] ON Country.CountryID =
[BRFdRate&Amt].CountryID) INNER JOIN [CAFdRate&Amt] ON Country.CountryID =
[CAFdRate&Amt].CountryID) INNER JOIN [GEFdRate&Amt] ON Country.CountryID =
[GEFdRate&Amt].CountryID) INNER JOIN [SPFdRate&Amt] ON Country.CountryID =
[SPFdRate&Amt].CountryID) INNER JOIN [UKFdRate&Amt] ON Country.CountryID =
[UKFdRate&Amt].CountryID) INNER JOIN [USFdRate&Amt] ON Country.CountryID =
[USFdRate&Amt].CountryID),
WHERE (([Income]=75000));
ORDER BY TaxAmount;

Any help is greatly appreciated. Thanks Evan

Dale Fye said:

Evan,

A couple of problems with this.

1. When you include the TaxPayerIncome and USFedTaxSchedule tables in
your
query, without a JOIN clause, you get what is known as a Cartesian Join
(every record in Table1 mapped to every record in Table2). You can
restrict
that by adding a JOIN clause, to "join" the two tables on a common field,
or
in a more complex join (as in John's example) in a non-equi join. This
latter type of join must be created in the SQL view, as it cannot be
depicted
in the query design grid. Another way to restrict the cartesian joins
result
set is to include a WHERE clause that limits the result set to those
where a
relationship of interest exists between the two tables.

2. You don't need to use the SUM( ) because you are trying to add the
values from multiple fields in the same record, not across multiple
records.

Assuming that the table structure fo your [USFedTaxSchedule] table looks
something like: [BaseTax], [Low], [High], [TaxRate], I would recommend
getting rid of the DMIN( ) domain functions, and going with something
like
the following (which probably looks very similar to John's original
post).

SELECT TPI.Income,
TaxSch.TaxRate,
TaxSch.BaseTax,
TaxSch.BaseTax + (TaxSch.TaxRate * (TPI.Income - TaxSch.Low))
as
TaxAmount
FROM TaxPayerIncome as TPI, USFedTaxSched as TaxSch
WHERE TaxSch.High > TPI.[Income]
AND TaxSch.Low <= TPI.[Income]
ORDER BY TPI.Income, TaxSch.TaxRate

You could also write this as shown below, but as I indicated above, this
type of join can only be written in the SQL view.

SELECT TPI.Income,
TaxSch.TaxRate,
TaxSch.BaseTax,
TaxSch.BaseTax + (TaxSch.TaxRate * (TPI.Income - TaxSch.Low))
as
TaxAmount
FROM TaxPayerIncome as TPI
INNER JOIN USFedTaxSched as TaxSch
ON TaxSch.High > TPI.[Income] AND TaxSch.Low <= TPI.[Income]
ORDER BY TPI.Income, TaxSch.TaxRate

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

:

Dale,

The SQL statement for the query is:

SELECT TaxPayerIncome.Income, DMin("TaxRate","USFedTaxSchedule","[High]
"
& [Income]) AS TaxRate, DMin("BaseTax","USFedTaxSchedule","[High] > " &
[Income]) AS BaseTax
FROM TaxPayerIncome, USFedTaxSchedule
ORDER BY TaxPayerIncome.Income,
DMin("TaxRate","USFedTaxSchedule","[High] >
" & [Income]);

Evan

:

Evan,

Would need to see the entire SQL statement to make sense out of this.

Dale

Thanks so much. The DMIN function in my query works well. I also
need
to
include the calculated tax amount based on the Income & Tax Rate
fields in
the query and a BaseTax field from a table: USFedTaxSchedule that
includes
the Low, High, BaseTax and TaxRate fields.

I tried to use the DMIN function to bring the Base Tax into the
query
just
as was done with the TaxRate and then nest it in a Sum() function s
such:

TaxAmount: SUM(DMIN("BaseTax", "USFedTaxSchedule", "[High] > " &
[Income])
+
([TaxRate] * [Income] - [USFedTaxSchedule].[Low]))

I get an error: you tried to execute a query that does not include
the
specified expression 'Income' as part of an aggregate function.
Can
someone
help with this please. Thanks

:

Another method, this one uses Johns TaxRates table, but doesn't
require
his
JOIN would be to use the DMIN domain function to get the minimum
tax rate
from the tax rate table where the value of the ranges upper bound
([High]) is
greater than the persons [Income].

TaxRate: DMIN("TaxRate", "TaxRates", "[High] > " & [Income])

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

:

I have created a query on a table: USFed2008TaxRate&Amt and
created the
following IIf statement expression for the field: TaxRate in
that table
based
on the table's Income field. But, I get a syntax error. The
data type
for
the Income field is currency and the data type for the TaxRate
field is
a
long integer number. Any ideas what I'm doing wrong?

TaxRate:
IIf([USFed2008TaxRate&Amt].[Income]>"$0"<="$8,025.00",[USFed2008TaxRate&Amt].[TaxRate]=10,
IIf([USFed2008TaxRate&Amt].[Income]>"$8,025.00"<="$32,550.00",[USFed2008TaxRate&Amt].[TaxRate]=15,
IIf([USFed2008TaxRate&Amt].[Income]>"$32,550.00"<="$78,850.00",[USFed2008TaxRate&Amt].[TaxRate]=25,
IIf([USFed2008TaxRate&Amt].[Income]>"$78,850.00"<="$164,550.00",[USFed2008TaxRate&Amt].[TaxRate]=28,
IIf([USFed2008TaxRate&Amt].[Income]>"$164,550.00"<="$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=33,
IIf([USFed2008TaxRate&Amt].[Income]>"$357,000.00",[USFed2008TaxRate&Amt].[TaxRate]=35))))))

Click to expand...

Click to expand...