Append current record from one table to another

[Deb]

I need to know how to remove the current record from a table using a form
(clicking on a checkbox) and append it to another table (archive table).

I've designed a query, but it wants to copy all of the records from the
first table to the second table. I'm not sure how to set the criteria in the
query (or from the form) to make this happen.

Is there a better way than using a query? PLEASE HELP!

 

[John W. Vinson]

I need to know how to remove the current record from a table using a form
(clicking on a checkbox) and append it to another table (archive table).

I've designed a query, but it wants to copy all of the records from the
first table to the second table. I'm not sure how to set the criteria in the
query (or from the form) to make this happen.

Is there a better way than using a query? PLEASE HELP!

Unless you have a VERY good reason to do this, I'd really suggest a much
simpler solution: just add a yes/no field Archived to the table, and check it
to "archive" the records. You can base the Form on a query selecting just the
records where Archived = False to see active records, =True to see archived
ones.

For one thing, having the two tables in the same database provides almost zero
security: if a database is going to get damaged, it's almost always the entire
database, not just one table. For another, the size limitation of 2GByte
applies to the entire database, not to individual tables. Finally, with proper
indexing, you should not get any particular performance advantage by archiving
records.

All that said... you'll need to run *two* queries, an Append query using

=Forms!YourFormName!SomeControlName

as a criterion to select a single record; the append query will copy from the
main table to the archive table. You then need to run a Delete query with the
same criterion to remove the main-table record.

 

[Deb]

John: Thank you and Steve for your responses. The problem I have is that
the current table already has over 23,700 records. My plan is to build the
program using transaction codes (Received, Shipped, Adjusted, and several
other transaction codes) for the purpose of calculating quantity in stock
using a query. Perhaps my plan is flawed? (It was also requested to be done
that way by the client).

 

[John W. Vinson]

John: Thank you and Steve for your responses. The problem I have is that
the current table already has over 23,700 records. My plan is to build the
program using transaction codes (Received, Shipped, Adjusted, and several
other transaction codes) for the purpose of calculating quantity in stock
using a query. Perhaps my plan is flawed? (It was also requested to be done
that way by the client).

Huh?

You're planning to use a Form to process 23,700 records one at a time??? That
certainly does not sound practical.

Where are these transaction codes? In the table now?

What is your current table structure, and what is your desired ultimate table
structure?

 

[Deb]

John:

First of all, let me explain that the client (or actually the person who
will be doing the data entry) pretty much wants a "simple" flatfile system.
She would be happy with an Excel spreadsheet, but that would not allow for
over 35,000 records. Apparently an old program that she used was built that
way, and her boss wants her to be happy. Her boss wants to be able to pull
up forms and reports to determine inventory quantities, costs, etc. Data
will be entered by receiving and shipping personnel, along with the
aforementioned data entry person.

The planned structure of the table is as follows:
Transaction Code
Transaction Date
Part Number (driving force behind the whole system)
EMR (tracking number)
Requisition (tracking number)
Unit of Issue
Item Cost
Transaction Quantity
Serial Number
Index Code (tracking number)
Quantity in Stock
Next Higher Assembly (they want these items "archived")
Condition Code
Sponsor (who pays for the item)
Program (program that the item will be used for)
Division Code (who owns the item)
Purpose (why the item was ordered)
Building (storage)
Location (storage)
Warehouse Number (tracking number)
Date the record is modified
Who modified the record
Shipping Priority
Shipping Method
Tracking Number (shipping tracking number)

They provided me with an Excel file that they downloaded the 23,700 plus
records from, along with another file containing Part numbers, descriptions,
units of issue, cage codes, etc. I've built lookup tables for a lot of the
fields that they want in the table.

This may be way too much information, but based on their "requirements", I
need all of the help I can get on this effort!

Thank you,

 

[Deb]

I almost forgot, the reason they want a separate "History" table is so they
can keep track of where the shipped inventory parts went, and I left out that
they want a UIC field to identify where their parts went.

 

[John W. Vinson]

They provided me with an Excel file that they downloaded the 23,700 plus
records from, along with another file containing Part numbers, descriptions,
units of issue, cage codes, etc. I've built lookup tables for a lot of the
fields that they want in the table.

This may be way too much information, but based on their "requirements", I
need all of the help I can get on this effort!

Ok... so the client wants a 1960's style flat system because New Is Evil.
That's her problem I guess. :-{(

What does this have to do with your original question about appending one
record at a time to an archive table? Did the Archived yes/no field suggestion
help?

 

[Deb]

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

 

[John W. Vinson]

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?

 

[Deb]

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]

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.

 

[Deb]

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

Thank you sooooo much for all your help!