Sorry, quite hard to explain this. I have an Access application that contains a table called RMCosts. Every fortnight we update all tables in this application from our ERP system. I've been asked to retain an archive of the previous 24 RMCosts in another access file which I've managed to do OK. The code I wrote simply runs Create table SQL that effectively copies the current RMCosts to the other Access file and datestamps it (RMCostsToYYYMMDD) before creating the new RMCosts file in the live application. A new requirement has been given to me. I now have to include the last 24 RMCosts in a huge select query. Easy enough to do manually by attaching the last 24 RMCosts tables and pulling in the relevant fields. Doing this manually though makes it more likely that it will be overlooked or mistakes will be made.
I'd much rather find a way of getting the query to work out the that it should obtain RMCost-1 (2 weeks ago) from table RMCostsTo20080703, RMCost-2 (4 weeks ago) from table RMCostsTo20080620 etc etc. Expecting that this wasn't likely to be easy (or even possible) I considered writing a simple routine to manage tables called RMCosts-1, RMCosts-2 thru 24. It goes something like this in my head.
1. CopyObject RMCosts-23 to RMCosts-24, 22-23, 21-22 etc.
2. Copy RMCosts to RMCost-1 and RMCostsToYYYYMMDD
3. Create RMCosts from ERP as before.
I know how to do this within the existing application and the effect would hopefully be to have 48 tables - 24 datestamped and 24 with names that represent ageing i.e. -1 to -24. I could then connect my select query to tables RMCost-1 to RMCosts-24 and run without the need to edit first.
This may not be the best approach in any case but I could make it work. That said I can't live with the tables in the application from which I run the update. The tables are very large and the application is issued via email. I need to create the and manage the naming of these 48 tables in an external mdb. I don't see how I'm going to be able to do that with commands like CopyObject
Does anyone have a good way of doing this?