need help with tables so form okat- multiple equipment for 1 job

[Guest]

Here goes. I know this should be simple not sure why I can't see this right.
Here is what I want to end up with not sure the best way to set up the
tables to get what I want. and/or best way to create the form(from a query)
or using form wizard pulling in tables that I need.

I want to END up with a Form for all possible JOBS(cleaning jobs) with
equipment needed and soaps needed for each job. There are many pieces of
equipment for each job - mop, bucket, etc.(can't figure out how to get this
in a form without listing with a comma)

Have so far - don't thinks the tables are quit right.

tblAllJobs

ID (autonumber-PK)
jobdesc
Equipmentid(not sure where or how to put this)(since >1 equip per job)
Soap(may be more than one soap per JOb)
%used


tblSoap
Soapid(pk)
soapdesc
companyid
msds
specsheet
recommendeduse

tblEquipment
EquipmentId(PK)
EquipmentDesc

I did create a form pulling fields from these tables but since Equipment id
is the one side it shows this as the main form and the all jobs as the
subform. I am picturing that I would like Everything on the the ALLjobs
being the Main form and the list of Equipment and soaps needed as the many.

Not sure what I am doing wrong???

Thanks,
Barb

 

[Carl Rapson]

First suggestion: you need two additional tables, tblJobEquipment and
tblJobSoap (for example). Remove the Equipmentid and Soap from tblAllJobs
and place them in the new tables:

tblJobEquipment:
jobID (FK to tblAllJobs.ID)
EquipmentID (FK to tblEquipment.Equipmentid)
...any other fields related to this particular equipment for this
particular job

tblJoBSoap:
jobID (FK to tblAllJobs.ID)
SoapID (FK to tblSoap.Soapid)
...any other fields related to this particular soap for this particular
job

This way, you can have any number of equipment and soaps for each job. Your
main form will be bound to tblAllJobs, and you can use subforms for the
Equipment and Soap (bound to tblJoBEquipment and tblJoBSoap, respectively).
The subforms can be continuous forms, displaying the items in lists and
allowing you to add/delete items. tblSoap and tblEquipment are OK.

Carl Rapson