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

[Robertf987]

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

[MSeda]

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.

Private Sub Command2_Click()
dim tblname as string
dim mySQL as string
 
tblname = "YearEnding" & datepart("yyyy", date)
 
mySQL = "SELECT field1, field2, field3 INTO " & tblname
mySQL = mySQL & " FROM myTable;"
docmd.runsql mySQL
 
docmd.openquery "Delete Query"
 
 

you can copy and paste the SQL from SQL view of the saved query and just replace the table name with the variable.

[Robertf987]

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.

[NeoPa]

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.