calcluating a value if the original value meets a condition

[headachewithExcel]

Hello, I'm very new to Access, and I'm having a headache trying to set up and
run queries for the first time. I'm used to doing things in Excel and SPSS,
and maybe having a hard time adjusting to how the things are done in Access.
What I'm trying to do is this:
I'm trying to set up an accounting database, where you enter the amount of
money (from vouchers, etc.) that you spend. I have the "amount" field, and
some of the values in the field may already have taxes added, some do not
have it added yet (so need to have it added), and some are not taxed (so no
need to add tax). One of these tax categories are selected in a pull down
menu ("tax status" field) on the data entry form.
So, after entering the data, I want to add the tax amount only to those
records who were assigned the "tax not added yet" category in the "tax
status" field. The records that had the other two tax status should stay the
same as originally entered.
How do I do this -- by creating another field with all records (some as they
were in the original field, some with tax now added) or by overwriting the
original field/ records?
Please let me know. Thank you.

 

[Allen Browne]

There are 2 separate questions interwined here:
a) How do I design a table structure for this data?
b) How do I design a flexible interface for the user to enter this data.

For (a), the correct answer is to store all amounts *without* the tax, with
an extra field indicating the tax rate that applies to this row. If it is
tax exempt, the tax rate is 0%. If the tax rate ever changes, the existing
records will still be correct, since they will have the old tax rate. If a
government decides that some items are at a higher tax rate than others,
this structure still copes.

So, you will have a main form for the Transaction header table, with fields
such as:
TransactionID AutoNumber primary key
TransactionTypeID credit/debit, or whatever
TransactionDate Date/Time
ClientID Number who this transaction is for
and a TransactionDetail table for the line items in the transaction, with
fields such as:
TransactionDetailID AutoNumber primary key
TransactionID which transaction this row belongs to
Quantity Number how many items
ProductID relates to table of products
PriceEachEx Currency unit price without tax
TaxRate Number tax rate (percent)

You then create a query, and type an expression like this into the Field
row:
AmountEx: [Quantity] * [PriceEachEx]

In the next column in the field row:
Tax: [Quantity] * [PriceEachEx] * [TaxRate]
Or, to avoid rounding errors, you might want:
Tax: CCur(Nz(Round([Quantity] * [PriceEachEx] * [TaxRate],2),0))

If you wish, you can then add another calculated column:
AmountInc: [AmountEx] + [Tax]

Save the query. You can now use it anywhere you would have used the table
(e.g. as the source for a form or report), and it will show you the correct
calculated results. Typically you create a main form bound to the
Transaction table, with a continuous subform bound to the query so it shows
the line items, and a total in the subform's Form Footer.

Now for the 2nd part of your question, how can the user enter a value that
includes the tax, and have it stored correctly in this table? The user
cannot enter anything into a calculated field, so you will need some
programming tricks to simulate it. Add an unbound text box to the subform.
Place it behind the AmountInc text box. Set its Tab Order so it shows in the
correct place. Send To Back (Format menu) so it sits behind the Amount Inc.

For AmountInc, set its TabStop to No (so focus passes to the unbound box.)
In its Enter event, SetFocus to the Unbound box (in case the user clicks on
it.)

In the Enter event of the unbound box, copy the value of AmountInc (so it
contains the same value as AmountInc on the current row.) The user can now
type the amount into the unbound box, and you can use its AfterUpdate event
procedure to calcuate the tax-ex price each and assign it to the PriceEachEx
field.

To the user, it looks like they are typing into the calculated control, so
the interface works just the way the user expects, even though the data
structure is storing the data in a correctly normalized manner.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message