how to transfer record from one table to another? 1

[gwendolyn8]

Ok, I'm working on an inventory database. Right now, there is a table for all of the computers that we have in the building, however, it would be cleaner to have a separate tables computers that have died and were sent to surplus.

My question is how would I transfer a record from one table to another (ie, a computer in the current computer list dies and its record needs to be transferred to the computer surplus list)?

I'm already assuming that obviously both tables will need to have identicle fields.

Thanks!
Gwendolyn

 

[SilentAiche]

gwendolyn,

Personally, I think it would be cleaner to add something like a "status" field to your existing table. Then use the status field to record "In Use," "Surplused," "Being Repaired," etc.

Just a thought!

But to answer your question, a Make Table query should do what you're asking.

Hope this helps.

Tim

 

[gwendolyn8]

I am currently using the system you suggested, but if it is doable, it would be much more preferable in the long run to transfer the records to a separate table. I'll look into the make query command.

 

[missinglinq]

I think SilentAiche is right. One table should be enough! Even over a 10 or 20 year period, how many computers can go thru the system? I'm assuming since you want to retain the record for computers that have been "sent to surplus" that your company is actually holding on to them. If this is true, sooner or later someone is going to decide to rehab some of these units, and they'll need to go back into the "currently in use" list. The simplest way to do this is to have an "In Use", "Yes/No" field that is checked if the unit is currently in service. Uncheck it if the unit is retired. Have two forms in your database, one for "In Service" and one for "In Surplus" and have the query that populates the form look at this single field to decide which form a given unit goes into.

Hope this helps.

The Missinglinq

It's got to be the going,
not the getting there that's good!
-Harry Chapin

 

[SilentAiche]

Dear missinglinq,

A Star for you.

Not so much for brilliance (which, of course, was evident), but for using the phrase "[blue]I think SilentAiche is right.[/blue]"

Just needed a little reassurance... (from an old pro).

THanks,
Tim

Seriously, gwendolyn, can you explain further why a separate table is necessary? I don't mean to dog you on this subject; I'm just curious. Many of us latch onto ideas that, quite frankly, need another look. That's what this amazing community does best.

Best wishes!
Tim

 

[gwendolyn8]

Ok, I was trying to be breif, but I'll flesh out the situation a little. Basically, there is a third catagory, too, which is storage. In other words, what I was hoping for was to have three tables for our computers (current, surplus, and storage- in which current is what's on the floor, surplus has been retired off to the warehouse, and spares are sitting around waiting to be deployed) that I can move record from one to another.

While it had occured to me (and is in fact how I have it currently set up) that I could simply mark the surplussed and spare computers accordingly and have everything all in one big table, there are two issues, there. One is that the table, in the long run, will become very large and unweildy, and the other is that it would be very, very handy to be able to look at each list in a separate table for a quick at a glance look at exactly what's there.

I know I could write reports that would let me look at this info, and I admit that I haven't worked with this area of access enough to know if what I'm hoping to do (ie, be able to move a record from one table to another) is a poor idea, but from an end-user-to-be perspective I like the idea very much.

Ok, so, that's everything you asked for. Thanks for your help so far, I'm looking foward to hear what else you have to say.

Thanks,
Gwendolyn

 

[mph1]

Please excuse me for butting in! I think Tim is correct. Taking into account your last comment "One is that the table, in the long run, will become very large and unweildy"You shouldn't be to concerned with the number of records in a table, rather the size of the database itsef. It the database is approaching 2 GB, then there are other things that can be done.


HTH


An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[gwendolyn8]

Well, I know that in theory, it doesn't matter how big the table is, but I'd rather that it not, down the road, be littered with lots of computers that are no longer even in the building. We just need to hold onto that information because when our university does its annual inventory, there are always inevitable things that were legitimately shipped off to surplus that somehow don't make the master list that they wind up trying to find (and charge us if they can't).

Therefore, it would be handy to retain the information, but not have to keep it with the active information. Because all of the current computers will eventually be retired to surplus, it would very handy to be able to move them to a separate table when that happens.

 

[gwendolyn8]

So, mainly, what I'm wondering is, is it impossible/a bad idea to move records from one table to another, or do you just not follow my logic for wanting to do it?

 

[SilentAiche]

gwyndolyn8,

Well, although the vote is 3-1 against your method, it is certainly doable.

Back up your database first.

Make a copy of your current table structure (no data). Create an Append Query to move records based on whatever your criteria are, and then use a Delete Query (based on the same criteria, of course) to remove the records from the first table. If this is just going to be an occasional activity, I'b probably leave it at that. Or, perhaps you could take it a step further and create a macro to run the two queries.

Good luck!
Tim


[gray]_______________________________________
Although not artistic by nature, I sure can draw a blank...[/gray]

 

[gwendolyn8]

Thanks for the info. I guess what I'm wondering is why you guys recommend against it? I don't mean to be headstrong here, but I don't see the problem with it.

 

[SilentAiche]

Philosophy, I guess. I like the idea of having like data in one place/table, and then using the other tools available to manipulate the data. I would use queries and reports to distinguish between current and surplused equipment.

Also, it seems to me that there is more risk in moving the data from one table to another and deleting it from the original table. If something goes wrong during the Append/Delete process, either human error or computer malfunction, you could be screwed. Less movement is safer, in my opinion.

That's just a couple of reasons, but there are others, such as having one table makes it easier to tweak the design down the road since you would only have to do it once.

That's my two cents worth - good luck!
Tim


[gray]_______________________________________
Although not artistic by nature, I sure can draw a blank...[/gray]

 

[mph1]

Access, like most anything else in life, there is usually more then one way to accompolish a task. The result can be the same, however, some ways may take longer, ie, steps required that aren't necessary.
As Tim pointed out, what you want to do can be done the way in which you want, but we don't think it to be the most prudent.

Also, as indicated ----- BACKUP your database first.

An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[SilentAiche]

gwendolyn8,

You might also want to read Paul Litwin's "Fundamentals of Relational Database Design." It has been cited in these forums a number of times with a hyperlink, but I can't find one right now (sorry). You should be able to Google it (or someone may post it here for us).

Tim


[gray]_______________________________________
Although not artistic by nature, I sure can draw a blank...[/gray]

 

[Lightning]

gwendolyn8

One is that the table, in the long run, will become very large and unweildy,

Access will happily process thousands of records reasonably quickly, so this isn't really a problem from the processing point of view.

and the other is that it would be very, very handy to be able to look at each list in a separate table for a quick at a glance look at exactly what's there.

Assuming that you are using some sort of switchboard in your database, try setting up three (or however many are required) option buttons to open this table, and apply a different filter to each button. This way you will only display the records that satisfy the filter, eg "In Use," "Surplused," "Being Repaired," etc.

I did (once) set up a similar system to what you're proposing. The client wanted to archive old data, but keep it in the same database. It turned out to be useless in the long run, because the users would archive data, only to discover that they needed the data again within a short period of time.

If you really must move the data into another table, use some sort of time constraint, so that old data has not been used for at least a year, and then don't transfer it for at least another six months, otherwise you'll end up moving it back again.

Just some thoughts.

HTH
Lightning