By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,519 Members | 2,416 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,519 IT Pros & Developers. It's quick & easy.

Make Table Query - how to use the same query to make different tables

P: 23
Hi, I'm a bit stuck with an access database. I'm using access 2000 if that's any help.
Right, it's 3:40am right now and I'm rather tired, but I *hope* this makes sense.
I have a table which contains the main data of the database, records of information of grants. For each financial year, the old data needs taking out of the table. Basically I have two options: delete the old data from the year before, or back it up in a new table. I'd rather be able to back it up, just so it's still on record. Doing a Make Table query then running a delete query will do this, but with one small problem: when it goes to make the new table, the new table name is built into the query. So if, come the end of the second year, and I back it up, it will create the table of the same name (and so overwrite the table from the year before).

I need the Make Table query to EITHER automatically put in the year into the table name (so for example, the new table name to be "YearEnding##", where ## is 07, 08, 09, etc (corresponding to the year). OR I want the make table query to ask for the name for the new table: so bring up an input box for the user to type in the name for the new table.

I'd be really greatful for any help anybody can give. It's probarbly something simple that will suddenly come to me in the morning, but right now I can't think; I can barely remember my name. And... I seem to have lost my glasses. If anybody knows where they are, could you post that too?
Thanks
Mar 3 '07 #1
Share this Question
Share on Google+
3 Replies


MSeda
Expert 100+
P: 159
you'll have to build the sql string in vba. create a command button on a form and in the on click event enter code similar to this.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command2_Click()
  2. dim tblname as string
  3. dim mySQL as string
  4.  
  5. tblname = "YearEnding" & datepart("yyyy", date)
  6.  
  7. mySQL = "SELECT field1, field2, field3 INTO " & tblname
  8. mySQL = mySQL & " FROM myTable;"
  9. docmd.runsql mySQL
  10.  
  11. docmd.openquery "Delete Query"
  12.  
  13.  
you can copy and paste the SQL from SQL view of the saved query and just replace the table name with the variable.
Mar 3 '07 #2

P: 23
Thanks. i knew it would be simple, I should have known that. In my defence, I'd not slept properly in a few weeks, and I'd not slept at all that night. Also, I have the insanity/stupidity plea.
Thanks for your help though.
Now, if anybody can tell me where my glasses are, you get a chocolate brownie.
Mar 3 '07 #3

NeoPa
Expert Mod 15k+
P: 31,494
I'm not sure, but I suspect they're perched up on your head where you left them earlier :D
I'd answer your question for you too, but it looks like MSeda has already done a perfect job for you.
Mar 4 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.