If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Find Correct Tax Rate from Tax Table
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! |
#2
|
|||
|
|||
Find Correct Tax Rate from Tax Table
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... -- Daryl S "KBFour" wrote: 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! |
#3
|
|||
|
|||
Find Correct Tax Rate from Tax Table
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! "Daryl S" wrote: 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... -- Daryl S "KBFour" wrote: 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! |
#4
|
|||
|
|||
Find Correct Tax Rate from Tax Table
On Tue, 26 Jan 2010 21:27:02 -0800, KBFour
wrote: 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. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Find Correct Tax Rate from Tax Table
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); -- Daryl S "KBFour" wrote: 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! "Daryl S" wrote: 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... -- Daryl S "KBFour" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|