A difficult one..

[Mike Diamond]

Hi All

I have a database setup for the storage of details for project submissions.
I have a table which stores the details of the submissions themselves
(tblSubmissions) and want to create a link between these details and a team
of both staff members (consisting normally of 3 or more combinations of staff
- but can be more) and a team of Associated Companies.

As an example; We want to go for a project for Microsoft to build new HQs.
We put together a submission offering our services and propose a consortium,
consisting of 2 internal team members, a Project Managment Company, a
Quantity Surveying Firm and a Construction Company.

The SubmissionsID (in tblSubmissions) is linked to the two tables below.
When combined on a form the SubmissionsID is automatically assigned to both
tables and this works fine, but is it the best way to join them? The two
tables cannot be combined because there can be different multiples of teams
(ie. there could be 3 internal members but four external).

tblTeamInt;
IntTeamID
SubmissionID
StaffID (lookup for Staff Names)

tblTeamExt;
ExtTeamID
SubmissionID
ProjectAssociateID (lookup for Company Names/Associates)

Any assistance would be greatly appreciated.

Regards
Mike

 

[Tim Ferguson]

As an example; We want to go for a project for Microsoft to build new
HQs. We put together a submission offering our services and propose a
consortium, consisting of 2 internal team members, a Project Managment
Company, a Quantity Surveying Firm and a Construction Company.

From such a very brief description, I'd suggest you probably need tables
like this:

Commissioners (commissionerID, fullName, address, creditStatus, etc)

Submissions (dateMade, currentStatus, owner, etc)

SubmittedTo (submissionID, commissionerID)

TeamMembers(memberNum, submissionID, roleInTeam, etc)

Members(memberNum, commissionerID)

Companies(memberNum, normalTitle, nameOfContact, address, etc)

People(memberNum, fullName, isAnEmployee, etc)

but it might be a bit more complex than that once you have started
analysing it properly.

Hope it helps


Tim F

 

[Larry Daugherty]

Hi Mike,

What is the issue? There are many ways to get members of two
different sets to show up in a single output document. It may be that
you're trying to associate which candidates are related to which
submission??

One way to solve that would be to create a table
~tblSubmissionsCandidates which is on the many side of a one-to-many
relationship with tblSubmissions.

HTH