Having trouble calculating tax

[torok]

Please help me with this query!

I've got a table "orderitems" that allows people to enter unit prices, # of units, and then select a tax code from a combo box. The combo box gets values from a tax table which has three fields: tax_code, tax_desc, and tax_rate. Taxes available are NONE, GST, PST, GST&PST.

When I calculate an order's total (for the purpose of creating an invoice), I need to know the value of the PST and the value of the GST separately. I can't figure out how to do it. Any suggestions? Maybe my design is poor.

I can easily write a query that calculates the total tax, but I can't figure out how to get Access to give me one column for PST and one column for GST.

Thanks!

 

[cogent1]

How do you derive the total tax? What proportion is GST and what PST? Without more data, there is no point of reference.

 

[torok]

I'm not sure what you mean.... Possible taxes are GST and PST. At present, I allow the user to select whether an item should be charged PST, GST, or both. The GST and PST rates are fixed according to the Taxes table, but can be modified by modifying that table.

So when I calculate the value of an order I need to calculate the subtotal, the GST, and the PST associated with each item. Since an item has a tax code associated with it I can look up the rate. (i.e. code 1 = no tax = 0, code 2 = GST = 0.07, code 3 = PST = 0.075, code 4 = both = 0.145). This allows me to calculate a subtotal and a total very easily, but if I want my query to calculate the subtotal, the PST, the GST, and the Total it becomes very difficult, partly because of the "Both" field, but also because I'm not sure how to write the query so that it gives me a column for PST and a column for GST if there's only one "tax" column. Subqueries maybe?

I might have to just use checkboxes to specify whether PST and GST are charged. Hm...

 

[RichMorrison]

I don't see any really simple solution. If you are doing this with a form, you could make a "Calc" button and then write some code for OnClick.

Make a Select Case for the combo box
if 1 nothing, if 2 do a DLookup of GST rate, if 3 DLookup PST, if 4 DLookup GST then PST.

Calculate the tax amount(s) and display on form as needed.

HTH,
RichM

 

[cogent1]

If the item is associated with a certain code, I don't see the problem. Knowing the code, you can use an expression to get PST or GST working back from the total. I'll work it out and post my findings tomorrow.

 

[cogent1]

I must be misunderstanding this problem. If you have a tax code for each item, you can derive all taxes as follows:



You can get the tax as follows

If TAXCODE 1=No Tax, 2=GST, 3=PST 4=Both

Then for GST
IIf([TaxCode]="1" OR [TaxCode]="3", 0, 0.07*[ItemCost]*[Qty])

For PST
IIf([TaxCode]="1" OR [TaxCode]="3", 0, 0.075*[ItemCost]*[Qty])

For Both
IIf([TaxCode]<>"4" ,0.145*[ItemCost]*[Qty], 0)

SubTotal=[ItemCost] *[Qty]

TotalTax=GST+PST

TotalCost=ItemCost+GST+PST

With these calculations you have all the data you need for your invoice. You could make all these calculations directly in the report.

Why do you need a query?

Now, tell me where I'm going wrong! This is too easy!

 

[torok]

Hm. Actually, cogent1, that might work rather well - if it doesn't I'll let you know. I might still need a query because there is more than one item on the order, and I would have to group my result by order, summing the taxes etc. On the other hand, might be able to do that on the report too, I'll have to check into it.

Apparently I need more sleep. Thanks very much for the assistance! I'll try to ask more difficult questions in the future

 

[fratezone]

might I suggest that you also include the HST and while your at it, breakdown the PST's by province since they vary. (better off to do right from the start than to recode at a later date because you know someone is going to ask for it!)

Just a thought
Ray

 

[fratezone]

reading this post painfully reminds me the all too real tax dilemma we have in Canada !