Combining data from 2 tables into 1 form

[britney]

<sup>I am building my first Access database at work after taking several college courses on the subject. I have imported Excel files into tables with the necessary information.



In the first table, there is a list of departments (with unique department name to distinguish them), within the columns I have details like address, locations, etc. In a second table I have a list of providers (each provider's name is unique). The department which each provider works is listed in one column, and each department has one or more providers.

What I would like to do is create a form in which I could input the department name, and the information about that department would populate from Table 1, and the list of all providers who work there (1 or multiple) and their info would populate from Table 2.

In my efforts so far, what I have run into is Access is creating duplicating department information, and each provider appears on a separate form, rather than having more than one provider listed on one form. I am not really sure what to even search for to find steps to perform this, so even if you can just let me know what this would be called, it would be a huge help. Thank you in advance!</sup>

[ranman256]

you want 3 tables:
tDepts, tProviders, tDeptProviders

tDept table:
DeptID, DeptName, Manager,etc
Acct, Accounting, Joe Smith

tProvider table:
ProvID, ProvName, ProvAddr
34, AT&T,....
22, Spectrum Cable, ….

tDeptProviders is a child table of tDepts:
DeptID, ProvID
Acct, 34
Acct, 22


in the single record form for Dept, add a subform for tDeptProviders.
then add providers to 1 dept using a combo or similar.

[pbaldy]

Typically that would be done with a form based on department and a subform based on provider. Master/child links keep them in sync with each other.

[britney]

Wow, I think between these two comments, I was able to create what I needed! Thank you both for your input

[pbaldy]

No problem and welcome to the site by the way!