469,085 Members | 1,051 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,085 developers. It's quick & easy.

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

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
3 3223
MSeda
159 Expert 100+
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
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
32,158 Expert Mod 16PB
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.

Similar topics

8 posts views Thread by brian kaufmann | last post: by
6 posts views Thread by scottyman | last post: by
6 posts views Thread by Ian Boyd | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.