Item Master List | Products Table

[namu23]

Hi All,



I hope somebody can help me out and give me positive advises. What i was trying to do is to make an item master list where no duplication of items will or might occur. Or maybe in cases where it found that same item already exist on the master list, it will not proceed. something like that. Sorry for my brief explanation and please excuse my english because i am not fluent.

This is what i want to do.

I was thinking on having 5 to 6 different tables which contains different criteria. The criteria's of the remaining tables will be based on the selection made on the first table. Like if i choose the material "PIPE", the next question would be, "What kind of PIPE?", is it PVC, CARBON STEEL, STAINLESS STEEL, RGS, etc. then it will be followed by sizes, schedule, and so on. BUT if i choose like "CABLE" as the first criteria, then the next question would be "What kind of CABLE"? is it POWER CABLE, HV CABLE, CONTROL CABLE, something like that followed again by different criteria.

Can somebody please give me advice if I this is correct? I will be really appreciative if you could share your opinion about the item master creation.

Thank you and once again sorry for my bad english.

[ssanfu]

Welcome to the forum



When designing the tables, set the description field "Indexed" property to "Yes (No Duplicates)".

For the Products table, the fields might be:

Field Name	Data Type	Property	Value	comment
ProductID_PK	Autonumber			Primary key
ProductDesc	Text	Indexed	Yes (No Duplicates)

Data

ProductID_PK	ProductDesc
1	Pipe
2	Cable
3	Conduit

For the Products Type table:

Field Name	Data Type	Property	Value	comment
ProductTypeID_PK	Autonumber			Primary key
ProductID_FK	Number	Field Size	Long Integer	Foreign key to Products table
ProductTypeDesc	Text	Indexed	Yes (No Duplicates)

Data:

ProductTypeID_PK	ProductID_FK	ProductTypeDesc
1	1	PVC
2	1	CARBON STEEL
3	1	STAINLESS STEEL
4	1	RGS
5	2	POWER CABLE
6	2	HV CABLE
7	2	CONTROL CABLE

Same idea for following criteria tables....

[offie]

Access uses primary keys, so that if you make a column a primary columd it is not allowed to have repeating data. I'm not sure if you have your data already and need to fix it, or if you are going to enter it.

I like to start at the end fist, so think about what you want your form to look like and what you want it to do.

Make sure to use an ID to link all you tables for example:

Table 1
01 - Pipe
02 - Cable

Table 2
01 - carbon
01 - steel
01 - pvc
02 - power cable
02 - hv cable
03 - control cable

Using ID's you can easily link the tables when you are making queries.

You'll probably want to use combo boxes, probably cascading combo boxes, so look up tutorial on that

[namu23]

Welcome to the forum



When designing the tables, set the description field "Indexed" property to "Yes (No Duplicates)".

For the Products table, the fields might be:

Field Name	Data Type	Property	Value	comment
ProductID_PK	Autonumber			Primary key
ProductDesc	Text	Indexed	Yes (No Duplicates)

Data

ProductID_PK	ProductDesc
1	Pipe
2	Cable
3	Conduit

For the Products Type table:

Field Name	Data Type	Property	Value	comment
ProductTypeID_PK	Autonumber			Primary key
ProductID_FK	Number	Field Size	Long Integer	Foreign key to Products table
ProductTypeDesc	Text	Indexed	Yes (No Duplicates)

Data:

ProductTypeID_PK	ProductID_FK	ProductTypeDesc
1	1	PVC
2	1	CARBON STEEL
3	1	STAINLESS STEEL
4	1	RGS
5	2	POWER CABLE
6	2	HV CABLE
7	2	CONTROL CABLE

Same idea for following criteria tables....

Hello Sir,

I appreciate you giving me advice. I understand the scenario up to this point but now again I am stuck on the current situation. When I reached on the criteria selection for the ProductType i.e Carbon Steel, Stainless Steel, RGS. I will face another issue because the next question would be "What is the size of the pipe?". There are two types here. If i was going to choose CS or SS, the unit measure should be in metric i.e millimeter, meter, etc. And if i choose RGS the unit should be in english, i.e Feet, inches, etc. I was thinking of making a table designated only for UnitOfMeasures. Is this the correct approach? But how can i store the sizes i.e 1in, 2in, 150mm, 250mm, 3ft, I dont want to make a separate table only for the numbers like 1,2,3,4...3000. I hope you could give me another insight about this.

Thank you

[namu23]

Access uses primary keys, so that if you make a column a primary columd it is not allowed to have repeating data. I'm not sure if you have your data already and need to fix it, or if you are going to enter it.

I like to start at the end fist, so think about what you want your form to look like and what you want it to do.

Make sure to use an ID to link all you tables for example:

Table 1
01 - Pipe
02 - Cable

Table 2
01 - carbon
01 - steel
01 - pvc
02 - power cable
02 - hv cable
03 - control cable

Using ID's you can easily link the tables when you are making queries.

You'll probably want to use combo boxes, probably cascading combo boxes, so look up tutorial on that

Hi Offie,

Thank you. I will try to look for the combo boxes and what it can do for me.

Anyhow, i have an existing data of 10,000 lines, but i dont really want to keep this and i want to build the item master from scratch. Because most of these are repeating data.

For example, on my record there is CS PIPE 150mm and CS PIPE 6". These two are the same in most cases. But another data exist the like CS PIPE 150 mm, or CARBON STEEL PIPE 150mm. I will consume a lot of time fixing the item master rather than building a new one. I GUESS.

I appreciate your advice Thank you.