0

I have just created a many to many relationship in MS Access with three tables as shown below

enter image description here

AE Career Fair - Struc contains a list of companies where the company name is distinct.

Office Locations - Int is a junction table containing in one column all the companies occuring in AE Career Fair - Struc, but duplicated based on the countries in which they occur.

Countries contains a list of all the countries in the world.

I was able to run a query for companies occuring in one particular country of interest. I would like to take this a step further and query this relationship and determine which companies are in more than one country. I cant really think of a way to do this based on how I currently have it set up. Could anyone provide indications as to how this would be done? Thanks.

3 Answers 3

0

I didn't really do a lot with MS Access, but you can try something like this, assuming that company.id is primary key and country.id is primary key and the corresponding tables are related to locations through company_id and country_id, respectively:

SELECT company.name, country.name
FROM locations
LEFT JOIN company 
ON company.id = locations.company_id
LEFT JOIN country 
ON country.id = locations.country_id
GROUP BY company.name 
HAVING count(locations.country_id) > 1
1
  • You can't have SELECT company.name, country.name with GROUP BY company.name . That will raise an error. Jul 16, 2018 at 20:30
0

You can do this without referencing any table except Office Locations by manually entering SQL code in the SQL view for a query (you don't even put anything in the design view first, just create a blank query and go to SQL view). You use a self join enabled by aliases:

SELECT a.Company, a.Country
FROM [Office locations] as a
INNER JOIN
    (SELECT Company
    FROM [Office locations]
    WHERE Country = 'India') as b
ON a.Company = b.Company
GROUP BY a.Company
HAVING COUNT(DISTINCT a.Country) > 1

This will return all companies that have an office in India that also have an office in at least one other country. If you just want the companies that have offices in more than one country, regardless of whether one of those countries is India you'd use:

SELECT Company, Country
FROM [Office locations] 
GROUP BY Company
HAVING COUNT(DISTINCT Country) > 1
1
  • You can't have SELECT a.Company, a.Country with GROUP BY a.Company. That's not valid SQL and will raise an error. Jul 16, 2018 at 20:28
0

Can you add a join database? A 2 field database with only the I'd field of each of the 2 many to many databases. So the many databases are joined to it as a one to many. Each entry specifies a country, and a company. Ask for a country, return a list of companies, and vice-versa. That's how it used to work in Lotus Approach. ..

3
  • You say "database" where you probably mean "table". Jul 16, 2018 at 20:25
  • And the question has indeed such a join/junction table: "Office Locations - Int is a junction table containing ..." Jul 16, 2018 at 20:26
  • 1
    In other words, your answer is, "You've got what you need to do what you want" - which doesn't answer, "How do I use what I've got to get what I want?"
    – RDFozz
    Jul 16, 2018 at 21:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.