Transfering info between tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can I remove lines from one table and transfering that info to an archive
table when product has been shipped.
 
Sure, you can use 2 separate SQL statements, one to insert to the archive,
then one to delete the original. But I'm curious - why not simply use a
single table and flag it as shipped or inactive or some such?

Susan
 
Well, ok, sure. But remember that unless you compact the in process table
regularly it will bloat also.

Do you need help with the SQL? If so, what are the defining fields and
controls for an item being shipped and a unique id for that record?
 
Yes I do need help, thank you. I don't know the right terms for sql. The
Pallet number is the way I had thought to remove them. I've made a table to
summarize the production by pallet. I have one line or each pallet. Is that
what you need to know.

Thanks Clare
 
Susan makes a very good point here. How large is too large?

A properly indexed table can have hundreds of thousands of
records before becoming unwieldy.
 
Could get upto 500,000 thousand enteries as it is a freezer plant that holds
inventory for up to 3yrs.

Marshall Barton said:
Susan makes a very good point here. How large is too large?

A properly indexed table can have hundreds of thousands of
records before becoming unwieldy.
--
Marsh
MVP [MS Access]


My boss is worried that the table will get to large.
Click to expand...
Click to expand...
 
Ok, well you *should* create a form for data entry - entering data directly
into the table can cause numerous problems (For example, a user hitting the
down arrow and modifying the next record rather than the one they meant to).
Using a form will give you Events you can use as triggers to run code. This
would most definitely be the preferred method, as you don't have to rely on
someone remembering to manually run a query or two.

Also, do you already have an Archive table setup? It should have the EXACT
same structure as the Main table. (If you haven't set this up yet you can
create it by copying and pasting the Main table and choosing Structure
Only).

Be that as it may, let's say you simply want a saved query to run manually
either at the end of the day or once a week or whatever, this is how you
would go about it:


VERY IMPORTANT: Make a backup copy of your database and makes changes TO THE
BACKUP FIRST as a test. If all goes well, you can rename your original and
pop the backup in it's place, but this way you always have a "go back" point

;-D



First, determine which field in your Main table is going to be the one that
determines whether a record should be copied to the Archive table, then
deleted. You didn't specify, so for the sake of getting this going, I'm
going to pretend that field is called Shipped and it's a date field.

- Go to the database window and select Queries.
- Create Query in Design View
- Select the table with the data you want to use (in this example the Main
table). You can click cancel to get out of the "choose tables" mode.
- On the Query menu select Append query, choose the Archive table as the
destination.
- select all the fields from your Main table (not the asterisk, but all the
field names), and Append To should fill in automatically if the tables are
designed the same.
- On the Shipped field, Criteria row, type in Is Not Null. If you change
your view to datasheet view you will see that only records with the Shipped
field populated are listed. This is a good way to see what data the query is
looking for.
- Save the query as something descriptive, "MoveToArchive" or something and
close it.
Note the query has a Plus sign - this shows you at a glance that it's going
to actually DO something to modify your data.
- Double click your saved query to run it this will copy the Shipped records
to the Archive table. Check the Archive table to be sure that all these
records were added correctly.

That's the first half - I don't want to start deleting anything until we
know the data is being copied correctly, ok?


Let me know how this works out, I'll be back here in the morning.

Susan
 
Back
Top