Adding two access fields together and saving the total into thetable.

[satori_1]

Hi, I have been searching and pulling my hair out over this for a few
weeks now and i just cant get my head around how to do it. I hope you
guys can help!

OK..I am creating an Access 2003 database that controls all my dad's
invoicing and stock control. Mostly its been really easy to do but I
have come across a big problem when trying to create the invoices.
Here is a detailed summery of what i want to be doing.

I have a table that contains headings... Product 1 to 10, Quantity 1
to 10, Net amount 1to10 and Total.

When I raise an invoice i simply (from a drop down box linked to
another table called "products") choose my "product" and then again
from another box the "quantity". This fills in the first line in the
invoice main body with what the product is and the quantity i sold and
then it displays the net value of the item that is also held in the
separate table called "products".
This process is then repeated upto 10 times to complete the invoice.
At the end of each line I have a text box that calculates the line
total by taking the quantity and multiplying it by the net value. This
also works totally fine.

Then at the bottom of the invoice I have another 3 text boxes. The
first calculates the total of the line totals. The second calculates
the tax on it and then the third adds the tax total and the line
totals together to give me a total invoice figure.

This all works fine and dandy while you are viewing the form..
However ..!!!! I cant work out how to get the figures in my text boxes
stored into the "invoice" table along with the other figures such as
quantity and product.

I believe that this is not a normal process to be storing calculated
figures as its faster to calculate them as the form opens. However...

1 - I need to be able to change the prices on the products in the
future without it effecting previous invoices.
2 - I need to have the total figure and tax stored so that i can
offset it against my output tax

I maybe doing something stupidly wrong and totally overlooking a very
basic function in access.

PLease PLEASE help me

 

[Hank]

Do you have fields in your table for the totals to be populated to? and if
so, do you have your textbox controls bound to those fields?

 

[Jackie L]

First of all, make sure that you have the price field in your Invoice detail
table and not just in the product table. That way, when you choose the
product, you can populate the invoice line with the price at that time. When
a product price is changed in the future, it will not affect your invoice
history. It is always a good idea to do the same for description, cost, etc.
The easiest way to do this is to have the fields in your product combo box
(you can have the width set to zero so that they are hidden) then on the
After Update event of your choosing the product put

Me.ProductPrice = Me.Product.column(2)
Me.ProductDesc = Me.Product.column(3)
etc.
(note: the column numbering begins at zero, not one. Use your field
names)

If you put the Invoice Amt, sales tax, etc. fields in your Invoice header
table, then you would then need to have a "save" type button on your invoice
to activate the update of the calculated fields. This is the normal process
for most "canned" invoicing programs. Although I would not normally
recommend putting a calculated amount into a field, it helps, as you are
finding, with screens and reporting down the road.

Hope this helps with some of your issues.

Jackie

 

[John W. Vinson]

Hi, I have been searching and pulling my hair out over this for a few
weeks now and i just cant get my head around how to do it. I hope you
guys can help!

OK..I am creating an Access 2003 database that controls all my dad's
invoicing and stock control. Mostly its been really easy to do but I
have come across a big problem when trying to create the invoices.
Here is a detailed summery of what i want to be doing.

I have a table that contains headings... Product 1 to 10, Quantity 1
to 10, Net amount 1to10 and Total.

Then you don't really have a database. You have a spreadsheet! "Fields are
expensive, columns are cheap" - what will you do if you need ELEVEN items in
an invoice? Try to correlate two invoices? Redesign everything? OUCH!

You have a Many to Many relationship: each invoice has one or more Products,
and each Product may be in one or more Invoices. The proper structure for this
has three tables: Invoices (with information that applies to the invoice as a
whole, such as who it's to, invoice date, etc); Products (a table with product
ID, product name, current price, etc.); and InvoiceDetails, the missing piece
here. This will have one record for each product/invoice combination that you
sell; if an invoice has three products on it there will be three records in
INvoiceDetails for that invoice.

Take a look at the Orders form on the Northwind sample database that comes
with Access. It shows how this structure can be implemented.

You may also want to take a look at some of the tutorials and references here:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials


But to continue...

When I raise an invoice i simply (from a drop down box linked to
another table called "products") choose my "product" and then again
from another box the "quantity". This fills in the first line in the
invoice main body with what the product is and the quantity i sold and
then it displays the net value of the item that is also held in the
separate table called "products".
This process is then repeated upto 10 times to complete the invoice.
At the end of each line I have a text box that calculates the line
total by taking the quantity and multiplying it by the net value. This
also works totally fine.

Then at the bottom of the invoice I have another 3 text boxes. The
first calculates the total of the line totals. The second calculates
the tax on it and then the third adds the tax total and the line
totals together to give me a total invoice figure.

This all works fine and dandy while you are viewing the form..
However ..!!!! I cant work out how to get the figures in my text boxes
stored into the "invoice" table along with the other figures such as
quantity and product.

You don't.

I believe that this is not a normal process to be storing calculated
figures as its faster to calculate them as the form opens. However...

Computers are FAST. Adding ten numbers will take a few nanoseconds - FAR less
time than waiting for a disk drive to spin, read a record, transmit it to the
cpu. Worse, if you store both the line item values (which you should) and
their sum (which you shouldn't), they can get out of synch. Suppose you
realize that the Blue Five-point Widget was discounted that day, and correct
the price in the line item; if you have already stored the invoice total, you
will have *two different sums* and no automatic way to tell which one is
wrong. Just recalculate it.

1 - I need to be able to change the prices on the products in the
future without it effecting previous invoices.

You need to store the price twice - the *current* price in the products table,
and the *price charged at the time* in the InvoiceDetails table. However you
do NOT need to - nor should you - store anything which can be unambiguously
calculated from data that is already in your tables.

2 - I need to have the total figure and tax stored so that i can
offset it against my output tax

Again... calculate it when you need it. If it's stored in two places, it can
be edited in one of them. Auditors get really creepy when they see that.

I maybe doing something stupidly wrong and totally overlooking a very
basic function in access.

Just need to take the next step and get fully into the relational paradigm...
we'll be glad to help!

 

[satori_1]

Thanks to everybody for their responses. I am taking a good look at
what people have said and will report back when i feel i understand it
more.

Many thanks