Need help on relationship tables

[leongtay]

Dear all,

i am in the development phase for a database model to be implemented in Access.

As per attached is the diagram showing the paper based model that i have developed.

Basically, i am looking to develop a database where the customer service personnels can know what HS code to code to a manufactured products that we produce based on item classification.

I have also attached another table showing the type and quantity of raw materials required to produce a certain products and the corresponding table showing the raw material names , the lot size , unit measurement(i.e. Kg or M3) and costs.

I have a problem now, let's say i have a finished goods called DEF which uses finished goods ABC and XYZ to create, how do i go about modifying my diagram to include this? Also, i want to cost my finished products based on raw material prices in the RM table , is it possible to do so?

I have forgotten many things about Access and the last time i did a project on this was many years ago...i cant really remember much about doing the relationship diagram and making queries etc.

Hope to have some guidance from you guys over here.

 

[jzwp22]

Since your finished goods are just a mixture of other finished goods and/or raw materials, all materials should go in 1 table

tblMaterials
-pkMaterialID primary key, autonumber
-txtMaterialCode
-txtMaterialDescription
-fkClassID foreign key to tblClassification
-currCost
-longLotSize
-fkUoMID foreign key to tblUnitsOfMeasure

The cost field would only be filled in for those materials that are raw materials. The cost for finished good items would be calculated when needed.

Now you need a table to associate the materials that make up other materials (finished goods or intermediaries). I'll call this tblManufacture since it would be similar to your manufactured table.

tblManufacture
-pkManufactureID primary key, autonumber
-fkPMaterialID foreign key to tblMaterials (the finished good)
-fkSMaterialID foreign key to tblMaterials (the raw material or a finished good used as an intermediary)
-longQuantity (quanity of fkSMaterialID needed to make fkPMaterialID
-fkUoMID foreign key to tblUnitsOfMeasure

tblUnitsOfMeasure (a record for each unit: EA, kg, m3 etc.)
-pkUoMID primary key, autonumber
-txtUnitOfMeasure

tblClassification
-pkClassID primary key, autonumber
-txtItemCategory
-longHSCode

BTW, it is best not to have spaces or special characters in your table or field names. I also recommend using autonumber primary key fields ( identified with "pk" prefix above) in each table and join tables via that field. The corresponding foreign key field (identified with the "fk" prefix above) needs to be a long integer number datatype.

 

[jdraw]

Since HS (Harmonized System) codes are used internationally, I recommend you get an authorized table with the standard names and spelling. Part of the assignment of HS codes to a product would be a search for key words or phrases at different levels in the hierarchy.

There are examples on the internet
http://www.foreign-trade.com/reference/hscode.cfm?cat=1

 

[leongtay]

Dear JZWP22,

I posted a reply yesterday but unfortunately it seems the forum did not post it.

Anyway, thanks so much for your guidance. There are a few point i'll like to clarify with u.

Assuming that a certain product called "XYZ" is produced, this product can either be packed off in either drums or containers. If it is packed in Drum or containers they will then be considered finished goods which will be sold to customers. The finished product code will be XYZ/Dr for drums and XYZ/CI for containers.

However, product XYZ can also be used as a raw material to create product ABC, which also can be used as an intermediary or be packed off to sell to customer in drums or containers. In which the final code will be ABC/Dr or ABC/CI.

So what i want to know is that, as for the tbl materials do i include all materials , be it finished goods, intermediary , raw materials and packaging materials?

So as for the tbl manufacture assuming that i am producing product ABC and its lot size is 1000kg will the table be in the format as per below?

Manf ID | FkpMatID | FksMatID | L.qty | Fk.UOMID

xxx------ ABC/Dr------ RM01------- 200------ KG
xxx------ ABC/Dr------ RM02------- 300------ M3
xxx------ ABC/Dr------ XYZ--------- 500------ KG
xxx------ ABC/Dr-------- Dr----------- 5------- EA

So, let's say now that if i run the report to get the cost for ABC/Dr will i be able to see the component breakdown for XYZ? Since XYZ is an intermediary good.

 

[jzwp22]

So what i want to know is that, as for the tbl materials do i include all materials , be it finished goods, intermediary , raw materials and packaging materials?

Yes

Your example table looks correct. The manfID will just be sequential numbers (unique record identifiers).

So, let's say now that if i run the report to get the cost for ABC/Dr will i be able to see the component breakdown for XYZ? Since XYZ is an intermediary good.

That's where things can get a little tricky. For one level down you should be able to use a subreport embedded in the main report, but if you want to break it out past that second level, it will get messy. Expanding it out to more levels is typically called an exploded bill of materials. I read a discussion in an Access forum (I do not recall if it was this forum or another) a couple years back about someone using Access for a bill of material system and was able to show the exploded bill of materials down to something like 6 levels using a query with several subqueries. For complicated products, I would definitely look at specifically designed bills of material software packages (typically these are called Manufacturing Resource Planning or Enterprise Resource Planning (MRP or ERP) systems) , but if your finished products are somewhat simple then Access can be used.

 

[leongtay]

Ok thanks..i might not need a exploded bill of materials list afterall.

If worse come to worse, i might be able to just make do with the raw materials needed to make the final products without sub-grouping them into intermediaries products since i just need to know the materials required and the final raw material costs for them.

As i am still in the very infancy stage of it, i am still enquiring for more information on it. It will be messy if i also need to cost overheads into the products based on the time spent required to "cook" the product.

Good news is, i am pretty sure most of the finished goods that we do are only up to 2 levels.

 

[jzwp22]

The labor & other costs associated with producing a material would most likely be in a separate but related table. That might actually be more straight forward than the bill of material aspect.

 

[leongtay]

Hi jzwp22,

I have some problem when i doing a test run on my new relationship that i created.

since tblmanufacture link to tblMaterials, i am wondering if my relationship as shown in the attached screen shot is correct. In which from tblmaterials, material code link to MaterialCode(FG) and MaterialCode(RM) in the manufacturtbl.

Doing so, my report comes up wierd, the material description as shown in the attached report show the name of the finished good and not the name of the Raw material.

 

[jzwp22]

Because you use the material table twice to join to the manufacture table, you typically have to create a query that pulls the list of materials used as raw materials first and then join that to a query that pulls the finished goods. In those queries, you have to rework the joins between the fields. I have attached an example database. I first created the query qrySubMaterials to get the raw materials. I then used that query in qryMaterialListing to get the listing. I then created a report based on that query.

 

[leongtay]

Hi jzwp22,

Thanks so much for your help in this. I will input my raw data into the database for now and see if there's anything else that i might need.

In the meantime, there are 3 tables in the relationship diagram which does not belong to my orginal database. Do i remove those?

 

[jzwp22]

No, do not remove them. Those are system tables. Usually they are hidden. I believe I toggled a setting to show them. I'll post back with directions to hide them.

 

[jzwp22]

I don't know what version of Access you are using, but the following directions to hide the system tables should apply to Access 2007 and newer. Go to File-->Options-->Current Database. Scroll down to the Navigation section and click on the button: Navigation Options... Under the section for Display Options make sure that the check boxes next to Show Hidden Objects and Show System Objects are unchecked. Then hit OK. Go to the relationship diagram and right click on the upper part of the frame of one of the system tables. Select Hide Table from the menu. Do the same for the other two system tables. Then finally, save the relationship diagram

 

[leongtay]

Hi Jzwp22,

Thanks a lot, have hidden the tables already.

I am now inputting some data and testing run to do various reports.

Might have some questions later, thanks a lot for your help on this.

 

[leongtay]

Hi Jzwp22,

I just realised when i added two new finished goods to the table -materials and table-manufacture and run the query -qrymaterialisting, i got duplicate raw material entries for the same finished good.

And i realised that when i add a 3rd finished good to tbl materials and tbl manufacture , i realised i got triple the number of raw material entries for the same finished goods now.

As attached is the database, if possible hope u can help me to look through it.

 

[leongtay]

Hi Jzwp22,

I have managed to remove the duplicates record already by changing the relationship in my query, called QueryMat.I did it by having two materials table joining to a single manufacture table, with one of the Material table linking by the RM ID whilst the other is linked by the finished goods ID.

As attached is a latest version of my database with a customised report showing the finished goods ID and description , its total costs and HS code. The 2nd level will show the RM making up the finished goods and its associated costs.

 

[jzwp22]

I did it by having two materials table joining to a single manufacture table, with one of the Material table linking by the RM ID whilst the other is linked by the finished goods ID.

Glad you got it sorted out. Good luck with your project.