I'll just mention something about one part of the project, which is that it
seems there will be a Materials table, then another table for materials as
they apply to a job. This would be the junction table mentioned in an
earlier posting. It is a JobMaterial table (material used for a specific job)
, with links to the Job table and the Material table. I am using "tbl" as a
prefix for tables for convenience in this posting.

tblMaterial
MaterialID
SupplierID
Description
MatCode
UnitPrice
Notes

tblJobMaterial
JM_ID
JobID
MaterialID
Quantity
UnitPrice

There would be a one-to-many between tblSupplier and tblMaterial, between
tblJob and tblJobMaterial, and between tblMaterial and tblJobMaterial.

In terms of the interface, typically there would be form just for tblMaterial,
where you can add/edit materials records.

Then there is a Jobs form with a JobMaterial subform. The subform control is
linked by way of JobID (Link Child and Link Master in the subform control
properties. On the JobMaterial subform is a combo box bound to MaterialID.
The combo box Row Source comes from tblMaterial.

It's not really possible to get into the full details in this forum. If you
are new to Access here are some resources that will help you better
understand structure and terminology. It seems you have a pretty good grasp
of some fundamentals of table design, but the links may help.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Renaldo wrote:
Hi guys!
I’m quite new to access so please bear with me.
What I want to do is make a database for my father’s engineering company.
What I need is as follows:

A Customer places an order for a project. (So I thought I’d need a Customers
table and a Projects table)
The project can have many different jobs that need doing. (Jobs table)
Each of these jobs can be further broken down into Labour on the job,
Materials used and any additional items or costs. (Labour table, Materials
table and Items table)
Under Labour any number of machines can be used to do the jobs. (Machines
table)
Under Materials, any number of suppliers could be used to supply the
materials (Suppliers table)

My table setup so far looks as follows:

[Customers]
Customer ID
Company name
Contact First Name
Contact Last Name
Telephone
Email

[Projects]
Project ID
Project Name
Order Date
Completion Date
Project Cost
Customer ID

[Jobs]
Job ID
Job Name
Job Description
Job Quantity
Job Cost
Project ID

[Labour]
Labour ID
Labour Description
Labour Quantity
Labour Hours
Labour Cost
Job ID

[Machinery]
Machinery ID
Machine
Machine Rate

[Materials]
Materials ID
Description
Materials Quantity
Materials Unit Price
Materials Total Cost
Job ID
Supplier ID

[Suppliers]
Supplier ID
Company
Contact Person First Name
Contact Person Last Name
Telephone
Email

[Items]
Item ID
Description
Price
Items Total Cost
Job ID

Now my relationships setup is a bit fuzzy. I figured as follows:

Customers – one to many – Projects
Projects – one to many – Jobs

Now here is where I get lost. One job can have only one Materials and only
one Items and only one Labour.

Jobs – one to one - Labour
Jobs – one to one – Materials
Jobs – one to one – Items

Labour can have many machines working. And one machine can work on many
different labours. (All the different jobs.)
One piece material can have many different suppliers (depends on who is
available or cheapest at the time) and one supplier can supply many different
materials.
How do I set up those relationships? A many to many system is needed I
think, but I can’t figure out how to implement it. Are there perhaps any
other errors or things I skipped that you can help with?
Thanks a lot guys!
Renaldo

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201004/1