how to create an archive table to store ordering info

[troubledinaccess]

I have three tables for inventory control, one that is basically
description table and lists what we use, a second related table that tells
stock quantity, and a third related table that shows ordering and receiving.
I am trying to create a form that will overwrite information in the 2nd and
3rd tables as the stock amounts and ordering fluctuates, where my problem
lies is trying to get a fourth table for archiving of all ordering done in
order to compile a report for orders over a given period of time. the columns
needed in the archive table are po#, ordered amount, received amount,
received date, and order cost. I have managed to make exppressions that will
auto calculate stock quantity as orders are made and received, but cannot
figure out how to get the fourth table to populate. I would prefer to be
able to accomplish this from the same form if its possible

 

[troubledinaccess]

I am not sure of exactly what you need, but let me just though out a few
ideas and see if they help.

It is not normally a good idea to compute and store any number that
might change. You would normally not store the current inventory level as
it will change tomorrow. You would compute it anytime you want to see it.

Normally you don't want an archive table. You may mark records as
inactive or whatever, but they would normally stay in the same table. (BTW
there is no size limit to a table, rather there is a size limit to the whole
file so splitting data into an archive table does nothing to save space.)

I have decided after reading your response that I can do without the
archive table and just pull queries off of the actual table that i would have
wanted to make the archive from. If I were to need information from more
than just the one table to make a report I could do a union query to combine
the information or would I just add information directly through
relationships into the report?
I am not understanding how to calculate a current stock level at a given
moment, what would be the reference for tomorrows calculation if i have not
stored yesterdays amount? What I was originally planning to do was keep a
running total, then make a form to add and subtract from that number by
entering amount used or received and having a calculation in the backround
adjust my running total.
Asone could probobly tell I am very new to databases and stock control in
general any help or advice is greatly appretiated.