February 4th, 2010, 08:34 AM
posted to microsoft.public.access.forms
|
|
|
Form/Table structure
Thanks very much for your help.
"Allen Browne" wrote:
You need at least these 5 tables:
a) Company table, with CompanyID primary key.
b) Worker table, with WorkerID primary key.
c) CompanyWorker table, with fields:
- CompanyID Number the company this person works for
- WorkerID Number the person who works for this company.
d) Job table, with JobID primary key and CompanyID foreign key.
e) JobWorker table, with fields like this:
- JobWorkerID AutoNumber primary key
- JobID Number relates to Job.JobID
- WorkerID Number relates to Worker.WorkerID
Your company form will have a subform where you select the workers for that
company.
Your job form will have a subform where you assign the workers to the job.
In the Current event of the job form, set the RowSource of the WorkerID
combo. That way, only the workers for that company show up in the subform.
(There is a potential display issue he if a worker switches company after
you've assigned them to a job, when you go back and visit that job, the
subform will show a blank combo, because the name for that WorkerID is no
longer associated with the company doing the job.)
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Mike" wrote in message
...
I want to create a form which contains details of a job to be performed by
a
contracting company on the site. Each company will have several jobs and
many
workers.
I need to be able to create a new job, select a company it will be
performed
by and then select the workers from that company to do the job.
I'm normally quite good with access but the construction of this one is
baffling me.
Any help on table structures and form code would be much appreciated.
Thanks
.
|