Find Correct Tax Rate from Tax Table

[KBFour]

I am working with a small non-profit that works with Seniors. They sell some
arts and crafts and have to charge sales tax. It's a pretty small operation,
but we need to figure out how to populate the form they use to input the
sales. I have created a tax table with the effective date of the sales tax
rates (there is a state, county and local tax), but can't figure out how to
tell the form to populate the tax rate in the form and table with the correct
tax rate. As an example, in NC, the rates changed in October and the rate
went up and the allocation of how the tax is paid changed for all 3 of the
agencies collecting, so they had to run through all of this manually to
calculate the correct rates. Any help would be greatly appreciated!

 

[Daryl S]

KBFour -

You don't give us enough information to answer your question, especially
around the change in allocation. If you are just asking how to get the
correct tax data from the table (because it now has effective dates in it),
then the change would be to the query that pulls the tax information based on
the sales date. Where if you might have pulled the data from the tax table
by locality before, now you need locality and a date criteria. For example,
if the old query was:

SELECT StateTaxRate, CountyTaxRate, LocalTaxRate from tblTaxRates WHERE
tblTaxRates.Locality = Forms!MyForm!Locality;

You will need to change it to this:

SELECT StateTaxRate, CountyTaxRate, LocalTaxRate from tblTaxRates WHERE
tblTaxRates.Locality = Forms!MyForm!Locality AND tblTaxRates.EffectiveDate =
(SELECT Max(EffectiveDate) from tblTaxRates WHERE tblTaxRates.Locality =
Forms!MyForm!Locality AND EffectiveDate <= Forms!MyForm!SalesDate);

You are asking how to apply these, but you have not said how they should be
applied (e.g. how do you calculate them manually)? Please include what your
current system does, as well as what it should do going forward...

 

[KBFour]

Thanks for the reply! The calculation of the actual sales tax is really not
the issue. The issue is how to query the effective date from the tax table.
If the most current effective rate is 10/1/2009 and today is 1/26/2010, then
how do I get the form to know that the 10/1/2009 rate is the current rate?
The is no upper end date range.

Thanks!

 

[John W. Vinson]

Thanks for the reply! The calculation of the actual sales tax is really not
the issue. The issue is how to query the effective date from the tax table.
If the most current effective rate is 10/1/2009 and today is 1/26/2010, then
how do I get the form to know that the 10/1/2009 rate is the current rate?
The is no upper end date range.

Use a Subquery to select the most recent effective date prior to today's date:
a criterion on the effective date of

=(SELECT Max([EffectiveDate]) FROM taxtable WHERE [EffectiveDate] <= Date())

Use [SaleDate] instead of Date() if you want to find the tax rate in effect at
a particular SaleDate in the past.

 

[Daryl S]

KBFour -

This query will pull the tax rate that has the latest effective date prior
to or on the sales date:

SELECT StateTaxRate, CountyTaxRate, LocalTaxRate from tblTaxRates WHERE
tblTaxRates.Locality = Forms!MyForm!Locality AND tblTaxRates.EffectiveDate =
(SELECT Max(EffectiveDate) from tblTaxRates WHERE tblTaxRates.Locality =
Forms!MyForm!Locality AND EffectiveDate <= Forms!MyForm!SalesDate);