Matthew - The best way to do this is to create a sub-form to display the
varying list of officers within the main form for the company.
To do this, create a form based on the data you wish to display from the
officer table. Format this form as a datasheet so all you see is the field
names header and a row for each officer.
Now create a form for the main company info and include the info you wish to
display. While in design mode, reduce the size of the window displaying the
company form and click on the officer form from the Access list of forms.
Drag the officer form to the company form and Access automatically creates
the embedded sub-form. If you defined the relationship between the officer
and company tables, Access uses this to link the two forms, otherwise, you
need to specify the key field which links the tables.
You'll probably want to tweak the size/location of the sub-form but this is
pretty easy to do once the main/sub-form is set up.
As for combining all the data in one table, you could do this but it defeats
the purpose of using a relational database and results in a very inefficient
design as you need to include fields in the company table for the maximum
number of officers even though most companies have less.
Hth.
Steve Moskowitz
-----Original Message-----
From: Matthew Ballard [SMTP:[log in to unmask]]
Sent: Sunday, July 04, 1999 4:51 PM
To: [log in to unmask]
Subject: [PCSOFT] MS Access Database Problem
I am working on an Access database that was created by someone
before me,
and I'm having a problem working with some of the data. I have two
tables
that contain information, one being a list of companies and the
other being
a list of officers for those companies. The ID columns for the two
table
match, but there are multiple officer entries for a lot of the
companies,
on seperate lines in the officer table. What I need to be able to
do is
combine the two tables so that the officers, or at least up to five
to
seven show up on the same line as the company information, and I
can't/don't want to do it manually because there are over 5000
companies.
I have shown an expample below:
Companies Table:
5 Name Address Address2 City State Zip
Officers Table:
5 Title1 First Name1 Middle1 Last Name1
Position1
5 Title2 First Name2 Middle2 Last Name2
Position2
What I want in the result is:
5 Name Address Address2 City State Zip
Title1 First Name1 Middle1 Last
Name1 Position 1 Title 2 First Name2 Middle2 Last Name2
Position2
(each line starting with a number is one continuous line.)
Thank You if you can help,
Matthew Ballard
The PCSOFT web site always needs good submissions. If
you would like to contribute to the website, send any
software tech tips or software reviews to:
[log in to unmask]
PCSOFT maintains many useful files for download
visit our download web page at:
http://nospin.com/pc/files.html
|