Results 1 to 3 of 3

Look up data from second database

  1. #1
    Stev is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    1

    Look up data from second database

    Hi All,

    I am repetitively in experienced with access and am trying to create a database that is above my current skill set and would really appreciate some help.

    What I am trying to set up is and "Equipment List" Database for work. I am intending on setting this up in two parts. The "Master" database will be populated with various details for different parts, or bits of equipment we use across many projects. This will effectively become a "library/catalogue" of various bits of equipment supplied from many different vendors.

    The second part is a s "Project Equipment list". This database is specific to the individual project and reads information from the "Master".
    When a user wants to select equipment for the specific project in the "Project Equipment list" database, that this database will be able to look at the data on the Master Database that would be saved on a centralised server.

    As the "master" may end up with thousands of entries, when selecting equipment from the "Project" DB, it needs to be able to filter the data based on the look-up tables.

    For example, in the master list, there may be 10 different classes of equipment, each class with multiple items belonging to it. if the user were to select Class 1 from a Drop-down/lookup, then when going to the next field, only equipment that is a Class 1 should show. This same approach would then be used for several layers, such as; Class, Category & Type. with these results filtered, when the user wants to select from equipment from the "description" field, it would be a manageable list to browse through and select.

    Hopefully that explains what I am trying to achieve.

    So now for my question.

    to be honest, i have no idea where to start. I have attempted to use look ups, but don't know how to set it up so that it automatically filters the results based on other look ups values.

    I also have no idea on how to get one database to talk to another.

    so if anyone can point me in the right direction on how to get started, or suggest a better structure that will achieve what I am trying to do, I would be extremely grateful.



    I have attached a mock-up of the two databases to help better explain myself. the master has some dummy data in it and the project one is blank. in the table description, i have listed how i would like each field to work.

    thanks in advance.
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,688
    Several ways for dbs to 'communicate'.

    1. set links to tables with ExternalData Import & Link utility

    2. establish connection in VBA

    3. embed file path in SQL statement - in VBA or in Access query object

    For 2 and 3 review https://stackoverflow.com/questions/...62238#54962238

    The requirement to limit combobox lists based on previous selected data is called 'cascading' comboboxes - common topic.
    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.

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,792
    you can do it just using SQL. e.g.

    SELECT *
    FROM tblMaster IN 'path and filename to masterDB'
    WHERE Class=[cboClass]

    Ah - sorry Option 3 in June's list

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

Similar Threads

  1. Replies: 3
    Last Post: 10-01-2016, 09:41 AM
  2. Replies: 1
    Last Post: 05-16-2016, 04:58 AM
  3. Replies: 2
    Last Post: 06-25-2015, 02:56 PM
  4. Replies: 11
    Last Post: 08-06-2014, 09:10 AM
  5. Getting a database without the data
    By GRB in forum Access
    Replies: 5
    Last Post: 01-09-2011, 06:50 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