Moving records from one table to another

[Nina]

Hey guys, i hope someone can help me with this problem.....

I have a database that has an Allocations table, which deals with the allocation of units of stock to a hiring employee. When the item is booked out to an employee, an allocation date is entered into the system. A return date is filled in when they are done with the item and return it to storage.

Ultimately, each unit of stock will only be assignable to one employee at a time (altho not worked out how to do this yet).

What i would like, is that when an item is returned to stock, and therefore an assignment is closed, that the record is removed from the Allocations table, and moved to a Closed Allocations table. (my client does not want to delete the closed allocation but for the record to be archived for a period).

My other option would be to create a query which only displays open Allocations, but this has not worked quite as expected.

Is the movement of records as i need possible in Access? and if so how do i do it?

I am using 2000 format on Access 2003

Thanks in advance to anyone that can help!

 

[John Vinson]

Hey guys, i hope someone can help me with this problem.....

I have a database that has an Allocations table, which deals with the allocation of units of stock to a hiring employee. When the item is booked out to an employee, an allocation date is entered into the system. A return date is filled in when they are done with the item and return it to storage.

Ultimately, each unit of stock will only be assignable to one employee at a time (altho not worked out how to do this yet).

What i would like, is that when an item is returned to stock, and therefore an assignment is closed, that the record is removed from the Allocations table, and moved to a Closed Allocations table. (my client does not want to delete the closed allocation but for the record to be archived for a period).

The simplest solution might be to not move the records at all:
instead, have a Yes/No field OPEN in the assignment table. Set it to
TRUE for an open allocation, and to FALSE when it's closed.

My other option would be to create a query which only displays open Allocations, but this has not worked quite as expected.

In what way? What was the query, and how did it fail to work?

Is the movement of records as i need possible in Access? and if so how do i do it?

Certainly. You would need two queries run in succession - an Append
query to append the record from [Allocations] to [Closed Allocations],
followed by a Delete query to delete the record. These two queries
could be called from VBA code or from a macro (code is better because
you can wrap both queries in a Transaction ensuring that they either
both get done, or neither).