Results 1 to 5 of 5
  1. #1
    steve_odea is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    10

    Creating a Table from a query but automatically adding a lookup column

    I have reached a bit of an impasse on quite a specific design aspect and wonder if anyone has any tips.

    I have a large table which is linked to a separate database and is therefore not editable.



    From this table, I have created a query to identify a subset of rows from the larger set.

    What I want to be able to do is now add a new column to the data that is in the query, but which is based on a lookup in another table.

    I cannot add the column with the lookup values to the query, so I have tried to use the make table option to create a new table, but then I have to manually add the lookup column.

    Can anyone think of a method which creates a subset of a larger table, but then adds a column which allows the user to select from a drop down values based in another table, without a manual intervention?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I cannot add the column with the lookup values to the query, so I have tried to use the make table option to create a new table, but then I have to manually add the lookup column
    why not? Providing there is some reference in the main table to provide a link to the lookup table, you can add it.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    but which is based on a lookup in another table
    Is this by chance a Look Up FIELD in the table?? I NEVER use Look up FIELDS because they hide what is really going on......

  4. #4
    steve_odea is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    10
    Hi Ajax thanks for the response. The lookup I wish to add is not included in the main table, it's completely stand alone.

    Essentially I have a large dataset in a non-editable main table.

    This table contains a list of individuals, but I wish for the user to be able to categorise a subset of this list into groups, depending on the users choosing.

    I thought that I could create a query that just showed the subset, and then simply add on a new column for the drop down, but I don't seem to be able to do this.

    I then produced the make table query to produce a stand alone table for the subset, but then I had to add the lookup column manually to get the end result.

    The main (original) table is updated periodically and therefore the subset would change, but I didn't want to have to go through the process of manually adding the lookup column each time I re-ran the make table query.

    If you had any suggestions of course...

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    still not quite sure what the issue is since the apparent solution is simple

    if using a make table query you would have something like

    Code:
    SELECT maintable.*, 0 AS lookupCol
    INTO newtable
    FROM maintable
    but your better option would be create your newtable as required, including the new column required and use an append query

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

Similar Threads

  1. Replies: 3
    Last Post: 05-08-2015, 11:08 AM
  2. Replies: 2
    Last Post: 04-15-2015, 10:05 AM
  3. Replies: 1
    Last Post: 11-15-2013, 11:42 PM
  4. Adding a lookup to a column
    By revnice in forum Access
    Replies: 4
    Last Post: 08-16-2010, 12:58 PM
  5. Adding new column to make-table query
    By dtn118 in forum Access
    Replies: 2
    Last Post: 08-03-2008, 06:51 AM

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