Multiple Records

[pupkiss1965]

I have a corporate database that has multiple directors, officers and
shareholders and would like to list all of the directors in one list box in
the form...so if I pull up ABC Company and click on the director name it
lists all of the directors. 2ndly when I search sometimes that person could
be in all three categories...is there any way that I can do a search and have
it pull up everything with that name rather than clicking on next each and
every time. Sorry, I am still new with all of this

[Wayne-I-M]

Hi

If you have 2 combo boxes on your form you can write some code do this,
where you select an item from the 1st and - AfterUpdate - you filter the
items available in the 2nd.
It is called casscadeing combos

If you are not familiar with write code you can also do it like this (using
the wizards)

Open you form in design view
Add a combo box.
In the wizard box that opens click cancel
Do this again so you have 2 new combos

Right click the first combo and open the properties box
In the data column select the Row Souce Tyep row = Table/Query
In the Row Source row click the build option (...)
This will open a query builder
Add the ID
Add the company Name

Do the same for the 2nd combo
But in this combo you must add the table with the companies "and" the
directors
Add the ID from the directors table
Add the directors name from the directors table
Save

"ALSO" the ID from the companies table
In this column click the Criteria row and select build
select forms
Select the 1st combo you have just made
Save

Go back to the form and give it a try.
You may need to later the row widths to allow you hide the ID's in the
combos and show the names (try this by setting the width to 0; and then
changing it to see what you like the look of)

Good luck with your application

--
Wayne
Manchester, England.

[pupkiss1965]

I did the steps that you suggested and I it is not giving me the results that
I need. 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

[Wayne-I-M]

Hi

I think the table are where your problems are as well :-)

Don't worry you can get round this. There are a number of options. You "
could" use the same table as you have and then run some code to select all
the directors from each compnay.
You "could" just base your forms on a totals query so that each company is
shown only once and then run the other forum of your normal tables.
etc
etc
There are lots of way round it

But ...... and this is just my opinion so others in this forum may disagree
and tell you something else. So bare in mind this is just one of the methods.

If it were me (coz I'm a pedant - new english word for today by the way :-)
I would try and get everything how it should be before I started doing other
"stuff". Its not hard and nce it up and running it will work again and again.

So
You have imported a table from excel (I think). In your table you have
these fields

Company Name
Incorporation #
Date of Incorporation

SD&C File

Directors Last Name
Directors First Name

Officer Last Name
Officer First Name
Shareholders Last Name

Hope this right so far.
If it were me I would import the file from excel and (in the same module)
run an update query to various tables.

But in this case can I suggest that you do it the "old fashioned" way. By
clicking the mouse a few times :-)

You are going to create an update query

The first thing to do is to create the tables

Note I have taken the spaces out of the names you use and the & simbol
1st 1
Call it tblCompanies
Put these fields into your new table
CompanyID = autonumber
CompanyName = text
Incorporation = (not sure what this is so I’ll leave it to you)
DateOofIncorporation = date
SDCFile = (again not sure what this is so I’ll leave it to you)

2nd table
Call it tblDirectors
DirectorID = autonumber
DirectorsLastName = Text
Directors First Name = Text

3rd table
Call it tblOfficers
OfficerID = Autonumber
OfficerLastName = Text
Officer First Name = Text

Not really sure where the shareholder should go – what is it ?
ShareholdersLastName

Anyway now you have your tables sorted – I have assume that you are using an
import and all the data is going into a table called tblMyExcelStuff

Ooops
Sorry got to go now (family thing).
If you spend some time createing the tables I will do the rest this evening
– unless someone else doesn’t jump in with the update queries

[Wayne-I-M]

Did you create the tables ?

[pupkiss1965]

I have created all the tables as you suggested. I now have a table for each
of the columns that were brought over from excel spreadsheet. SDC is just a
file number and shareholders I called it tblShareholders.

[Wayne-I-M]

Sorry don't have much time - saturday night and all that :-)

Anyway I asusme that you have created the tables ?

Open the relationship window and link the companies table with the directors
table (using the CompanyID)

The best next step would be to use a query with a subquery to give the the
diectors of each company the correct company ID (which you need to like the
tables - and this will enable your form to work as you want it.
Check out Allans Brown's site for the best way to create query/subs

But for now we will do it with 4 queries - 2 totals query and 2 append
querries

They MUST be run in the right order or they will not work (as you can not
give a likning field to the directors until each company as a primary field)

So create four queries

qryCompaniesTotal
qryDirectorsTotal
qryDirectorsAppends
qryCompaniesAppend

I assume you have used the field names I gave. If not them give it a try
with those names and them change them later (just so you can ee who it works)

Open each of the queries in design view and select Veiw - SQL
Paste this into each qry and then save an close (without running)

qryCompaniesTotal

SELECT tblMyExcelStuff.[Company Name], tblMyExcelStuff.Incorporation,
tblMyExcelStuff.DateOfIncorperation
FROM tblMyExcelStuff
GROUP BY tblMyExcelStuff.[Company Name], tblMyExcelStuff.Incorporation,
tblMyExcelStuff.DateOfIncorperation;

qryDirectorsTotal

SELECT tblMyExcelStuff.[Company Name], tblMyExcelStuff.DirectorsLastName,
tblMyExcelStuff.DirectorsFirstName, tblCompanies.CompanyID
FROM tblCompanies RIGHT JOIN tblMyExcelStuff ON tblCompanies.[Company Name]
= tblMyExcelStuff.[Company Name];

qryDirectorsAppend

INSERT INTO tblDirectors ( DirectorsLastName, DirectorsFirstName, CompanyID )
SELECT qryDirectorsTotal.DirectorsLastName,
qryDirectorsTotal.DirectorsFirstName, qryDirectorsTotal.CompanyID
FROM qryDirectorsTotal;

qryCompaniesAppend

INSERT INTO tblCompanies ( [Company Name], Incorporation,
DateOfIncorperation )
SELECT qryCompaniesTotal.[Company Name], qryCompaniesTotal.Incorporation,
qryCompaniesTotal.DateOfIncorperation
FROM qryCompaniesTotal;

OK - if all that is doen.

Next open the tables Directors and companies and make sure they are empty -
delete all records

Next click
qryCompaniesAppend
Select yes to update

Click
qryDirectorsAppend
SelectYes to update

Now your form should work

I hope :-)

--
Wayne
Manchester, England.

[pupkiss1965]

Hi Wayne, I attempted a couple of times to run the queries etc and it
populates the companies but not the directors. I followed the instructions
you gave me but I must be missing something. Is there anyway that I could
send you what I have thus far so you can review and let me know what I have
been doing wrong. I can send it to you via email with the spreadsheet that I
am using. It would be nice to know what it is that I am doing wrong! LOL as
I created all the tables and labelled them what you suggested and then I ran
the 2 queries and 2 subqueries and then when I went to my form it was
blank...I feel like an idiot right now but it may be from fiddling around
with this for sometime now and I am just not seeing my mistake.

[pupkiss1965]

Okay, I tried again and what I noticed is that it populated the
qryDirectorsTotal and the qryCompaniesTotal but not the tblDirectors or
tblCompanies. So I know that I have definitely gone wrong here. When I
click on the Create tab and then click on QueryDesign it automatically comes
up with the Show Table window. For the qry's I chose the tblMyExcelStuff and
for the appends I chose the Queries tab and selected the qryDirectorTotal and
the qryCompanyTotal. Was I supposed to choose the tblMyExcelStuff or should
I have not chosen any of them and just pressed close and then went to SQL
view? They didn't automatically populate after clicking the Update
button....I had to click on the Run for it to even at least populate in the
qryCompaniesTotal and qryDirectorsTotal. I am sorry for the questions but
you have been so helpful and you are a very brilliant man for Access...so I
cherish your assistance but I understand if you have had enough of me.

[Wayne-I-M]

Hi

the 2 totals queries don't "run" as such. You should be able to just open
them.

Open the companies 1st and make sure you companies are showing - you should
see just one of each.

Go to the companies table and delete all the record just to make it simple.

So you should be able to your companies in the companies total query ?
The should be nothing in the new companies table.

Click run on companies append and it should add the companies from your
import table from ecel to the new table and allocated each an ID.

If this is working do the same with the directors.

Let me know if its working.

If not I will create an email account some where - can you zip the file as
most e mail accounts will allow access db's unless zipped (I think)

--
Wayne
Manchester, England.

[pupkiss1965]

Okay so I do see that the qryCompaniesTotal and qryDirectorsTotal are
populated and the tblCompanies and tblDirectors are empty. When I do the
form it isn't populating the information. If I want A.G.C. company and
select it, the accompanying Director doesn't come up with it like I would
like it to. Each time I do a search by company, director, shareholder or
officer I need it to populate the rest of the information associated with
that id. I did notice that when I ran the query the first time, there was no
id field so I re ran the query and it populated the fields in each but it
still isn't functioning right...I obviously missed a step or have messed up
the relationship or something because the queries did what you said they
would do. At this point, I am not sure what I have done wrong or need to do
to get this thing to work. Thank you for your patience Wayne, I must be a
real piece of work for you!

[Ken Sheridan]

There are alternative models you can use for this. By having separate tables
for each Wayne's in essence treats directors, officers and shareholders as
sub-types of an overall 'people' type. That's a good model if you need to
record different attributes of each sub-type as you can add the relevant
columns to each table for this. You should really have another table
Personnel say which includes everybody and has columns such as FirstName and
LastName for those attributes common to all. The three sub-types tables will
then be related to this and include columns only for the attributes specific
to each sub-type. This way you eliminate the redundancy of storing the same
people's names more than once, which is not only uneconomical but risky as it
allows for inconsistencies (I once found three versions of my name in one
database as author of technical papers in my own area of work; as far as the
database is concerned I'm three separate people!)

Another way of modelling it would be to have one Personnel table for
everybody and then a table Capacities related to this which includes the
capacity in which each person is connected to the company, i.e. Director,
Officer or Shareholder. With this model Director, Officer or Shareholder are
being treated as attribute values rather than as sub-types of a people entity
type. This model works well if you only want to record attributes such as
names which are common to everybody. A complicating factor, however, is that
a person could be associated with more than one company, so there is a
many-to-many relationship between Companies and Personnel, which in each case
could involve the person being involved with the different companies in the
same or different capacities, e.g. they might be shareholders of both, or a
shareholder of one and a director of another. A many-to-many relationship is
modelled by a third table, the Capacities table in this case, so with this
model the tables would be:

Companies
….Company Name (Text - Primary Key)
….Incorporation # (Text or Number as appropriate)
….Date of Incorporation (Date/Time)
….SD&C File # (Text or Number as appropriate)

(I've assumed that company names are unique)

Personnel
….PersonnelID (Autonumber - Primary Key)
….Last Name (Text)
….First Name (Text)

Capacities
….PersonnelID (Long Integer Number)
….Company Name (Text)
….Capacity (Text)

The primary key of this last table is a composite one of all three columns.

Lets assume that your table created from the imported Excel data is called
ExcelTable. You can fill the Companies table with an 'append' query:

INSERT INTO Companies ([Company Name],[Incorporation #], [Date of
Incorporation], [SD&C File #])
SELECT DISTINCT [Company Name],[Incorporation #], [Date of Incorporation],
[SD&C File #]
FROM [ExcelTable];

Before filling the Personnel Table you need to pull all the names from the
separate columns in the ExcelTable together into the same Last Name and First
Name columns with a UNION query:

SELECT [Directors Last Name] AS [Last Name], [Directors First Name] AS
[First Name]
FROM [ExcelTable ]
UNION
SELECT [Officer Last Name], [Officer First Name]
FROM [ExcelTable ]
UNION
SELECT [Shareholders Last Name], [Shareholders First Name]
FROM [ExcelTable ];

Save this as qryAllPersonnel say. A UNION query suppresses duplicate rows
so the result will have only one of each, this does assume that no two people
have the same first and last names of course. You can now append rows to
Personnel with:

INSERT INTO [Personnel] ([Last Name], [First Name])
SELECT [Last Name], [First Name]
FROM [qryAllPersonnel];

The above could be done in one step, but you might find it easier to create
the UNION query first and then a separate 'append' query.

The PersonnelID column will as an autonumber be given arbitrary unique
values automatically. This means that different people with the same names
can be distinguished – never use personal names as keys.

The remaining task now is to fill the Capacities table. This requires the
ExcelTable being joined to the Personnel table in an 'append' query, and the
easiest way is to do it in three stages, once for directors once for officers
and once for shareholders, so for directors:

INSERT INTO [Capacities] ([PersonnelID], [Company Name], [Capacity])
SELECT DISTINCT [PeronnelID], [Company Name], "Director"
FROM [Personnel] INNER JOIN [ExcelTable] ON
[Personnel].[Last Name] = [ExcelTable].[Directors Last Name]
AND [Personnel].[First Name] = [ExcelTable].[Directors First Name];

For shareholders:

INSERT INTO [Capacities] ([PersonnelID], [Company Name], [Capacity])
SELECT DISTINCT [PeronnelID], [Company Name], "Shareholder"
FROM [Personnel] INNER JOIN [ExcelTable] ON
[Personnel].[Last Name] = [ExcelTable].[Shareholders Last Name]
AND [Personnel].[First Name] = [ExcelTable].[Shareholders First Name];

and for officers:

INSERT INTO [Capacities] ([PersonnelID], [Company Name], [Capacity])
SELECT DISTINCT [PeronnelID], [Company Name], "Officer"
FROM [Personnel] INNER JOIN [ExcelTable] ON
[Personnel].[Last Name] = [ExcelTable].[Officer Last Name]
AND [Personnel].[First Name] = [ExcelTable].[Officer First Name];

As far as a form is concerned I'd forget about list boxes. Instead create a
companies form in single form view with separate directors, officers and
shareholders subforms within it, basing each subform on a separate query; for
directors:

SELECT [Company Name], [PersonnelID], [Capacity]
FROM Capacities WHERE Capacity = "Director";

For shareholders:

SELECT [Company Name], [PersonnelID], [Capacity]
FROM Capacities WHERE Capacity = "Shareholder";

And for officers:

SELECT [Company Name], [PersonnelID], [Capacity]
FROM Capacities WHERE Capacity = "Officer";

Link each subform to the parent form on Company name by setting this as the
LinkMasterFields and LinkChildFields properties of the subform control in
each case. Each subform would be in continuous form view and have a combo
box set up as follows:

ControlSource: PersonnelID

RowSource: SELECT [PersonnelID], [First Name] & " " & [Last Name] FROM
[Personnel] ORDER BY [Last Name], [First Name];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Each subform should also have a text box bound to the Capacity field, but
with its Visible property set to false (No) and its DefaultValue property set
to Director, Shareholder or Officer as appropriate in each case. This means
that you can add a person in the relevant capacity as a new record in each
subform if necessary provided that they already exist in the personnel table.
If the person is not yet in the Personnel, table you'll first need to add
them to that of course, for which you can have a simple personnel form based
on the personnel table. There is away you can enter a new person directly
via the combo box on the subform, but lets not complicate things too much at
this stage. We can always come back to that.

When you navigate to a company in the parent form each subform will show the
directors, officers and shareholders of the company.

To search for a company you can use the built in 'Find' facility or you can
add an unbound combo box to the main form with a RowSource of:

SELECT [Company Name] FROM [Companies] ORDER BY [Company Name];

and with the following code in its AfterUpdate event procedure:

Dim rst As Object
Dim ctrl As Control

Set rst = Me.Recordset.Clone
Set ctrl = Me.ActiveControl

With rst
.FindFirst "[Company Name] = """ & ctrl & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

To find a person, whatever their capacity add another unbound combo box to
the main form set up in the same way as those in the subforms:

RowSource: SELECT [PersonnelID], [First Name] & " " & [Last Name] FROM
[Personnel] ORDER BY [Last Name], [First Name];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

In its AfterUpdate event procedure put the following code:

Dim strFilter As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

strFilter = "[Company Name] IN" & _
"(SELECT [Company Name] " & _
"FROM [Capacities] " & _
"WHERE [PersonnelID] = " & ctrl & ")"
Me.Filter = strFilter
Me.FilterOn = True

Note that, unlike the combo box for finding a company, this filters the
form. This is because more than one company might be associated with the
selected person. You can clear the filter using the built in button on the
toolbar, or you can add a 'Show All' button to the main form with the
following in its Click event procedure:

Me.FilterOn = False

Ken Sheridan
Stafford, England

[pupkiss1965]

Ken
When I run the append query I am now getting an error message that says it
can't append all the records in the append query. "Microsoft Office Access
set 0 field(s) to Null due to a type conversion failure, and it didn't add
828 record(s) to the table due to key violations, 0 record(s) due to lock
violations, and 0 record(s) due to validation rule violations." It populates
the CompaniesAppend tab but not the Companies Table as it did when I
attempted Wayne's way but I didn't get the message error I am getting now. I
haven't attempted the other steps that you have so kindly typed out for me
due to the message I received when attempting to do the append query. The
fields that are in the CompaniesAppend are the CompanyName,
IncorporationNumber, DateOfIncorporation, and SDCFileNumber which is showing
me 828 records in it. Suggestions before I attempt your next step?

[pupkiss1965]

Ken and/or Wayne

I have succeeded in making the queries and populated all the table the way
they should be...well all but the Officers one. I reattempted the format
that you suggested Ken and ran the queries and such and the DirectorsAppend
and ShareholdersAppend both worked and are in my Capacities table but when I
try and do the OfficersAppend I get this message "Microsoft Office Access
can't represent the join expression [Personnel].[LastName] =
[TblMyExcelStuff].[OfficersLastName] in Design View....*One or more fields
may have been deleted or renamed, *The name of one or more fields or tables
specified in the join expression may be misspelled, * The join may use an
operator that isn't supported in Design view such as > or <" But I have
checked the fields and didn't see anything misspelled. I copied the code
into my OfficersAppend query and saved it and then went into Design Tab and
clicked Run...thus the error.

[pupkiss1965]

I then proceeded to correct the spelling after figuring out it was just
missing an "s" in Officer...ughhh and was so happy and went to run it and
then it came back with the error I used to get which was MSA set 0 field(s)
to Null due to a type conversion failure, and it didn't add 3489 record(s) to
the table due to key violations, 0 record(s) due to lock violations and 0
record(s) due to validation rule violations....OH SO CLOSE! My Capacities
Table has the PersonnelId, CompanyName, and Capacities(Shareholder and
Director) all populated and is coming together nicely..almost there thanks to
both of you. So if you have any ideas as to why I am now getting this error
and why it won't allow me to run it and append it to the Capacities Table I
would be very appreciative...so ignore my previous message! LOL

[Wayne-I-M]

Hi

The form is not a problem. You can get this to work. In an database the
main thing to get right are the tables. If they are working (with
relationships etc) then the rest will come.

I see another small problem though. Please don't take this in the wrong way
it really isn't meant to be rude. You are createing an application that you
don't understand. You will get it to work v soon but what then. You really
should not only make an application that works but only that you understand.
It "will" need working on later to fix a problem, to add other functions, to
alter the UI, etc, etc and if you (as the creator) do not understand the very
basic way in which it works there will be a problem. Basic by the way in
this case the basics of the database the tables, queries, forms, etc.

--
Wayne
Manchester, England.

[Wayne-I-M]

I think what you need to do here is to look at the excel sheet and see what
formts you have and make sure they are the same as the tables you are
appending to.
Number to number
text to text
etc

You can always run an update query (on the whole imported excel file) to
alter the formats of the cells/fields as soon as you bring them into access
but this will be another task. Access can change formats on most things but
it may be an idea this time to just tie up the excel formats with the table
formats as this will remove the need to alter the data when you bring it in.

--
Wayne
Manchester, England.

[pupkiss1965]

I reviewed the excel spreadsheet and the format is the same as the other
shareholders or directors columns. In the datasheet view of the
OfficersAppend query it shows the PersonnelID, Company Name and the Expr1
being Officer so I can see that it is taking the information that it is
supposed to but when I go over to Design View and click on Run...that is the
message it gives me...and doesn't append the 3489 records that it has in the
datasheet view due to that error code...sorry, I really don't know what that
code means.

[Ken Sheridan]

As you'll have seen the 'append' queries to insert the rows into the
Capacities table are each just a slight variation of the same basic pattern,
so there's no reason in principle why the one for officers should behave any
differently. Make sure that you have the names of the officers first and
last name columns from the TblMyExcelStuff table exactly right in the append
query.

Ken Sheridan
Stafford, England

[pupkiss1965]

Okay, I am going to pull my hair out...I have checked everything I can
regarding the append query for my Officers and I get the same error as I
previously mentioned. I run it and it populates the query but it won't let
me append to the Capacities Table....I have checked the format, the spelling
on both the tblMyExcelStuff and even compared and rewrote the code but still
nothing...why is it that the other 2 went through and are in the Capacities
table but this one is going to drive me nuts? Any suggestions?

[Ken Sheridan]

It could be the indexing in the Capacities table is wrong. The primary key
should be a composite one on all three columns, PersonnelID, CompanyName and
Capacity. In table design view you select all there fields together and then
right click and select Primary Key from the shortcut menu. The key symbol
should thenshow against all three fields. The PersonnelID and CompanyName
fields, as foreign keys, should each be indexed non-uniquely. This will show
in the properties sheet of each field in table design view as 'duplicates
allowed' in the index property.

Its theoretically possible that there could be other indexes set, which you
can check by selecting View | Indexes from the menu bar while in table design
view. If any of the fields are individually indexed uniquely then this would
prevent any rows being inserted into another row if the same value already
exists in the field; if a PersonnelID value has already been entered for
somebody as a director or shareholder for instance, and the PersonnelID is
indexed uniquely (no duplicates) then it would prevent the same person being
added as an officer.

I'm dubious about this being the problem, however, as it would very
coincidental if all officers had already been entered as directors or
shareholders. I'd be more inclined to suspect the data as the first culprit.
Are you sure that the officers are showing correctly as such in the source
tblMyExcelStuff table? If so have they been appended correctly to the
Personnel table? They'll just show as names in personnel of course, so you
really need to lock for people who are officers but not also shareholders or
directors to be sure they've all been appended correctly.

Ken Sheridan
Stafford, England

[pupkiss1965]

Oh no...I didn't set the Primary key and the 2 foreign keys for the
capacities table...actually, I didn't realize I had to do that. So I guess I
should redo all of my queries and such and try again. Do you have time to
let me know where my primary keys and foreign keys should be and list the
relationships to make sure that I have them right? At this point, I am
doubting that I even had them right...boy I feel like a real newbie. Thank
you so much for your patience and feedback with assistance. I have been
following your instructions and they have been working but I don't want to
have to redo again if I can avoid it. I do have a hard time with
relationships and now obviously designating the proper Primary and Foreign
Keys are an issue with me.

[Ken Sheridan]

Not having created the keys or relationships shouldn't have caused the
problem with officers, so doing so might not solve it unless you've
inadvertently created an incorrect primary key or unique index in Capacities.
Nevertheless it should be done. You should not have to run the queries
again though; the data which has been inserted into the tables should not
conflict with the keys or relationships. Incidentally a foreign key is not
set in the table design, it’s a by-product of creating a relationship.
Taking it table by table:

1. Companies

Set the Company name field as the primary key by selecting it in table
design by clicking on the field selector (the little square on the far left
of the field), right clicking and then selecting primary key from the
shortcut menu.

If there are any other fields, e.g. Incorporation #, in this table whose
values will be unique in the table, i.e. the same value can't appear in more
than one row the index the field uniquely by selecting Yes (No Duplicates) as
its Index property in its properties sheet.

2. Personnel

Set the PersonelID field as the primary key in the same way as above.

3. Capacities

The primary key of this table is all the fields in combination, so select
all three by clicking on the field selector of each in turn while holding
down the Ctrl key, or by clicking a dragging down over all three field
selectors. The right click and select primary key. You should then see the
key symbol against all the fields.

The PersonnelID and Company name fields in this table will both be used as
foreign keys, so index each of them non-uniquely by selecting Yes (Duplicates
OK) as the Index property of each. The indexes will improve performance
when the tables are joined in queries or linked in a form/subform.

Relationships:

To create the relationships open the relationships window. With the mouse
pointer inside the window right click and select Show table. Add the three
tables to the window and move them so that they are in a line with Companies
on the left, Personnel on the right and Capacities in the middle.

With the mouse click and drag from Company Name in the Companies table to
Companies Name in the Capacities table. The 'Edit Relationship' dialogue
will open; it should say 'one-to-many' at the bottom. In this check the
'Enforce referential integrity' check box and then click the Create button.
It should create the relationship, but if you get an error message then there
is something in the data which is preventing referential integrity being
enforced. This should not happen, but if it does uncheck the 'Enforce
referential integrity' check box and then create the relationship until
you've sorted out what the problem with the data is (it means that there is a
row in Capacities without a match in Companies).

Then do exactly the same between Personnel and Capacities by clicking and
dragging from PesonnelID in Personnel to PesonnelID in Capacities.

You'll see now how the Capacities table has resolved the many-to-many
relationship which exists between Companies and Personnel into two
one-to-many relationships. This is always how a many-to-many relationship is
represented, never directly between two tables. Tables like Capacities which
do this are sometimes referred to as 'junction' tables or some other similar
term, but that's just a folksy way of saying that they are modelling a
relationship between other tables, usually a many-to-many relationship, but
in some special circumstances it can be a one-to-many relationship.

With the keys and relationships set up as above your 'logical model' or
'schema' is now securely set up so that you can't enter invalid or
inconsistent data. However, I'm doubtful that it will solve the problem of
appending the officers to Capacities. As I said before one explanation of
that would be the presence of incorrect unique indexes, not the lack of
indexing.

Let me know how you get on with creating the keys, indexes and
relationships, and we'll take it from there.

Ken Sheridan
Stafford, England

[pupkiss1965]

Ken...you are a genius! Obviously I was doing something terribly wrong with
the Primary and Foreign keys because your guided assistance was
successful!!!! I now have the 3 tables that we were trying to achieve with
the Capacities Table showing the Officers, Shareholders and Directors
together! So now I will attempt to make my forms as per your instruction
previously. Is there anything I should do or tweak before setting out on the
form tasks? I really really appreciate your assistance and especially your
explanations..it starts to make a little more sense. I don't do code but
with all of this, I was at least able to understand what we were trying to
achieve. I will copy your instructions now for my forms and will let you
know. THANKS AGAIN!!!

[pupkiss1965]

Where do the 3 queries come in (director, shareholder and officer)? When I
made my Company form I selected the Personnel Table and Capacities Table but
I didnt see where I had to put "ControlSource: PersonnelID

RowSource: SELECT [PersonnelID], [FirstName] & " " & [LastName] FROM
[Personnel] ORDER BY [LastName], [FirstName];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

From the instructions you gave, I think I missed an important step here.

[Ken Sheridan]

Take another look at my original reply. You'll see that what you need is a
Company form and then 3 subforms within it so the parent Company form doesn't
include the Personnel or Capacities table; its based solely on the Company
table. Rather than simply making the Companies table the form's
RecordSource, however, its better to first create a query which sorts the
companies in name order and then use the query as the RecordSource. The
query is a very simple one:

SELECT *
FROM [Companies]
ORDER BY [CompanyName];

Save the query, as qryCompanies say, and then base a form on it. You can
use the form wizard to create the form and then amend it later when you add
the subforms. The form must be in single form view, though.

You now need to create three separate subforms, but as they'll be identical
apart from their RecordSource properties you can just create one and then
copy and paste it under new names twice to create the others. You just have
to change the RecordSource property of the two copies.

First create the three queries on which the subforms will be based:

For directors:

SELECT [CompanyName], [PersonnelID], [Capacity]

FROM Capacities WHERE Capacity = "Director";

For shareholders:

SELECT [CompanyName], [PersonnelID], [Capacity]

FROM Capacities WHERE Capacity = "Shareholder";

And for officers:

SELECT [CompanyName], [PersonnelID], [Capacity]

FROM Capacities WHERE Capacity = "Officer";

Now create the first form which you'll use as the directors subform. This
will be a form in continuous form view based on the first of the above
queries.

Each subform would be in continuous form view (you can use the wizard to
create it) and have just one visible control, a combo box set up as follows:

ControlSource: PersonnelID

RowSource: SELECT [PersonnelID], [First Name] & " " & [Last Name] FROM

[Personnel] ORDER BY [Last Name], [First Name];

BoundColum: 1

ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will

automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Each subform should also have a text box bound to the Capacity field, but

with its Visible property set to False (No) and its DefaultValue property set

to Director, Shareholder or Officer as appropriate in each case.

So if you use the form wizard to create the first subform include the
PersonnelID and Capacity fields, but then hide the latter by setting its
Visible property to False (No) in form design view.

In answer to your question you'll find ControlSource as a property of the
combo box on the data tab of its properties sheet.

Once you've created the first subform copy and paste it twice, giving it a
different name each time, so you might end up with three identical forms
called sfrDirectors, sfrShareholders and sfrOfficers for instance (what names
you use is entirely your choice, but it helps if the names are meaningful).
Open each of the two copies in design view and change the form's RecordSource
property to the appropriate one of the three queries.

You can now embed the three forms in the original Company form as subforms.
Open the Company form in design view and size the window so there's some
spare space and then drag the edge of the form design area to create some
empty space on it to place the subforms. You can add each subform either by
adding a subform control from the toolbox and setting the SourceObject
property to one of the three subforms you created, or by dragging and
dropping the source forms from the database window onto the design area of
the company form.

Move the subforms around to position them on the main parent form how you
want and size them as appropriate. You'll probably find that putting the
three side by side at the bottom makes sense.

To link with the parent form the LinkMasterFields and LinkChildFields
properties of each of the three subform controls should be CompanyName,
though you'll very likely find that Access has already done this for you on
the basis of the relationships you created.

You should now find that as you moved from company to company through the
main form the subforms will change to show the directors, shareholders and
officers for the current company.

Finally I explained in my first reply how to include unbound combo boxes on
the main company form to search for a company or person, but I'll repeat what
I said (make sure the field an table names in the code match what you've used)

To search for a company you can use the built in 'Find' facility or you can
add an unbound combo box to the main form with a RowSource of:

SELECT [CompanyName] FROM [Companies] ORDER BY [CompanyName];

and with the following code in its AfterUpdate event procedure:

Dim rst As Object
Dim ctrl As Control

Set rst = Me.Recordset.Clone
Set ctrl = Me.ActiveControl

With rst
.FindFirst "[CompanyName] = """ & ctrl & """"

If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

To find a person, whatever their capacity add another unbound combo box to

the main form set up in the same way as those in the subforms:

RowSource: SELECT [PersonnelID], [FirstName] & " " & [LastName] FROM
[Personnel] ORDER BY [LastName], [FirstName];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

In its AfterUpdate event procedure put the following code:

Dim strFilter As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

strFilter = "[CompanyName] IN" & _

"(SELECT [CompanyName] " & _

"FROM [Capacities] " & _
"WHERE [PersonnelID] = " & ctrl & ")"
Me.Filter = strFilter
Me.FilterOn = True

Note that, unlike the combo box for finding a company, this filters the
form. This is because more than one company might be associated with the
selected person. You can clear the filter using the built in button on the
toolbar, or you can add a 'Show All' button to the main form with the
following in its Click event procedure:

Me.FilterOn = False

One thing you might like to do before you do any of this is check that the
tables, now that you've got all the data in, do give the correct results when
joined. You can do this with a query which will list all companies, with
their directors, officers, shareholders. You don't have to save the query if
you don't wish to, just create it in SQL view and then switch to datasheet
view to see the results:

SELECT [Companies].[CompanyName], [Capacity], [FirstName], [LastName]
FROM [Companies], [Capacities], [Personnel]
WHERE [Companies].[CompanyName] = [Capacities].[CompanyName]
AND [Personnel].[PersonnelID] = [Capacities].[PersonnelID]
ORDER BY [Companies].[CompanyName], [Capacity], [LastName], [Firstname];

Ken Sheridan
Stafford, England

[pupkiss1965]

Hi Ken

I have created the 3 queries for my subforms but am still a little confused.
I made the 3 forms using the wizard and did as you said and changed the
RecordSource accordingly. My main form is in Single view. I have made the
PersonnelID not visible but I am stuck on something...your comment "Each

subform would be in continuous form view (you can use the wizard to
create it) and have just one visible control, a combo box set up as follows:

ControlSource: PersonnelID " you are talking about the subform and then you
talk about a combo box. Am I making a combo box in the subform? I made a
combo box on the main form to see if that is what you were referring to but
there was no option for the ControlSource being PersonnelID only CompanyName,
Incorporation, DateOfIncorporation and SDCFileNumber ( Companies Table). So
I am not sure where I am adding the code for the RowSource or selecting the
ControlSource as PersonnelID ? I hope this makes sense to you

[pupkiss1965]

Oh man am I an idiot....I finally figured out what you have been saying...I
was reading it all wrong...I now realize that if I click on the Capacity
field I can "Change To" a combo box! Sorry about that...it helps when you
don't have an idiot reading instructions! LOL

[pupkiss1965]

Ken

Well I thought I had it but obviously I don't. In my subform I made the
Capacity field into a combo box and the label as the text box but it makes it
unbound. I tried doing it with the add fields button but it still comes up
as an unbound text box. When I go to the properties page and select control
source to make it a bound text box the only options are the Capacity,
PersonnelID and CompanyName. There isn't an option for me to select a
DefaultValue property to Director

[pupkiss1965]

Ken

I was wondering if I could send you what I have for review. I am not
getting the results that I should be with the subforms. It is showing 3
records for Director and 3462 for Officers and 3600 for shareholders...I am
pretty sure that one company doesn't have that many shareholders. LOL. You
would really help me out if you could review this and advise me of things
that I have done wrong.

[pupkiss1965]

Ken

that I have done wrong. Once I can figure out what I am doing wrong then I
will be adding fields such as the addresses for the members and adding at
least 3 searches (search by member name, company or sdc file number).

[Ken Sheridan]

Did you join the tables in a query as I suggested and open that to see if the
right officers, directors and shareholders were being returned per company?
If so and the query's results look OK then the most likely source of the
problem is that the subform and form isn't linked correctly. The
LinkMasterFields and LinkChildFields
properties of each of the three subform controls should be CompanyName.
These are properties of the subform controls. A subform control is the
control in the main form which houses a subform.

I don't normally look at files on an individual basis, but as we've come so
far with this and I suspect the problem will probably be not too difficult to
identify I'll be happy to take a quick look at it. Mail it to me at the
following (munged) address. If you are using a later version than Access
2002 be sure to convert the copy you send to 2002, as I'm still in the dark
ages. My email address is:

kenwsheridan<at>yahoo<dot>co<dot>uk

Ken Sheridan
Stafford, England