Results 1 to 3 of 3
  1. #1
    REBBROWN is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    14

    pulling text values into FK number column

    I apologize in advance for the length here. I always tend to be long winded in my explanations so I will try and keep as simple as possible.
    I have two main tables that I am attempting to split up into several tables.
    The two primary tables are: “VEHICLES” and “EQUIPMENT”
    There are many fields in each table so I’m trying to create tables for some of these fields and set a primary key in those tables pointing back to a Foreign Key in one or both of the above referenced tables.
    As I said, there will be many additional tables but If I can get help figuring this out on one, I can replicate on the others.
    For reasons of explanations I’ll include one of the additional tables which will have Foreign Keys in both VEHICLES and EQUIPMENT tables. This table is “VHIC” for Vehicle ID Code.
    It is a 6 character text field. An example of a record would be “VTR123”.

    I created the VHIC table with two fields, one for text (6-characters) and one for VHIC_ID which is the primary key and autonumber.
    I then created a number field in the VEHICLES table, named it: ‘VHIC_ID’ (Foreign Key in VEHICLES) number-long integer and then in Relationships created a relationship from the VHIC table to VEHICLES.



    So far, so Good or so I thought.
    Lets go back to the example Record I have above, which is : VTR123
    for that specific row in the VHIC table I have a VHIC_ID number of 127 (meaning it is the 127th record in the table) and the record VTR123 in the column next to it.

    Going back over to the VEHICLES table under the Foreign Key VHIC_ID it is displaying 127.
    I want to be able to open the VEHICLES table and see (in the actual table, not a query or form view) VTR123 displayed in the VHIC_ID column.
    I know it can be done as I have seen and torn apart a template for another access DB I downloaded off the templates section of Microsoft.
    That database is Orders management database.
    The first complication is that I am going from a number format as a primary key to a number format in Foreign Key field and trying to display text.
    In the Orders Management DB I see the following for the EmployeesID colum in the Orders Table:


    SELECT [EmployeeID], [LastName] & ", " &
    [FirstName] FROM Employees ORDER BY
    Table/Query
    This SQL is in the RowSource in the LookUP tab on that field.
    Again, this is a number datatype and it is pulling not only one column of text data from another table but both first and last name.

    I now click open the ORDERS table and right there under EmployeeID is first name and last name.
    If I click on a name it opens a drop down where I can select from other employees first and last name, all in a field that has data type set for number.


    I have tried to replicate this SQL in the LOOKUP tab in the VEHICLES table for VHIC_ID but obviously changing the SQL to reflect the correct names.
    When I open the VEHICLES table and go over to VHIC_ID column I still see the autonumbers pulling from the VHIC and this is what happens when I click on one of those numbers:
    I get a drop down and the values I can choose from are the 6-characted text (example: VTR123)

    If I choose one, it throws up an error that I am trying to select Text for a Number data type column.
    Any idea how to fix this?
    I want to see only related records from the VHIC table and not the autonumbers, only the related 6-character records.
    any and all help will be VERY much appreciated.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,898
    Here's something to read about Lookup Fields: http://www.mvps.org/access/lookupfields.htm
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    Not sure how to do this in 2010, but:

    In table design of VEHICLES, you need to select the VHIC_ID field then set the lookup option to "combo box" (if it isn't already) and ensure the bound column is set at "1" in order to use your numerical ID.

    Cheers,

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

Similar Threads

  1. Replies: 2
    Last Post: 11-18-2009, 06:49 PM
  2. Replies: 3
    Last Post: 10-16-2009, 09:27 AM
  3. Replies: 1
    Last Post: 08-21-2009, 06:52 AM
  4. Lookup values in one column from another record
    By cjayjones in forum Queries
    Replies: 16
    Last Post: 08-05-2009, 02:27 PM
  5. inserting values in column based another column
    By wasim_sono in forum Database Design
    Replies: 1
    Last Post: 06-27-2006, 05:23 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
  •  
Tech Forums: Microsoft Office Forums