Archive Data

[Guest]

I have a table containing details of customer orders which has a dropdown
status field. When the status is changed to 'completed' I want the order
details to transfer to a separate table so that the first table only contains
details of incomplete orders. Would I do this with an append query?

Many thanks.

 

[Guest]

Do yourself a favor and do NOT do this. Unless you have a few hundred
thousand records, there is no good reason to do so. It's almost always a
mistake and causes you do do slow, convoluted things like Union queries to
check on all records in both tables to find what you are looking for. Also
think of the problems when you find that the order really wasn't completed
and need to move it back.

If you don't want to see completed records, just base any forms or reports
on a query that excludes them. Something like:

Status <> "completed"

 

[BruceM]

You could use an append query, but you probably shouldn't unless millions of
records are causing performace issues. That's not how relational databases
work. When you need either completed or not-yet-completed orders you could
query for just those records.

If OrderDetails is related to an Orders table you have the additional issue
of an order record being incomplete because some of the child (OrderDetails)
records are in another table. You could move the OrderDetails records to
another table, but then you would need a query to combine the tables if you
want to see the full Order record. If this is the case there is nothing to
be gained by creating a second table, then recombining it with the original
one.