Results 1 to 9 of 9
  1. #1
    Josen is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Oct 2019
    Location
    London
    Posts
    4

    Field names in new table

    I have used a Make Table query to create a new table from data within another table, the Field names in this new table need to be different and will be dependent on original values used for the make table query (all of which are stored in another table).

    Am I correct that specifying new Field names with anything other than a plain text value is not possible (e.g. using values from another table is not possible), when using a query?

    I presume I would need to use a VBA method to rename the fields in the new table, using a form of lookup (to another table) for the values I require?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Query might be able to use DLookup() to pull values from other table to use as field names.

    I have code (basically never used) that creates table with field names prescribed by data in another table. Involves recordset objects.

    Why do you need this?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    It doesnt seem normal to have to keep making tables. You make the tables in the beginning then add data. Maybe add 1 new table over time because of a new schema, not new data.

    You CAN use a make table and name the fields anything you want just join the other table into the query.
    or assign labels:
    select [UserName] as MyName from table

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Josen,

    Please provide a description of the "business" that this database is to support (simple terms)
    As ranman advised - it isn't a normal practice to have to keep making tables.
    Perhaps you have some "special conditions" for which there may be options, but readers need to
    understand the requirement before offering focused responses.
    Good luck.

  5. #5
    Josen is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Oct 2019
    Location
    London
    Posts
    4
    The database is being developed to store diagnostic data from system(s). The main data set is over 100 characters long and not fully understood due to limited information available at present - such that it is required to be decoded later (once understood) into meaningful data. The table(s) being created were being created rather than selected because there would have to be a large or unknown number of fields in the main data table to accommodate different diagnostic data types and unknowns, at least from my understanding.


    My intention therefore was to query the main table of data for the desired data (create a new table), and use other tables to pull out the fieldnames and then update the raw values of data to decoded values - again using values in another table to do so.


    My approach is probably not ideal and would have been more straightforward in VB but as I am new to this I wanted to see what could be done using queries alone!


    Thanks to the responses I have changed my approach and have renamed captions using VB on the new table which satisfies the main reason for this post. I had tried doing this in some form in the query SQL view - but I got an error that the statement contains a reserved word/punctuation is incorrect. Given that this renaming would have to be done to a number of fields a VB approach makes even more sense!

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Fieldnames in a SELECT cannot be dynamic. Never actually tried DLookup() with MakeTable.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I suggest you try a little harder to give us a little more detail. If you really don't know the diagnostics or data to be identified, potential fields involved, the analyses to be done on whatever information is gathered, you are not nearly ready to be talking physical database -Access or any other.

    Mock up something to help with discussing the general study being undertaken, and collection, manipulation and analysis anticipated by the players involved. If you are the identified designer/builder of a database to support this seemingly unknown activity(ies), then you need a pencil and paper to capture any and all requirements as they evolve.

    Think of it from another perspective --if you were going to hire someone to develop a database for this "activity", what would you say was the requirement such that they could proceed?

    Good luck with your project.
    Last edited by orange; 10-17-2019 at 07:11 AM.

  8. #8
    Josen is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Oct 2019
    Location
    London
    Posts
    4
    Thanks for the advice Orange. I'm quite new to databases but have done some creative solutions in Excel, sometimes working towards an unknown end!

    As to dynamic/changing field names, I'll stick to the VB approach - I think I'll be using VB a lot in this project. Perhaps posting less here as a result!

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    All I was suggesting is, if we are to offer any focused advice, we need something concrete to work with.
    With Access we're talking vba.
    I don't understand your " dynamic/changing field names ". Perhaps you're describing field values??

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

Similar Threads

  1. Replies: 7
    Last Post: 03-21-2018, 04:58 AM
  2. Create Table With Field Names From Current Table
    By jo15765 in forum Programming
    Replies: 5
    Last Post: 05-22-2017, 03:33 PM
  3. Filtering on Field Names from Table
    By reddog1898 in forum Access
    Replies: 3
    Last Post: 05-13-2011, 10:47 AM
  4. Get Field names from a table in CurrentDB()
    By e.badin in forum Programming
    Replies: 8
    Last Post: 01-06-2011, 02:17 AM
  5. Replies: 5
    Last Post: 03-31-2009, 09:16 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
  •  
Other Forums: Microsoft Office Forums