moving records to another table based on field value

fingermouse

Board Regular
Joined
Dec 13, 2013
Messages
117
Hi – I only created my first access database yesterday,so I’m a complete novice.


I have created an Access 2007 database with 2tables: ‘Independent Assurance’ and ‘Independent Assurance - Complete’.

Both tables have identical structures interms of columns/fields. ‘Independent Assurance - Complete’ is currentlytotally blank, apart from the column headers.



I need to automatically copy all records fromthe ‘Independent Assurance’ table to the ‘Independent Assurance - Complete’ tablewhere the field/text box named "Status" has a value of "Complete".



It would be great if someone could assist with a query or VB script to automate this, any help much appreciated.



Thanks,


Cal

 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Most people would advise to leave as is and exclude status=complete from normal processing, unless you are approaching size limits.
However use the query designer to create two queries. That way you also learn more about Access.

1. An append query to select all records with that status.
2. A delete query to select all records with that status to delete from table 1 if previous query was successful.
 
Upvote 0
Thanks Welshgasman, much appreciated. i wouldnt even know where to begin attempting to create an append query, but thanks anyway.

Thanks
Cal
 
Upvote 0
i wouldnt even know where to begin attempting to create an append query
Google is your friend here!
Just Google "create an append query in access", and you will get tons of articles and tutorials that show you how to do this.

Being a visual learner myself, I usually prefer watching the YouTube tutorials, where you can see every step along the way.
 
Last edited:
Upvote 0
Hi Joe,

Many thanks for responding, much appreciated. I found a tutorial on the microsoft office website, and can now create an append query! :)

What I should have said in my original post (apologies for not making this clearer) was that I want to MOVE/CUT records completely out of one table and transplant them into another, based on a value of 'complete' in my 'status' field. The append query only copies the records over.

Can this be done? If so, how should I approach this?
 
Upvote 0
You then create a DELETE query, BUT make sure all the records have been copied over first.
 
Upvote 0
You then create a DELETE query, BUT make sure all the records have been copied over first.

Thanks welshgasman - in hindsight, Im not sure I want to delete these records ...

is there a easy way to create a query then link that query to a checkbox or button to toggle between those with a field status value of 'complete' and those with a status of 'ongoing'?
 
Upvote 0
Have you considered welshgasman's original proposal:
Most people would advise to leave as is and exclude status=complete from normal processing, unless you are approaching size limits.
However use the query designer to create two queries. That way you also learn more about Access.
Instead of having two tables, you would just add a new field to your existing table where you indicate the status.
Then, you can get whatever you want using a query.
This is the generally accepted "best practice" for issues such as these.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top