Transfering info between tables

C

Clare

Can I remove lines from one table and transfering that info to an archive
table when product has been shipped.
 
S

SusanV

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
 
S

SusanV

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?
 
C

Clare

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
 
S

SusanV

I think I can work with it. Are you entering data via a form or directly to
the table?
 
M

Marshall Barton

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

Clare

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

SusanV

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
 
B

Bob Miller

A better method IMO would be to put a check box shipped field in th
table if you don't already have say a shipped date field. When an ite
is shipped check it. Any queries can disregard the shipped records o
not as needed.
If you insist, you could run an append query of items that have bee
shiped in your table. Then you could run a make table query o
unshipped items, delete the orginal table and rename the just mad
table to the original. This can be done in code or macro. I know i
is messy. Perhaps someone else has a cleaner way.
 
Top