Prev: what causes, "no results returned"
Next: subforms: using input from one subform to update the other subform
From: Renaldo on 3 Apr 2010 11:34 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
From: Stephen Raftery on 6 Apr 2010 17:31 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 >
From: BruceM via AccessMonster.com on 7 Apr 2010 08:50
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/accessjunkie/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/Forums.aspx/access-gettingstarted/201004/1 |