how to create an archive table to store ordering info

[Guest]

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

 

[Joseph Meehan]

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

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.)

 

[Guest]

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.

 

[Joseph Meehan]

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 generally use queries, but you can set up the relationship outside a
query as well. I think queries make it a little more clear, especially if
you are a little shaky about what is happening.

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.

To get any total you need to have stored or entered the data. All you
need do is to reference it.

Your application appears to be borderline on how to accomplish this
current level thing. I would first try to use the raw data (with date
references) to compute it on the fly. However doing a daily update to a
running total might also be valid. I guess it would depend on how that
information was coming in, how accurate and timely it is etc. either would
work.