Automatically moving records to another table

[bethanw88]

Hi there, I am fairly new to access but have been charged with putting together acompany database.

At the moment I have tables for business enquiries that are in progress, and one for current contracts, and one for past contracts.

I would like a way of automatically moving a record from one to another as simply as possible. When I try and use append it does not seem to work. I can not just use a query for dates as there are other factors which keeps a record in each section.

Also the headings in each table are different, though I would like the information to be copied over when the fields are the same - or similar, i.e. what it appears you can do in the append to tool.

Any help would be greatly appreciated. I was hoping something like ticking a confirmed box etc could send it to current, and eg selecting a completed box could send it to previous - keeping the key information, but with additional and different fields also in the tables.

Any help would be greatly appreciated!

Thank you

 

[spoole]

From the info you have given so far:

What about having just 2 tables, where table 1 is the same as your current business enquiries table but also has a column added called status which records the number 1, 2 or 3. the second table contains the description for the status numbers ie. 1 = In Progress, 2 = Current etc.

however if you have many contracts past, current etc for one client which typically businesses do then you will also need to ensure that every contract in that table has a unique number. This way you dont have to worry about moving data from one table to another.

In the ideal world What I would do:

Store start and finish dates for the contract and base everything on this.