moving records to another table based on field value

[fingermouse]

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

 

[welshgasman]

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.

 

[fingermouse]

Thanks Welshgasman, much appreciated. i wouldnt even know where to begin attempting to create an append query, but thanks anyway.

Thanks
Cal

 

[Joe4]

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.

 

[fingermouse]

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?

 

[welshgasman]

You then create a DELETE query, BUT make sure all the records have been copied over first.

 

[fingermouse]

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

 

[Joe4]

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.

 

[welshgasman]

Yes, but I'd say a little advanced for a starter.
Look at the filter option on the ribbon, you can filter on any field you can see.
You could also filter in code to do the same thing.

https://docs.microsoft.com/en-us/office/vba/api/access.form.filter(property)