There is more than one company for a particular part in a given system. The
reason i mentioned that several systems may be combined in one report
(MCReport), was to get the idea of creating a new form on systems. I would
like to keep the forms based on the company because it is much easier to look
at one company and the parts they supply rather than at one system and the
parts the company supplies. i understand where your thinking comes in, a sub
form that would have a field for the company and a field for the parts.
"You said that the main form is based on the Company table. How do you
intend to associate a system with a company?"
The companies are associate do a system through a linking table. The
linking table has a company field, a system field, and a part field.
Therefore each part is linked to a system and a company.
"Each company supplies a variety of parts, so you could have a Company table
linked to a Parts table. For each company you would have a listing of parts.
Company and Parts are related one-to-many, so you have a Company form and a
Parts subform. "
This is currently what i have.
"If what I have described is not how things are, please provide an example
of a system or two, and how companies, parts, and systems are associated. I
don't need to know every part in a system. If you are describing an AC
system it is enough to list the compressor and a hose or something like that.
Then, explain how you want that system to show up on a report. Describe two
systems if it makes your point clearer"
Like i had said i just think that it will be easier to look at a single
company at a time with a subform that shows all the parts that company
supplies. Then i would like to option to filter those parts depending on the
system, through a combo box on the main form. I would like to have it on the
main form, because there are several other subforms that i would also like to
have filtered by the same control, but did not mention them in order to keep
the problem as simple as problem.
For a small description say you have Companies A, B, C. Now there is a
system for fuel which has rubber tubing, plastic tubing, and tanks. Also
there is a system for air intake which has parts rubber tubing, plastic
tubing, and cooling modules.
So my table will look like this (it is a linking table, so all three are IDs
and lookup the values):
Company System Part
------------------------------------------------------------
A Fuel Rubber tubing
A Air intake rubber tubing
A Air Intake cooling modules
B Fuel plastic tubing
C Air intake rubber tubing
hopefully this has clarified how things work in my database.