Results 1 to 14 of 14
  1. #1
    Persist is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2010
    Location
    Melbourne Australia
    Posts
    32

    Store a number in a table A, and this field then shows the text linked to this number in table B

    Consider an “orders table” which holds a number for the employee who looks after the order. When you view the “order table” you do not see the number that is stored in the “orders table” you see the employee’s name. The name comes from an “employees table” which associates the name with the number.

    The sample MS Access database “north-wind” uses this feature, see below. I have used the feature in my own test database , but have some questions:
    . What is this feature called?
    . Where can I read more about it?
    . Can this be done by writing a query rather than by using SELECT DISTINCTROW, as shown below.
    . What are the pros and cons of using this feature

    From the north-wind database we have:

    ** Employees table structure

    Employees.EmployeeID
    . autonumber
    . Long integer
    . New values = increment
    . Indexed = yes no duplicates

    Employees. LastName
    . text
    . Field size = 20
    . Caption = Last Name
    . Default value = none
    . Required = yes
    . Allow zero length = no
    . Indexed = yes no dups
    . Unicode compression = yes
    . Ime mode = no control
    . Ime sentence mode = none

    FirstName
    . similar to last name

    ** Orders table structure

    Orders.OrderID
    . autonumber
    . Long integer
    . New values = increment
    . Caption = Order ID
    . Indexed = yes no duplicates

    Orders.EmployeeID
    . number (Viewing the table shows the employee last name, and first name. It does not show this employee number)
    . Field size = Long integer
    . Decimal places = auto
    . Caption = Employee


    . Required = no
    . Indexed = yes no duplicates

    EmployeeID Look up
    . Combo box
    . Row source type = table query

    . Row source
    SELECT DISTINCTROW [employees].[employeeID], [LastName] & “, ” & [FirstName] AS name FROM employees ORDER BY [employees].[LastName}, [employees].[FirstName];

    . Bound column = 1
    . Column count = 2
    . Column heads = no
    . Column widths = 0cm ( it did not work without this - strange)
    . List rows = 8
    . List width = auto
    . Limit to list = yes
    . Allow value lists edits = no
    . Show only row source value = no

    I welcome your comments

  2. #2
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    The is called a Look-Up Field and you do NOT want to use it, see...
    http://www.theaccessweb.com/lookupfields.htm

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    . What is this feature called? - a normalized relational database

    . Where can I read more about it? - a zillion web pages on db normalization and how to build a database, plus everything else related to the subject

    . Can this be done by writing a query rather than by using SELECT DISTINCTROW, as shown below. Select Distinct is structured query language; i.e. it IS part of query syntax. The basic types of query are select, delete, update and append. There are a few other specialized query types. The DISTINCT clause causes a field to be limited to only unique values. If more than one field is involved, then the combinations are unique. There are other clauses and predicates that make up sql. The topic is too broad to cover here, and there a zillions of web pages on any of this stuff. You would be not wasting your time to read up on some of them. If you're going to be designing a db, here are some that I've collected that deal with important subjects (at least I think so)

    . What are the pros and cons of using this feature - if "feature" is what you originally referred to, then to have a poorly normalized db will eventually cause problems; maybe even make the db virtually useless.

    Normalization is paramount. Diagramming maybe not so much for some people.
    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.ca/...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.ca/...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.ca/...cation-in.html

    Important for success:
    One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html

    EDIT: I see that I might have misunderstood the question because I don't have a copy of NW database so I didn't look. The description could also fit the situation of a PK in one field and the associated FK in another. That's what I thought was being asked.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Persist is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2010
    Location
    Melbourne Australia
    Posts
    32
    Thanks for your responses.
    “Lookup fields in tables” is the name of the feature that I am querying – very useful to know this name

    Hearing the “do not use it” was a disappointment. I searched and immediately found encouragement to use this feature.
    https://improvingsoftware.com/2009/1...cess-are-evil/

    Below I describe why I find the look up feature fantastic.
    My question becomes, “How I can achieve the same functionality in another way.”

    The lookup feature is helpful in a small test database I am working on now: a database to locate food items in my kitchen.

    My table for cabinets / shelves / cupboards / fridge is tCabinet
    It has fields including
    CabinetID: auto-number
    CabinetName: Text
    CabinetOrder: Number

    Example cabinet entries from tCabinet:
    CabinetID, CabinetName, CabinetOrder
    15, s1-SouthWallLowDrawers, 51
    16, s2-SouthWallLeftCupboard, 52
    32, s3-SouthWallMiddleCupboard, 53
    14, s4-SouthWallRightCupboard, 54

    My table for food items is tItem
    It has fields including
    ItemID: auto-number
    ItemName: Text
    CabinetID: Number: (Set using the lookup feature)
    ShelfNumber: Number (Shelves within the cabinet are numbered from the top)

    Example item entries from tItem:
    ItemID, ItemName, CabinetID, ShelfNumber
    3, flour, s3-SouthWallMiddleCupboard, 2
    19, salt, s2-SouthWallLeftCupboard, 1

    This structure suits me:
    . When entering the row for salt, it was very easy for me to enter the “cabinet” where I find the “salt”: “s2-SouthWallLeftCupboard”. All I had to do was open the tItem table view, position the cursor and enter “s2”. The computer found and showed the correct full cabinet name and placed the correct ID=16 into the CabinetID field. This ease of picking from the list of cabinets is the big virtue of the lookup fields. I did not have to find the item in a list a click on it.
    . I can use the database by simply viewing the table tItem - as I see the CabinetName for each item. This makes easy sense. Without this feature the table would only show the number of each cabinet and this is not easy to use. I would have to use a query to view this. Easily done, but another step.
    . As CabinetID is an auto-number, people cannot mistakenly change the ID number and break database links.
    . As I add new cabinets I can arbitrarily adjust CabinetOrder to present the cabinets in a suitable order: order from top left to bottom right.
    . Even after I have entered lots of data, I can still easily change the name of a cabinet, just by altering the text in tCabinet, say from “s1-SouthWallLowDrawers” to “LD-LowDrawersSouthWall”. This ability to change names easily has helped me gradually improve my original cabinet names. If one presented a draft database like this to a user, the user could ask for different names and order of presentation. These changes would be simple.

    When constructing queries all I have to do is remember that in table tItem, CabinetID contains a number, despite the text CabinetName being seen in the field. If I sort by [tItem].[CabinetID], I will sort by the Cabinet auto-number.

    To view my food items sorted by CabinetName I just sort by the CabinetName in table tCabinet.

    Can I get this ease of use without using the look up feature?

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    As noted, lookup fields are based on a hidden table and they make things easier for those who don't fully grasp normalization and entity/attribute relationships. They also encourage working directly in tables, which is another thing frowned upon. Those who do understand and work on enterprise db solutions will rightly tell you not to use them for reasons given and others perhaps not mentioned. Your project will never need upsizing so forget that one. What you might encounter is a problem trying to query a lookup field. When you have [Cupboard].[Location] = "shelf), shelf isn't in the Location field - some number like 2 is there but you cannot see that by looking at the visible table, and this query won't work. Yes, there is a work around, but whenever unsuspecting folks have this problem, they're back to the 'net to solve it. The workaround is to use the .Value property of the field, as in [Cupboard].[Location].Value = "shelf although I'm not sure it would appear that way in sql view. That's because I wouldn't use lookup fields. That's because I understand normalization and relationships (I think!).

    For your current project, I'd say go ahead and use them if you still want to while knowing the limitations and issues you might face. The answer to your last question might be, "if Access builds a hidden table to support the relationship, why not just learn to do it yourself with a normal table and avoid the issues?" I can't help but think that if you're still asking "Can I get this ease of use without using the look up feature?" then you didn't study the information at the links I posted on the subject, or you didn't understand how they provide the answer.

  6. #6
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Hmm, I think there is a bit of confusion when it comes to Look-up Fields (even by the Blogger). There are two types of Look-up fields (neither of which a seasoned developer uses).


    • Look-up Fields 2003 and earlier


    • Look-up fields 2007 and later


    Access 2003 and earlier the Look-up field was created by utilizing the Look-up tab while in Table Design. You had control over the Indexes assigned. This is the one the blog is referring to and failed to understand the difference.

    For whatever reason the Access Dev team decided to make a Data Type with a little wizard to make it easier to create look-up fields which was done with the introduction of Access 2007. Only problem is it creates a HIDDEN Index which makes is wrought with issues, hence the list on the link a;ready posted.

    All that said, I'm sure if you scour the web you find even more folks that support the use of them. However, what you won't find is a seasoned developer that says they are okay to use whether it older or newer database. Things like Look-up Fields were created for the novice and will give you more work than they are worth down the road.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Persist View Post
    Can I get this ease of use without using the look up feature?
    Create your tables without using lookup (but create lookup tables);
    Create form(s) to enter/edit your data into tables. On forms, use combo boxes to display lookup value (usually some text) instead of numeric values (lookup table ID's) your combos are linked to. Additional bonuses are various events and properties, which will help to keep your data integrity, make data entry simpler, and doesn't allow to enter faulty data.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Access 2003 and earlier the Look-up field was created by utilizing the Look-up tab while in Table Design
    You're right, I'm not understanding the difference. Perhaps because I don't remember any difference between what you say 2003 had vs now (I'm using 2016). This is what I see if I select a control type for the lookup field.
    Click image for larger version. 

Name:	LookupTab.jpg 
Views:	23 
Size:	23.0 KB 
ID:	35929

    I'm probably not understanding completely. Can you clarify?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    There is now a Look-Up Wizard which assigns a internal unseen Index...
    Click image for larger version. 

Name:	2018-10-22_15-35-36.png 
Views:	20 
Size:	28.6 KB 
ID:	35931

    To use when you are creating a Table go to the bottom and you will see Look-Up Wizard. Does that help?

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sorry, no. Bottom of what?

    It's not visible to me in table design view regardless if a new table or existing. Nor do I see how to invoke a table wizard via the ribbon, although I know how to do that for forms, reports or queries.
    Wouldn't what I showed create the same invisible index? I've believed for some time now that that is the case.

  11. #11
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    When you create a field in Table Design, when you go to select the Data Type it's at the bottom of that list.

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Got it thanks. Proud to say I didn't know that was there! Obviously have not gone to the bottom of the list much, if ever.

  13. #13
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Tis okay. I think it's why people, like the blogger, get confused.

  14. #14
    Persist is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2010
    Location
    Melbourne Australia
    Posts
    32
    Thanks for you input. I hear your warnings.
    And it is pretty easy to avoid using these look up fields in tables – see below

    Table tCabinet:
    CabinetID, CabinetName
    15, s1-SouthWallLowDrawers
    16, s2-SouthWallLeftCupboard

    Table tItem:
    ItemID, ItemName, CabinetID, ShelfNumber
    3, flour, 15, 2
    19, salt, 16, 1

    Use a query showing
    [tItem].[ItemName], [tItem].[CabinetID], [tCabinet].[CabinetName]
    With a combo box for entering CabinetId

    In my earlier post, I was suggesting that look up fields made data entry easier, e.g. for entering the CabinetID for “salt” I only had to enter the start of the CabinetName “s2”. However, using the above query, entering the “16” is just as easy or I can look at the combo box and just select. And I can remove the “s2-“ from the CabinetName
    leaving: “SouthWallLeftCupboard”, not “s2-SouthWallLeftCupboard”

    Using the look up fields, lots can be seen and changed in the very compact table tItem. Not using look up fields needs a few more queries.

    To stop mistaken changing of fields, I set up one query with property “record-set type” = snapshot.
    Then the viewer cannot modify data shown in the query.

    Thanks for your help

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

Similar Threads

  1. Table select field shows up as a number ot text
    By LaughingBull in forum Access
    Replies: 2
    Last Post: 09-04-2015, 12:33 AM
  2. Convert text to number in linked table to excel....
    By ideasfactory in forum Queries
    Replies: 3
    Last Post: 05-24-2015, 05:17 PM
  3. Replies: 2
    Last Post: 01-28-2014, 10:13 PM
  4. changing field on table from Text to Number
    By fainterm in forum Queries
    Replies: 2
    Last Post: 01-20-2014, 03:34 PM
  5. Replies: 6
    Last Post: 01-24-2013, 10:02 PM

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