Table splitting question

[Guest]

I have a table of Projects and a table of Companies. The tblCompanies has
architects, owners, and contractors in it. If a Project will have only one
architect and one owner, but several contractors, does it make sense to pull
the contractors out into their own table to use with a many-to-many junction
table? Or just keep them all together in one table (differentiate by a
BusinessType field) and use the whole for the junction? (Then how do you
restrict a Project to only one architect and only one owner?)

I’m assuming if this table is split, I can continue to use the generic field
name CompanyID, even in separate tables & then use that as a subform link to
list company-specific projects after I compile all companies into one query.
And I can use a generic CompanyID foreign field in tblContacts & not have to
separate them out as well. Yes?

Anyway, I digress….. 1 table or 2? or 3?

thx.
any and all comments appreciated.
sr

 

[Guest]

You need a table called ProjectCompanies. This will be a junction table as:
ProjectID - points to Project table
CompanyID - points to Company table

This way you can have as many companies (of any type) associated with a
Project.
Of course if the different type of companies require different data, you may
want to separate them out into separate tables.

-Dorian

 

[Guest]

ok, that's what I've got now.
When you go to populate this junction table by a form, can you put 2
separate unbound comboboxes on it to pick an arch (source: query of arch
companies) and an owner (source: query of owner companies), and a separate
datasheet view subform for all the contractors (source: yadda yadda...) ?

am having trouble splitting these three out on a form to populate one table.
the datasheet works fine, but the arch & owner cboboxes keep overwriting one
another in the junction table.

any idea how to go about this? Don't know why I'm having such a problem with
this. It should be simple. My brain is fried.

thanks
sr

p.s. I also realized that my CompanyID usage in multiple tables wouldn't
work - more brain-fry. oops.

 

[Guest]

Well normally that would be done from the Project screen, you would have a
button 'associate companies'. This would display a screen with two panes
(listboxes) and between them a right-pointing arrow and a left-pointing
arrow. The left most pane contains the list of currently associated companies
and the rightmost pane the unassociated companies. By clicking the right and
left arrows, you move companies back and forth to associate or disassociate
them. That is how I would do it. As the buttons are clicked you issue INSERT
and DELETE commands to the junction table.

Dorian.

 

[Guest]

Wow. I would not have come up with that. Thanks. I'll give it a whirl.

Any suggestions on how to fish out the contacts for each company once
they're associated? has to be selected from the form as well. Right now it's
all in datashhet view & list everybody in the world, but would ideally like
it to filter on the company selected. I know you can't do that in a datasheet
view, but can you use a third (filtered) list for a contact? I've got a
ContactID field in the junction tbl just waiting for some input.

I don't know if I've given you too much or too little information. And I'll
understand if this is a big hairy deal, but I'd appreciate any crumbs you can
throw my way.

thanks
sr

 

[i_try via AccessMonster.com]

I have a table of Projects and a table of Companies. The tblCompanies has
architects, owners, and contractors in it. If a Project will have only one
architect and one owner,...

In the world in which I live, sooner or later, there will be more than one
owner, more than one architect, and this circumstance will unfortunately
occur just when everything is stretched to the limit. Usually there is a
kludge. "Lets make that a different project, and contract it out to ourselves
...".
I should not have to give a lecture on foundations ...