Query or Junction Table - Microsoft Access Forum


	Back to Access Forum Comments List

Query or Junction Table

Jean-Pierre Christen

5 years ago

Hi Richard! Trust you are well!

I was watching the Open Form to a Specific Record video. Is it possible to make a query that takes information from 3 tables, and use that query as a record source for a form?

An example is the website http://www.checkcompany.co.uk/ which is a registry of companies. You can type in a company name and it will list all the directors and shareholders of that company.

Even more interesting, if you type a persons name, it will list all the companies where he is a director and a shareholder.

I have 3 tables. The first is a COMPANYT, and it has one-to-many relationship with 2 others, a DIRECTORT and a SHAREHOLDERT.

Similar to the website, I want to create a form that has a persons information, and also lists all the companies where he is a director and shareholder.

My idea is to create a single form that contains 2 subforms (continuous). The single form will list a persons First and Last Name. Each subform will be a company list.
-Subform A lists all the companies where that person is a director.
-Subform B lists all the companies where that person is a shareholder.

Ive attached a draft form to show what Im trying to do, in case my explanation makes no sense. (also please ignore the blue, Im still playing with aesthetics)

Problem is, I have absolutely no idea how to structure the query. One person can have several DirectorID and several ShareholderID.

Is what Im trying to do possible by using a query ?
Or do I need to make a Junction table to use a many-to-many relationship?

	Jean-Pierre Christen OP @Reply 5 years ago

	Jean-Pierre Christen OP @Reply 5 years ago

Jean-Pierre Christen OP @Reply

5 years ago

Hi!

Please ignore the above. The Union Query video teaches something much better.

I'm still a Rookie and new to VBA and understanding the IF-THEN-ELSE conditions.

My union query is based off 3 tables. I understand the IF-ELSE part when dealing with 2 tables. But for 3rd table, I can't use ELSE a second time for the third condition without an error message.

How can I properly make the expression work?

Have attached screenshot.

	Jean-Pierre Christen OP @Reply 5 years ago

	Adam Schwanz @Reply 5 years ago
You use ElseIf

	Adam Schwanz @Reply 5 years ago
If Something Then 'some code ElseIf Something Then 'somecode ElseIf Something Then 'some more code Else 'this is the code for ALL other options that weren't specified already, Something wasn't equal to the first 3 statements than it automatically goes here. End If

	Adam Schwanz @Reply 5 years ago
I'm not sure what you have going on exactly so just guessing, but with your option it could maybe be If OfficerList.Column(11) = "Director" Then 'code ElseIf OfficerList.Column(11) = "Some other position" Then 'code Else 'all other positions End if Or you could Just do If ElseIf ElseIf and than not do anything if it isn't one of those options.

	Jean-Pierre Christen OP @Reply 5 years ago
YES!!! It works!! Thank you so much Adam! When I get these issues I try to read online or watch youtube help videos, but to me they're speaking Klingon :) You guys explain things so much better!

	Adam Schwanz @Reply 5 years ago
Glad to hear it works ;).

	Richard Rost @Reply 5 years ago
That's why I have a comprehensive course that teaches these things. Much better than watching random YouTube videos. Hint hint. :)

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen