Look up data from second database

[Stev]

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.

[June7]

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.

[CJ_London]

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