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

    How to get table with status as integer showing status description from table tStatus

    The standard ms-access example, North-wind database, has a great feature that I cannot duplicate in my databases. The North-wind database has

    A table “Orders” with fields including
    . OrdersId
    . ShipVia (Long Integer: could be called ShippersId)

    A Table “Shippers”
    . ShippersId CompanyName Phone
    . 1 BestShipping 9123-5677
    2 QuickShip 6789-1234

    When I open north-wind database and view the table “Orders”, the ShipVia field, an integer field, does not show the number “2”, it shows the company name “BestShipping”

    When I carefully try to define a similar structure in my database, I get what I would expect. When I open my equivalent of the “Orders” table, I see only a number in my equivalent field of ShipVia.



    Running Access 2007 with Windows 2007 64 bit

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It's likely a lookup field, which many of us won't use:

    http://access.mvps.org/access/lookupfields.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Sounds like the Northwind ShipVia field has a lookup setting. I prefer not to set lookups in tables because I want to see the real data when I view tables. Actually, I avoid lookups as much as possible. For short descriptive values (such as: Good, Bad, Excellent) I would just save the description and get rid of numeric key. However, if a lookup is the best approach, set them with combobox properties on form.

    Review http://access.mvps.org/access/lookupfields.htm
    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.

  4. #4
    Persist is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2010
    Location
    Melbourne Australia
    Posts
    32
    Northwind datase has a "select distinctrow" instruction in the lookup section for defining ShipVia
    I tried using this - but could not get it to work.

    However, as you suggest avoiding such features, I will stick with using:
    A table t-Status
    with entiries Status: "Filled", "Cancelled" etc
    and just pick from these with a combo box.

    I thought I was missing out a smart aspect of ms-access.
    Thanks for your rapid replies

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Lookup in table would be practical if you do data entry/edit directly with tables. It might be needed for web database (not sure, never built one). However, when forms are the user interface with data, the table lookup settings only serve as a template when creating forms. When a field is 'grabbed and dragged' from field list onto form or report, the control will be created according to the properties set in table.
    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.

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

Similar Threads

  1. Replies: 1
    Last Post: 04-26-2013, 09:53 AM
  2. Replies: 2
    Last Post: 07-08-2012, 05:28 PM
  3. Replies: 16
    Last Post: 03-13-2012, 03:47 PM
  4. Status (yes/no)
    By combine21 in forum Access
    Replies: 2
    Last Post: 07-26-2011, 02:37 PM
  5. Design table - keeping worker status
    By snoopy2003 in forum Database Design
    Replies: 8
    Last Post: 02-23-2011, 12:48 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