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
  #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

  #2  
Old April 6th, 2010, 10:31 PM posted to microsoft.public.access.gettingstarted
Stephen Raftery[_3_]
external usenet poster
 
Posts: 9
Default Help With First attempt at Database Relationships

To set up a Many-Many relationship, you need to create an intermediate table.
So if 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, what you need is another table:

SupplerMaterials
-SupplierID
-MaterialID
-AnyOtherInfoNeeded


Stephen



"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

  #3  
Old April 7th, 2010, 01:50 PM posted to microsoft.public.access.gettingstarted
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Help With First attempt at Database Relationships

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

 




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 02:55 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.