Forms - Getting specific info from duplicate information

[pupkiss1965]

What I want to see is when I do a search for example for ABC Company
I would like to see all of the directors, shareholders and officers
associated with that company. I think that the table I am using may be
interferring. I imported an excel sheet for my table. This is the data that
our old database exported to excel.

Company Name Incorporation # Date of Incorporation
ABC Company 123456 12/01/1977

SD&C File # Directors Last Name Directors First Name
01234 Doe Greg Doe Greg

Officer Last Name Officer First Name Shareholders Last Name
Smith David Carter

Shareholders First Name
Aileen

It then repeats and has all the same information on the next line but this
time with David Smith as the next director. So basically I may have 5
different entries for the same company because there are 5 different
directors/shareholders/officers. One entry for every new name in any one of
the 3 columns (director/shareholder/officer). I want to show all associated
persons with the one company for each of the categories...keeping in mind
that each entry has the same Company name, incorporation #, date, and sdc #
and I only want to see that one company whereas with the combo box that I
created when I click on it, it shows me all of the companies as if for me to
select one in a list..I don't want to select I want it to give me all the
information. I hope this makes sense.! and thanks for the help

 

[Tom van Stiphout]

On Fri, 7 Nov 2008 14:47:01 -0800, pupkiss1965

The reason you are having difficulty is because the database design is
incorrect. Once you fix it, the query becomes trivial.
tblCompanies
CompanyID PK
CompanyName
IncorpNo
DateOfIncorp
etc.

(I am assuming an Officer can only have ONE title and can work for
only ONE company)
tblOfficers
OfficerID PK
CompanyID
TitleID
FirstName
LastName

tblTitles
TitleID FK
Title

In the relationships window put all tables, draw the relationships,
and enforce them.
Now you have a relational database, and querying will be much simpler.
If you want a form to maintain the data, you'll have a parent form for
Company, and a subform for the Officers for that company.

-Tom.
Microsoft Access MVP

 

[pupkiss1965]

Unfortunately, John Doe can be a director in 2 different companies or even a
director with one and a shareholder in another. Just as there can be many
shareholders with a company that are also shareholders and officers with
other companies....it all depends on how many companies the person owns or
has interest in. So you can now see my problem as to why I am stuck.

 

[Douglas J. Steele]

So rather than tblOffices having a foreign key pointing to tblCompanies, use
a intersection entity to resolve the many-to-many relationship between
Companies and Offices.

tblCompanies
CompanyID PK
CompanyName
IncorpNo
DateOfIncorp
etc.

tblOfficers
OfficerID PK
TitleID
FirstName
LastName

tblCompaniesOffices
CompanyID
OfficeID

tblTitles
TitleID FK
Title

 

[Tom van Stiphout]

On Sat, 8 Nov 2008 06:55:19 -0500, "Douglas J. Steele"

That's right, and with a PK over both fields:
tblCompaniesOffices
CompanyID PK
OfficeID PK

-Tom.
Microsoft Access MVP