Results 1 to 7 of 7

Renaming Table based on Query Results

  1. #1
    Oxygen Potassium is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    37

    Renaming Table based on Query Results

    I have a query that returns a top value from a table column:


    Code:
    SELECT DISTINCT TOP 1 [MASTER DATA].Country
    FROM [MASTER DATA];
    I then uses that queries result to create a new table based on that result:
    Code:
    SELECT [MASTER DATA].* INTO Temp1
    FROM [MASTER DATA], Article_Country
    WHERE ((([MASTER DATA].Country)=[Article_Country].[Country]));
    Is there a way to systematically name the Temp1 table as the selected [Article_Country].[Country] result from the first query?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,603
    Yes but you'd have to be building the SQL in VBA. I'm curious why you want to create tables though, as it's not typically necessary. If it is:

    strCountry = DLookup(...)
    strSQL = "SELECT [MASTER DATA].* INTO " & strCountry & " FROM..."
    CurrentDb.Execute strSQL, dbFailOnError
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Oxygen Potassium is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    37
    I guess you're right that a table does not necessarily need to be built for this, but I want to be able to export the results with the name equal to the query results

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,603
    You can use OutputTo and control the name of the exported file.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Oxygen Potassium is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    37
    Great idea. I think I was just stuck on the process.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,603
    No problem, post back if you get stuck.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Oxygen Potassium is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    37
    This was my ending code if anyone is interested

    Code:
    Sub Rename_Tbl()
    
    DoCmd.OutputTo acOutputTable, "Temp1", acFormatXLSX, "C:\Users\UserID\Documents\" & DLookup("Country", "Article_Country") & ".xlsx"
    
    
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Renaming Query and table in MS Access 2016
    By CHEECO in forum Access
    Replies: 3
    Last Post: 04-17-2016, 01:03 PM
  2. Replies: 3
    Last Post: 02-22-2016, 03:38 PM
  3. Renaming Files using data from a table or query
    By enzokevin in forum Programming
    Replies: 7
    Last Post: 10-17-2014, 05:42 AM
  4. Replies: 1
    Last Post: 04-23-2012, 10:40 AM
  5. Renaming Fields on Table or Query
    By jo15765 in forum Access
    Replies: 4
    Last Post: 12-09-2010, 01:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums