Results 1 to 9 of 9
  1. #1
    RustySmith is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    4

    Unhappy Table shows sequential list number instead of list text when referring to a list from a list

    My question is: how can I change the view of a column to text when it is referring to another list that is also referring to another list?
    Is there a general problem when referring to a list that is from another list in Access?

    Here is the problem and how I come to it:

    I have three tables.
    The first table (2 columns) has a list which defines types of methods I use.
    1 HS-GM-MS
    2 NMR
    3 ICP-MS

    The second table (three columns) has a Look Up List list which reads these method types and from a drop down box I can select to make this table, where I also in a third column add the date I performed the method:
    1 HS-GM-MS date1
    2 HS-GM-MS date2
    3 ICP-MS date1


    Table that is made from another list: png:Click image for larger version. 

Name:	3.PNG 
Views:	9 
Size:	13.5 KB 
ID:	31631
    The problem arises in the third table (three columns), what I want to see is a list of items and select from a drop down list the method by date that was performed like this:
    1 item1 HS-GM-MS date1
    2 item2 HS-GM-MS date2
    3 item2 ICP-MS date1


    4 item3 Hs-GC-MS date1
    How I setup the dropdownlist: png: Click image for larger version. 

Name:	1.PNG 
Views:	9 
Size:	21.7 KB 
ID:	31629



    But I actually see this:
    1 item1 1
    2 item2 1
    3 item2 3
    4 item3 1

    Where it shows the index number of the method instead of the name I have given in the first table.
    Like this: png: Click image for larger version. 

Name:	2.PNG 
Views:	9 
Size:	16.2 KB 
ID:	31630




    Thanks for any hints, help or advice.
    I also am lacking words to describe these problems and so any guidance to correct search words would also be a help.

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Don't use a lookup column in your table design. They are a pain - http://access.mvps.org/access/lookupfields.htm
    Simply use a number field and do what you were doing by linking the field to the look up table.

  3. #3
    RustySmith is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    4
    Thanks Minty, can you explain what you mean by number field?

  4. #4
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Sorry - your look up field hides the fact that you are actually storing the AnalysisType ID as your Analysis type in your Analysis Performed table, (the foreign key FK) , not the actual text.
    Whilst this is exactly what you should be doing - the lookup field type confuses the issue, and makes it look as if you are storing the text.

    Just to confuse issues further if you use the combo wizard (as you showed) Access cocks the whole shebang up even further and tries to hide the already "hidden" FK field, and fails causing you the problem you experienced.

    So simply have a field called AnalysisTypeID in your Analysis Performed table, that stores the FK ID.

    (Wow that's hard to explain )

  5. #5
    RustySmith is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    4
    Thanks, I have been able to find a lot more information about this problem now and it is apparently a very common novice issue.

    However, I cannot see how to solve it still, how exactly can I store the FK ID?
    Can you suggest a better method than even using LookUps, what would the ideal thing be?

  6. #6
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The normal route is to create your own looks up using combo boxes (based on your own look up tables) on forms.

    See the attached - look at how the order header stores the customer ID not the name, but on the order header form it shows the customers name.
    Attached Files Attached Files

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    You doing this on Forms or is this all in tables? I agree with Minty avoid lookup in the table fields.

    Uncheck that hide key column and see what is shows you. In properties see what the number of columns, bound column and width of columns are. These can be manipulated to get you the values you are wanting. If you have an ID column bound but have 0 as the width, then it will be hidden in the selection dropdown but when you select the 2nd field value, the ID will actually be stored as it is the bound value. So if you don't want to ID to show, you can remove that value and make the type the bound column.

  8. #8
    RustySmith is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    4
    I am working with forms.

    Playing with the Bound Column and Column Widths, as suggested, I was able to correct the problem. Click image for larger version. 

Name:	4.png 
Views:	9 
Size:	31.2 KB 
ID:	31640

    Thank you for the suggestions!! Very much appreciated.
    Attached Thumbnails Attached Thumbnails 4.png  

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Rusty,
    Please describe your "business" to readers in 4-5 lines of plain, simple English. Tel us about a typical day or typical project so we can understand your issue in context.

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

Similar Threads

  1. Replies: 4
    Last Post: 08-22-2015, 12:30 PM
  2. Replies: 15
    Last Post: 11-20-2013, 04:30 PM
  3. Replies: 13
    Last Post: 09-07-2013, 04:57 PM
  4. Replies: 2
    Last Post: 04-05-2012, 08:39 PM
  5. List in Report Shows All Items in Table, But I Only Want...
    By italianfinancier in forum Programming
    Replies: 1
    Last Post: 05-28-2011, 02:42 AM

Tags for this Thread

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