Solved Archiving Data from Multiple Tables

[Uncle Ned]

Hi Everyone, looking for some help getting the code on this one right. I could probably get this to work on my own, but I am self taught and would probably not be able to achieve the result in the most efficeint manner.

I've put together a simple database to illustrate what I am trying to achieve

There are three tables that hold data relating to orders - items in the order - subcomponents on each item.

I want to be able to archive all related items into three other tables once the ship date has passed 30 days from todays date

Hopefully what I've attached will explain things a bit better

Thanks in advance for any help

 

[oleronesoftwares]

1. Create the three related tables using same data fields of each of the three initial tables, the only difference been table names

e.g if you have three original tables named table orders, orderdetails and components,
the three new tables can be named orders1,orderdetails1,components1

Then create an insert statement e.g
INSERT INTO orders1
SELECT * FROM orders
WHERE shipdate> DateAdd("M", -1, Date( ));

 

[Gasman]

You would use "d" and 30 for the DateAdd as you specified 30 days ?
Then do you delete the archived records from the original table?

You could just have an archived flag/date set and ignore for future use?
Plus I would have thought it would be < the calculated date?

 

[Uncle Ned]

You would use "d" and 30 for the DateAdd as you specified 30 days ?
Then do you delete the archived records from the original table?

You could just have an archived flag/date set and ignore for future use?
Plus I would have thought it would be < the calculated date?

Yes, I want to delete the archived records from the older tables

 

[Uncle Ned]

1. Create the three related tables using same data fields of each of the three initial tables, the only difference been table names

e.g if you have three original tables named table orders, orderdetails and components,
the three new tables can be named orders1,orderdetails1,components1

Then create an insert statement e.g
INSERT INTO orders1
SELECT * FROM orders
WHERE shipdate> DateAdd("M", -1, Date( ));

This is easy enough to do on the Orders table, becuase it has the ship date in it. However, the product and component tables don't have a ship date. So I have to start with the table on the bottom of the three, e.g. components and then onto products before finishing with the orders table.

My plan was to link everything through a query, but I'm not sure if this is the best/easiest way of doing it

 

[arnelgp]

here take a look at Module1.
click the button on the form. If you want to Delete the records from the Main tables,
on the Click event of the button, change bolDelete to bolDelete:=True.

note all archive tables i change the pk fields to long integer instead of autonumber.

 

[oleronesoftwares]

This is easy enough to do on the Orders table, becuase it has the ship date in it. However, the product and component tables don't have a ship date. So I have to start with the table on the bottom of the three, e.g. components and then onto products before finishing with the orders table.

My plan was to link everything through a query, but I'm not sure if this is the best/easiest way of doing it

If the other tables don't have a ship date , then the other tables must be related to the orders table via a key field, you can then use the insert statement with joining from orders table to other tables

 

[Uncle Ned]

here take a look at Module1.
click the button on the form. If you want to Delete the records from the Main tables,
on the Click event of the button, change bolDelete to bolDelete:=True.

note all archive tables i change the pk fields to long integer instead of autonumber.

Wow, I'm impressed that you managed to do so much in so few lines of VBA

I really appreciate the help on this