Results 1 to 7 of 7
  1. #1
    rscott7706 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2009
    Location
    Lakeside Ca
    Posts
    24

    Cool How to extract the data from additional columns in a lookup based on "I will type the values..."

    I know this has been answered, and just can't find the result that answers my need.



    I created a lookup table based on "I will type the values I want."

    The table has two columns, one lists the member type, and the other description of that type.

    So now, I want to present both fields in a report or query, when I create a report, I only get the first field, and don't have option to present the second. If I do a query, same thing, no way to add second column.

    This should be simple, but I can't find an answer on the forum or using Google.

    Thanks, Ron

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    In the query , join to the lookup table , pull BOTH columns into the query, then place them in the report.

  3. #3
    rscott7706 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2009
    Location
    Lakeside Ca
    Posts
    24
    I apologize, not an Access wiz (to say the least).

    I do not find a "join to the lookup table" option on query screen.

    Click image for larger version. 

Name:	query.jpg 
Views:	10 
Size:	172.6 KB 
ID:	25146

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    once you place both tables on the field, use the mouse , grab a field , drag it to the lookup table ,and drop onto the same field in that table.
    that is a join.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    looks like you have set membertypeid as a multivalue field. This is only appropriate if a member can be more than one type. If a member cannot be more than one type, it should just be a field of long datatype.

    to resolve your problem, click on and drag the membertypes table onto the query grid

    then - for current setup, drag a line from MemberTypeID.value (should be just below where the view of the table cuts off) across to the equivalent field in the membertypes table. Then you can drag down the typename field or whatever you require as the second column

    If you change the multivalue field to a normal field, do the same as above but drag the line from the MemberTypeID field itself

  6. #6
    rscott7706 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2009
    Location
    Lakeside Ca
    Posts
    24
    I know your going to start thinking me a dunce, but this is not based on two tables, I entered the data in a lookup wizard choosing the option ""I will type the values I want."", not a lookup table

    This field is just a field in the Members table. Here is a screen shot of it. I can't see the two columns in a query when I add that field to a query, just the first one, the one with A1, A2, ... but not the second that contains the descriptions "1 Year Membership", "2 Year Membership", etc.

    I hope I explained this well, or am I missing the point on what your conveying to me?

    Click image for larger version. 

Name:	members-table.jpg 
Views:	9 
Size:	134.9 KB 
ID:	25147

  7. #7
    rscott7706 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2009
    Location
    Lakeside Ca
    Posts
    24
    Got ya... I thought I might be making the wrong choice when I did the wizard. I may just start over and make the right choice there on next try.

    Thanks for your help!

    Ron

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

Similar Threads

  1. Unbound text box data type and "Delete" function
    By gaker10 in forum Programming
    Replies: 16
    Last Post: 06-13-2014, 10:46 AM
  2. Replies: 4
    Last Post: 01-11-2014, 11:57 PM
  3. Replies: 2
    Last Post: 01-03-2014, 09:35 AM
  4. Replies: 3
    Last Post: 05-31-2013, 04:32 PM
  5. Replies: 2
    Last Post: 05-17-2011, 02:40 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