Thoughts on how and where to keep history data?

[niuginikiwi]

I have a Sales Order Request processing application in a FE/BE environment.
It has a main form and a subform.
We receive Order to supply proucts each day. We make up the products and
tick a checkbox when that orderline is completed.
However, we dont tick every order line because we can not fullfil all orders
so we delete them.
BUT instead of deleting them, i'd like to export them to a table which can
get appended everytime we have an order that we can not supply for every
order line.
Ideally i'd like this table to be in another db because i don't want to clog
up the size of the production db with some history data.

I'd like to have a button on the main form called btnCloseOrder which when
clicked exports all the order lines not completed to a table and adds them on
if there is already existing records, then deleted those incomplete orders.

Can I please get some pointers on how to do that please?

 

[Arvin Meyer [MVP]]

I have a Sales Order Request processing application in a FE/BE environment.
It has a main form and a subform.
We receive Order to supply proucts each day. We make up the products and
tick a checkbox when that orderline is completed.
However, we dont tick every order line because we can not fullfil all
orders
so we delete them.
BUT instead of deleting them, i'd like to export them to a table which can
get appended everytime we have an order that we can not supply for every
order line.
Ideally i'd like this table to be in another db because i don't want to
clog
up the size of the production db with some history data.

I'd like to have a button on the main form called btnCloseOrder which when
clicked exports all the order lines not completed to a table and adds them
on
if there is already existing records, then deleted those incomplete
orders.

Can I please get some pointers on how to do that please?

Unless you have several thousand records a week, you won't have a problem
with "clogging" Access. If you do have more than that, then perhaps you need
to think about how long that you need to keep those records, and if more
than a few years, you might consider a SQL-Server back end.

In either case, the best solution does not involve copying records to
another table. You should be marking them as shipped, or even simpler, check
the stock level in code, when you enter the original order, deduct the item
immediately if there is stock or having the record automatically marked as
"OutOfStock" if you need to order before shipping. Your invoices can then
ignore items marked which are out of stock, and you can use the same records
to count the items against a level of stock field and create purchase orders
for you automatically at the click of a button.

A sophisticated app could even figure the receiving time based on an entry
in the products table and issue an expected delivery date on the invoice or
packing list. This tells your customers that you value them enough to let
them know when to expect delivery.

 

[niuginikiwi]

Thaks Arvin,
We already know what we can supply and what we can't supply before the
orders come in. I thought I was gona tidy things up by exporting them to a
different table using a SQL query but I guess I could just leave them
unchecked as not fullfilled or completed order lines and just throw the
already checked as completed order lines onto the invoice.
Then later on generate reports on how many we could not supply.
Thanks