Calculations in Tables

[Ruth]

I have a table in which I have the following columns:-

1.Product
2.Taxable?
3.Price ex Tax
4.Tax
5.Price with Tax

I want to just enter data in the first 3 columns.
I want columns 4 and 5 to be calculated automatically using the following
formula:-
Column 4 - if column 2 = yes, then column 3 multiplied by 0.175, otherwise
zero.
Column 5 - column 3 plus column 4.

I'd really appreciate any suggestions on how to do this.

Thank-you.

 

[RBear3]

In a normalized Access database, you don't perform calculations in a table.
Remove those two fields. Instead, perform the calculations in your queries,
reports, and forms when you need them.

Storing the numbers is redundant and opens you up to all kinds of problems.
What if one of the three values changes? How would you update the
calculations?

 

[missinglinq via AccessMonster.com]

Amazingly, you've just cited one of the small number of reasons FOR sometime
storing calculated values! Presumably this will be used for generating some
sort of invoice/order form. The deed is done and the transaction stored.

Next, somewhere down the road, taxes go up, as they always seem to do!

Finally, something happens and you need to pull records on past transactions.

If the calculations are stored, retrieving the data is easy!

If the calculations are not stored, retrieving the data is difficult or
impossible! Re-calculating tax on the product/order can't be done now because
the tax rate is different from that which was in force at the time of the
transaction! Granted, you could have a separate tax history table, and go
thru a lot of song in dance in order to figure out what the rate was then,
but why go thru this aggravation? Memory is cheap today!

And this is not just my opinion, but rather that aof a number of experts here
and elsewhere who have extensive experience in developing accounting
applications.

 

[Wayne-I-M]

Hi

I (almost) agree with RBear. Calculated field should not be stored (even in
this case).
But the tax (rate) as missinglink rightly says may change. So this "should"
be stored

The fields needed are
1.Product = Text (or maybe Linking ID)
2.Taxable = Yes/No
3.Price ex Tax = Currency
4.Tax = Number

The calculated result can be produced from these.
If Taxable is yes then Price Ex Tax * Tax

 

[Keith Wilby]

And this is not just my opinion, but rather that aof a number of experts
here
and elsewhere who have extensive experience in developing accounting
applications.

I concur and on a tentatively related issue, the same is true for survey
databases. If you have a list of questions in a lookup table, you should
copy the questions to the survey table and not just have a link to the
lookup. That way, if a question changes or is deleted or a new one added,
you still have a record of the questions actually asked in surveys predating
the change to the question(s).

Just my 2p worth

Keith.
www.keithwilby.com

 

[Wayne-I-M]

Hi Ruth

All these people (me included) making comments have forgoten to answer your
post. Sorry.
You can use the item below to show you the method to use then (after you
have it) then you can change it or copy the details to your own tables and
querys. Try this and it will show you how to do it.

Create a table call RuthsTable
Insert these fields
ProducID = AutoNumber
Product = Text
Taxable = Yes/No
PriceExTax = Currency
TaxRate = Number (Note - Field Size = Single / Format = Percent)

Make sure you use the names exactley as I have put them above.

Next create a new query called Ruths Query
Open the query in design view and insert RuthsTable into the top section.
Select View (top of screen)
Select SQL
Cut and paste this into this area

SELECT RuthsTable.ProductID, RuthsTable.Product, RuthsTable.Taxable,
RuthsTable.PriceExTax, RuthsTable.TaxRate,
([PriceExTax]*([TaxRate])+[PriceExTax]) AS PriceWithTax,
([PriceExTax]*[TaxRate]) AS TaxAmount
FROM RuthsTable;



Select View Design. Save and view the query.

You should not "work on" in the table it's just there for design and to
store data.

You can work on the query if you want or create a form for it (this is
better for users.

Oh just a point on percentages. Note that they a fractions of 100 so 5% is
entered as 0.05 (as 1 is 100). 9 would be entered as 0.09 etc etc - standard
UK VAT rate would be entered as 0.175 (17.5%)

Good luck.

 

[KARL DEWEY]

If you are storing calculation in a table you need to add a date so you will
know if the data is still valid or outdated.
--
KARL DEWEY
Build a little - Test a little

Hi Ruth

All these people (me included) making comments have forgoten to answer your
post. Sorry.
You can use the item below to show you the method to use then (after you
have it) then you can change it or copy the details to your own tables and
querys. Try this and it will show you how to do it.

Create a table call RuthsTable
Insert these fields
ProducID = AutoNumber
Product = Text
Taxable = Yes/No
PriceExTax = Currency
TaxRate = Number (Note - Field Size = Single / Format = Percent)

Make sure you use the names exactley as I have put them above.

Next create a new query called Ruths Query
Open the query in design view and insert RuthsTable into the top section.
Select View (top of screen)
Select SQL
Cut and paste this into this area

SELECT RuthsTable.ProductID, RuthsTable.Product, RuthsTable.Taxable,
RuthsTable.PriceExTax, RuthsTable.TaxRate,
([PriceExTax]*([TaxRate])+[PriceExTax]) AS PriceWithTax,
([PriceExTax]*[TaxRate]) AS TaxAmount
FROM RuthsTable;



Select View Design. Save and view the query.

You should not "work on" in the table it's just there for design and to
store data.

You can work on the query if you want or create a form for it (this is
better for users.

Oh just a point on percentages. Note that they a fractions of 100 so 5% is
entered as 0.05 (as 1 is 100). 9 would be entered as 0.09 etc etc - standard
UK VAT rate would be entered as 0.175 (17.5%)

Good luck.




--
Wayne
Manchester, England.

I have a table in which I have the following columns:-

1.Product
2.Taxable?
3.Price ex Tax
4.Tax
5.Price with Tax

I want to just enter data in the first 3 columns.
I want columns 4 and 5 to be calculated automatically using the following
formula:-
Column 4 - if column 2 = yes, then column 3 multiplied by 0.175, otherwise
zero.
Column 5 - column 3 plus column 4.

I'd really appreciate any suggestions on how to do this.

Thank-you.

 

[Ruth]

I can see where you are coming from but to try and keep things simple,
rightly or wrongly we set the database up so that we have all the information
on one table so that we have everything to hand when setting products up and
to answer customer enquiries.

I think it would be a fairly big job to work from a query as we have forms
and reports set up using the main table as the data source.

I've been having a look this afternoon and wondered if you could use the
default value option in design view but cannot see where you can pull fields
into the calculation?

 

[RBear3]

Your forms and reports should be based on queries, not tables. What if you
want to include only "active" items in the future? Or only pull invoices
from the last twelve months, etc.

It is pretty easy to change your forms and reports to be based off queries.

Also, there is no way to do what you want in your tables. You would have to
change the forms that create the records to perform the calculations and
update the underlying field. Again, if a value changes, you will be hosed.
Just because you have already set it up a certain way does not mean that it
will work for you. If you did it wrong, you did it wrong.


There was an earlier post that had a good point. You may (in an invoicing
situation) want to save a value at a point in time. For example, the price
of a "widget" will change. Your invoices need to store the value of that
part when you sold it.

ALL of this is already taken into account in the sample database "Northwind"
that ships with Access. Why don't you go take a look at that and see how
they handle invoices? It is a simple layout that includes an invoice table
for the overall invoice data, and an invoice detail table for each line on
the invoice. It also handles your tax and other questions you have raised.

 

[John W. Vinson]

I can see where you are coming from but to try and keep things simple,
rightly or wrongly we set the database up so that we have all the information
on one table so that we have everything to hand when setting products up and
to answer customer enquiries.

Don't confuse data PRESENTATION with data STORAGE.

They are different tasks with different requirements! Users should basically
*never* see a table datasheet, nor should they need to care about the
structure of the table.

Your table should be normalized, without calculated fields (as per the
discussion in the thread - the tax rate should be stored but the product of
the cost and tax rate should not). What the user will see for setting products
up and answering customer enquiries is a *FORM* based on the table, with the
appropriate tools - combo boxes, calculated controls, subforms, etc. - to
provide the information that they need.

John W. Vinson [MVP]

 

[Wayne-I-M]

Not storing the calculation

([PriceExTax]*([TaxRate])+[PriceExTax]) AS PriceWithTax,
([PriceExTax]*[TaxRate]) AS TaxAmount

But you're right. A datestamp would be a good idea.


--
Wayne
Manchester, England.

If you are storing calculation in a table you need to add a date so you will
know if the data is still valid or outdated.
--
KARL DEWEY
Build a little - Test a little

Hi Ruth

All these people (me included) making comments have forgoten to answer your
post. Sorry.
You can use the item below to show you the method to use then (after you
have it) then you can change it or copy the details to your own tables and
querys. Try this and it will show you how to do it.

Create a table call RuthsTable
Insert these fields
ProducID = AutoNumber
Product = Text
Taxable = Yes/No
PriceExTax = Currency
TaxRate = Number (Note - Field Size = Single / Format = Percent)

Make sure you use the names exactley as I have put them above.

Next create a new query called Ruths Query
Open the query in design view and insert RuthsTable into the top section.
Select View (top of screen)
Select SQL
Cut and paste this into this area

SELECT RuthsTable.ProductID, RuthsTable.Product, RuthsTable.Taxable,
RuthsTable.PriceExTax, RuthsTable.TaxRate,
([PriceExTax]*([TaxRate])+[PriceExTax]) AS PriceWithTax,
([PriceExTax]*[TaxRate]) AS TaxAmount
FROM RuthsTable;



Select View Design. Save and view the query.

You should not "work on" in the table it's just there for design and to
store data.

You can work on the query if you want or create a form for it (this is
better for users.

Oh just a point on percentages. Note that they a fractions of 100 so 5% is
entered as 0.05 (as 1 is 100). 9 would be entered as 0.09 etc etc - standard
UK VAT rate would be entered as 0.175 (17.5%)

Good luck.




--
Wayne
Manchester, England.

I have a table in which I have the following columns:-

1.Product
2.Taxable?
3.Price ex Tax
4.Tax
5.Price with Tax

I want to just enter data in the first 3 columns.
I want columns 4 and 5 to be calculated automatically using the following
formula:-
Column 4 - if column 2 = yes, then column 3 multiplied by 0.175, otherwise
zero.
Column 5 - column 3 plus column 4.

I'd really appreciate any suggestions on how to do this.

Thank-you.