Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2002
    Posts
    59

    Unanswered: Naming a new Table from a Make Table query

    I want to use a Make Table Query to generate a new read-only Table. This I can do but my problem is that I want to be able to name the new Table containing only the records from a calendar year; e.y. Rents2001 and the next year another new table would be created and called Rents2002 and next year Rents2003 ...............

    I require the Table to be generated yearly. I know I could do this in other ways but I really require the Table as once I have it I will be doing other things with it to give the final report.

    Any suggestions how I can generate the Table with the YEAR being in the Table Name as part of running the Make Table Query? Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I take it that you are doing this in MS-Access (using the Jet engine), not MS-SQL, right?

    -PatP

  3. #3
    Join Date
    Jun 2002
    Posts
    59
    Yes - in Access

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Hmmm... This is a bit of a challenge in MS-SQL. I'm sure it can be done in Access, but darned if I know how. You might want to pose this question in the Access forum to see if one of the folks there have a clue.

    -PatP

  5. #5
    Join Date
    Dec 2003
    Posts
    31
    create SP in the mssql that running something like

    declare @TableName varchar(255),
    @strsql nvarchar(4000)

    set @TableName = 'rent'+cast(year(getdate()) as varchar)

    if not exists (select 1 from sysobjects where xtype = 'u' and name = @TableName
    begin
    select @strsql = 'craete table ' + @TableName + '(colname datatype, .... )'
    exec (@strsql)
    end

    select @TableName as Tablename

    and try to call this SP from Access...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •