A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help With First attempt at Database Relationships



 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old April 3rd, 2010, 04:34 PM posted to microsoft.public.access.gettingstarted
Renaldo[_5_]
external usenet poster
 
Posts: 1
Default Help With First attempt at Database Relationships

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:45 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.