Elegant solution to query problem

[Guest]

I am trying to generate output from a query for a large list of company names
with associated individuals linked to these companies. Ideally, I would like
to specify a particular company, say Company X, and get a list of all those
individuals linked to that company, for examples, individuals A, B, C and D.
Note that the individuals linked to a company are all found in the same
field. As a next step, I would like to get a list of all the companies to
which individuals A, B, C and D are linked, in addition to company X. For
example, individual A could be linked to company Y and Z in addition to X.
Likewise, individual B could be linked to company M, N and O in addition to
company X.

I believe the first step is creating a cross-tab query. I have managed to
get a list of all the individuals in a column format with all their
associated companies in a row format. Is this the correct first step? If so,
what is the next type of query I should run? If not, is there a more elegant
solution?

Any help would be much appreciated. Thanks.

 

[kingston via AccessMonster.com]

Let's assume your data structure is like this:

tblCompany {CompanyID - PK,...}
tblIndividuals {IndividualID - PK,...}
tblLinks {CompanyID - FK, IndividualID - FK, ...}

To get the individuals associated with a specific company, all you really
need is tblLinks if you can live with just the fields [CompanyID] and
[IndividualID]. You can add the other two tables for more information if you
want; use two simple inner joins tblCompany JOIN tblLinks ON CompanyID and
tblIndividuals JOIN tblLinks ON IndividualID.

Now, reverse the logic. How do you get all the companies associated with an
individual? The same way. Again, all you really need is tblLinks. So, what
you want to do is combine these two queries where a CompanyID is given as an
input:

tblLinks JOIN tblLinks ON IndividualID

When you specify CompanyID in the first tblLinks, all of the associated
individuals are output. With those IndividualIDs, look for corresponding
CompanyIDs in the second tblLinks. Join the other tables as needed to these
two main tables to provide other information. HTH

 

[Guest]

Thanks HTH. Your logic is very sound.

Let's assume your data structure is like this:

tblCompany {CompanyID - PK,...}
tblIndividuals {IndividualID - PK,...}
tblLinks {CompanyID - FK, IndividualID - FK, ...}

To get the individuals associated with a specific company, all you really
need is tblLinks if you can live with just the fields [CompanyID] and
[IndividualID]. You can add the other two tables for more information if you
want; use two simple inner joins tblCompany JOIN tblLinks ON CompanyID and
tblIndividuals JOIN tblLinks ON IndividualID.

Now, reverse the logic. How do you get all the companies associated with an
individual? The same way. Again, all you really need is tblLinks. So, what
you want to do is combine these two queries where a CompanyID is given as an
input:

tblLinks JOIN tblLinks ON IndividualID

When you specify CompanyID in the first tblLinks, all of the associated
individuals are output. With those IndividualIDs, look for corresponding
CompanyIDs in the second tblLinks. Join the other tables as needed to these
two main tables to provide other information. HTH

I am trying to generate output from a query for a large list of company names
with associated individuals linked to these companies. Ideally, I would like
to specify a particular company, say Company X, and get a list of all those
individuals linked to that company, for examples, individuals A, B, C and D.
Note that the individuals linked to a company are all found in the same
field. As a next step, I would like to get a list of all the companies to
which individuals A, B, C and D are linked, in addition to company X. For
example, individual A could be linked to company Y and Z in addition to X.
Likewise, individual B could be linked to company M, N and O in addition to
company X.

I believe the first step is creating a cross-tab query. I have managed to
get a list of all the individuals in a column format with all their
associated companies in a row format. Is this the correct first step? If so,
what is the next type of query I should run? If not, is there a more elegant
solution?

Any help would be much appreciated. Thanks.