calculations in a table and autonumber

[stefanie]

hello,

during the building of my database I stumbled upon two
problems:

1. In a table I have three fields called:
amount excl taxes
taxes
amount incl taxes
I want to give these fields the following properties:
[taxes]=0.10*[amount excl taxes] and
[amount incl taxes]=[taxes]+[amout excl taxes]
I can't figure out how to do this, can somebody help me
and explain what to do?

2. I an other table I have given a field the property
autonumber, but when I delete all record, the new record
doesn't start with 1, how can I solve this? And I want to
start the autonumber with 001 instead of 1, is this
possible?

Thanks Stefanie

 

[Harlan]

Stefanie,

As for the first problem...DON'T.... It's not common to store calculated
values in a table (under normal circumstances)...Use a form, query, or
report and calculate only when the information is needed. By the way, you
can't perform calculations at the table level.... IF you really desire to
store the calculated values, use a form to provide the calculations and
store the info back to the table from the form.

As for the second problem....Autonumber field is not suppose to be a
"meaningful" number. It is used to provide a nearly certain unique value to
represent that row of information. IF you are using the Autonumber field to
provide a sequence number, you are better off creating your own....search
the newsgroup for "creating number sequence" and you should find much
information.

HTH
Harlan

 

[Allen Browne]

Stephane, the field "amount incl taxes" does not belong in the table. You
can be certain of that, because it would be an error if ever the field was
not precisely the sum of the other two. One of the rules of normalization is
not to store a field that is directly dependent on another like that.

The way you get the "amount incl taxes" is in a query.
Create a query into your table.
Enter this into the Field row:
[amount incl taxes]:[taxes]+[amout excl taxes]
Use the query in your form or report.
You can now be confident the field will never be wrong.

The "taxes" field is not quite so cut and dried. If the government ever
changed the tax rate, then you would need to have the tax stored in your
table, or all your old transactions would be wrong. Strictly speaking, we
should probably store the Tax Rate rather than the amount of tax. However,
you might find it more convenient to store the amount of tax rounded to the
nearest cent, so that your invoices always add up (don't have rounding
errors).

To achieve this, use the AfterUpdate event procedure of the [amount excl
taxes] box on your form. Set its After Update property to:
[Event Procedure]
Click but build button (...) beside this.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter:
Me.[taxes] = Round(Me.[amount incl taxes] * 0.1, 2)

Your program will now automatically calculate the tax when anyone enters the
tax-ex amount.