Append current record from one table to another

Do you have any suggestions for using an "archive" table where the current
record in a form can be deleted from the "Inventory" table and moved to a
"History" table? Some of the fields from the inventory table will not be
required in the History table. For example, building, location, quantity in
stock, warehouse number will not be required once the items are shipped.

I currently have two check boxes in one of the forms for Next Higher
Assembly and Archive, and theoretically, clicking on either one would remove
the record from inventory and append it to history (at least, that is what
the client wants).

"John W. Vinson" wrote:

On Tue, 9 Jun 2009 09:58:01 -0700, Deb wrote:

I guess that it sounds to me like everything will stay in the same table.
She anticipates approximately 35,000 records in "Inventory". We would
probably wind up with at least two records per inventory item (Received and
then Delivered by some method) for a total of 70,000 records. Will that not
cause issues down the line? Apparently we will eventually be able to delete
some of the records, but for all practical purposes, they want to receive the
items, record all of those tracking numbers that they will put in, and
"archive" the items once they have been shipped. At least some of the items
may come back into inventory.

For clarification purposes, this "inventory system" is for a government
based warehousing facility. Some items will be direct turn over (shipped as
soon as it comes in), but they still have to track that it was received,
where it came from, who paid for it, which command the items are sent to, and
they need to retrieve that information for future reference in the event
that, 10 years down the road, the command returns the item to the base for
whatever reason. It's very complicated, and needs a proper inventory system,
but it's like the inventory items never actually "go away".

The rational way to do this is with multiple tables - Items, Locations,
Commands, maybe Sources, and Transactions (shipment events). However, if I
understand aright, they have had this (or something like it) proposed and have
intentionally rejected it in favor of the non-normalized single table
solution... right?

I'm really not sure what I can suggest if that is the case. Given the table
structure you have proposed, you can certainly do all of these things; what
additional capabilities would you like to have?

--

John W. Vinson [MVP]

On Tue, 9 Jun 2009 17:13:01 -0700, Deb wrote:

Do you have any suggestions for using an "archive" table where the current
record in a form can be deleted from the "Inventory" table and moved to a
"History" table? Some of the fields from the inventory table will not be
required in the History table. For example, building, location, quantity in
stock, warehouse number will not be required once the items are shipped.

I currently have two check boxes in one of the forms for Next Higher
Assembly and Archive, and theoretically, clicking on either one would remove
the record from inventory and append it to history (at least, that is what
the client wants).

If you base the form on a Query selecting only unarchived (or archived)
records, you would get the same effect as far as the user is concerned: they
could choose to see only archived records, or only unarchived records.

If (for some reason) they really want to MOVE the record to another table (be
aware that this will bloat the database to a minor extent, and will NOT
provide any improved data security or any significant performance advantage),
then you'll need to run two queries: an Append query to append the current
record to the archive table, and a Delete query to delete the record after it
has been appended. These queries could be run from VBA code or from a Macro.
I'd recommend code because you can trap errors and use a Transaction to ensure
that either both queries run or neither does.
--

John W. Vinson [MVP]

Thanks John. I'll try to put this to the test tomorrow.

Thank you sooooo much for all your help!

"John W. Vinson" wrote:

On Tue, 9 Jun 2009 17:13:01 -0700, Deb wrote:

Do you have any suggestions for using an "archive" table where the current
record in a form can be deleted from the "Inventory" table and moved to a
"History" table? Some of the fields from the inventory table will not be
required in the History table. For example, building, location, quantity in
stock, warehouse number will not be required once the items are shipped.

I currently have two check boxes in one of the forms for Next Higher
Assembly and Archive, and theoretically, clicking on either one would remove
the record from inventory and append it to history (at least, that is what
the client wants).

If you base the form on a Query selecting only unarchived (or archived)
records, you would get the same effect as far as the user is concerned: they
could choose to see only archived records, or only unarchived records.

If (for some reason) they really want to MOVE the record to another table (be
aware that this will bloat the database to a minor extent, and will NOT
provide any improved data security or any significant performance advantage),
then you'll need to run two queries: an Append query to append the current
record to the archive table, and a Delete query to delete the record after it
has been appended. These queries could be run from VBA code or from a Macro.
I'd recommend code because you can trap errors and use a Transaction to ensure
that either both queries run or neither does.
--

John W. Vinson [MVP]