Results 1 to 3 of 3
  1. #1
    Durks123 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Posts
    10

    Can you make a field look up a value (on another table) dependent on another value (on the same tbl)

    I am trying to create a database for vehicle maintenance for my work (I work in a HGV workshop). What i am wanting to do it have the vehicle VIN number and vehicle registration number link up. So i have one table with this information in it called "Vehicle File" and the i have another table with these fields in them but i again have to type both manually. I am wondering if there is a way to be able to type in the registration which is only 7 characters max and pull the VIN from the "Vehicle File"



    Basically, if this was Excel i would be using the VLOOKUP function. I do not know if something similar is available inside Access

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    and the i have another table with these fields in them
    Why? This sounds like you're off on the wrong foot, especially when you bring Excel knowledge into the picture. That's usually a bad thing when it comes to relational databases. The pk field of the vehicle table should be related to other tables - f'rinstance, the table that holds records for maintenance. Suggest you research database normalization.

    Ignoring all that, an Excel-like lookup in Access is DLookup.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your table structure should be something like this (no doubt with other tables such as vehicle owner, make and model, service profiles etc)

    tblVehicles
    vehiclePK
    Reg
    VIN
    ...
    ...


    tblMaintenance
    MaintenancePK
    VehicleFK
    MaintenanceDate
    ...
    ...

    then in your maintenance form, for the VehicleFK field use a combo control (called in this example cboVehicle) with a rowsource of

    SELECT * FROM tblVehicles
    ORDER BY Reg

    bind column 1 to your control and use a column count of 3 and set the column widths to 0 (to hide the PK)

    when you create a combo, the wizard will fire and give you the opportunity to complete all of the above

    if you want to see the VIN as well, in a textbox put =cboVehicle.column(2)

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

Similar Threads

  1. Replies: 1
    Last Post: 12-04-2016, 05:43 PM
  2. Table association Or dependent on field value
    By NickWren in forum Access
    Replies: 17
    Last Post: 01-04-2016, 03:42 PM
  3. Replies: 2
    Last Post: 05-20-2014, 10:41 AM
  4. Replies: 1
    Last Post: 04-26-2013, 05:30 PM
  5. Replies: 5
    Last Post: 06-19-2012, 10:46 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
  •  
Other Forums: Microsoft Office Forums