filtering subforms

[Colin Foster]

Hello Group
Posted this to the "Getting Started" newsgroup... then I found this one that
may be more appropriate, so here goes...

I've built a database that records projects, the companies that are
tendering for that project and the contacts within each company. These are
displayed in a series of forms & sub forms as follows...
FrmProjects
subform frmTenderers
subform frmContacts

A company may have 3 contacts that I deal with, however it may be that Tom
deals with Project #1, Dick with Project #2 and Harry with Project #3.
What I would like to be able to do is, initially show all contacts for the
job and then select (via a check box) the contact (or contacts) that are
relevant to this project (maybe even filtering out the others). However,
when I then start a new project and link this company to it, I need to see
all contacts so that I can select the ones that are relevant to Project #2,
without then affecting those that are relevant to Project #1.

Any suggestions would be gratefully received.

Regards

Colin Foster

 

[Allen Browne]

Hi Colin. This question is mostly about how to build a suitable data
structure to record this kind of information.

Based on what you have told us, you will need at least these tables:

Company table: one record for each company.
CompanyID AutoNumber primary key
CompanyName
Address
...

Person table: one record for each person.
PersonID AutoNumber primary key
Surname
FirstName
...

CompanyPerson table: one record for each combination of company+person.
CompanyID Foreign key to Company.CompanyID
PersonID Foreign key to Person.PersonID

Project: one record for each project.
ProjectID AutoNumber primary key
ProjectName
DueDate date when tenders close
...

Tender: one record for each tender received.
TenderID Autonumber primary key.
ProjectID foreign key to Project.ProjectID
CompanyID foreign key to Company.CompanyID
PersonID foreign key to Person.PersonID
TenderDate date this tender was received.
...

With that structure, you can record a tender for a project from a company
with an associated contact person. Another record can have another tender
from the same company for a different project, with a different contact
person.

 

[Colin Foster]

Hi Allen,
This is one of those times when I wished that the person asking for the
database had thought of this need at the start!!
I've already got things set up much as you suggest, with one main exception,
namely I've got the Person table & company person table as a single table
(one person can only belong to one company, but a company can have more than
one person).
I'm looking at the possibility of using combo boxes to select as using your
suggestion, whilst more elegant (and "proper") will need some form
re-designing & there isn't too much left in the budget. However, it might be
something that I work on in the background.

Thanks for the advice & sorry for the delay in responding - I've been away
from the office.

Regards
Colin Foster