Access Newbie - Constructing a DB with two-level relationships

[JasonMB]

Forgive me for posting what is likely an incredibly elementary design question in order to construct a relational DB I need for a project.

ISSUE
I need to be able to query a municipality and identify staff that works in other municipalities. Furthering that relationship, if there are staff members in the municipality at the second level of the relationship, identify any staff also work in any other municipalities.

RESULT
The result I'm looking for is if I select Boise (Municipality A), for example, it will tell me if any of the staff there works in another municipality (Municipality B). Furthermore, if staff at Municipality B works at yet another municipality (other than Municipality A) it will be included in the report.

I used to be able to design these kinds of relationships in a GIS environment quite easily but Access is a foreign language to me.

[Welshgasman]

You would have a record for each person in each location.

Likely many to many relationship?

[JasonMB]

Yes, I already have a sheet created that has records for each municipality and each staff member.

[Gicu]

I think you are looking for a hierarchical query, this is quite a common topic, you will probably need to use self joins to get you what you're after:
https://stackoverflow.com/questions/...in-access-2010
https://stackoverflow.com/questions/...port-in-access

Here is the Google search:
https://www.google.com/search?q=acce...client=gws-wiz
Cheers,

[JasonMB]

Thanks for providing these resources! I'll dive in.

[orange]

I already have a sheet created that has records for each municipality and each staff member.

Are we to assume, you'll be bringing that data into Access? I recommend you describe your set up in a little more detail with examples. Second level..

[JasonMB]

Are we to assume, you'll be bringing that data into Access? I recommend you describe your set up in a little more detail with examples. Second level..

Existing Sheets include:
1. Municipalities - Fields include [County] [Municipality Name] [Municipality Common Name] [Municipal Code]
2. Officials - Fields include [Municipality Common Name] [FirstName] [LastName] [Title] [Municipal Code]

[orange]

Well, sheets implies spreadsheet _ Excel. This is an Access forum, so you could import or link to your "sheet data".

[JasonMB]

Well, sheets implies spreadsheet _ Excel. This is an Access forum, so you could import or link to your "sheet data".

The excel sheets have already been imported into Access as separate tables.

[Welshgasman]

It is very rare excel sheet(s) can be imported into access and be normalized? Normally it is the reverse, in that the data is NOT normalized