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