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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 [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 [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
|
|||
|
|||
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 [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 [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
|
|||
|
|||
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 [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 [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 | |
|
|