Test String test besed on table data (1 Viewer)

[Roku]

Table2 design is inappropriate for what you are trying to achieve. By repeating field groups for weld process and dimensions, it becomes much harder to manipulate, which is why you are having difficulty.

A better approach is to have a reference table which lists each material along with its weld process and dimensions. Any one material will have multiple records; one for each welding process which can be used with that material.

What is the relationship between 'Joint type', 'Material' and 'Weld Process'? I assume Joint types are dependent on Materials - but is there also a dependency on shape and dimensions?

What does 'CS', 'SS' etc. stand for? I thought CS might be 'Cold Steel'? It might be useful to include a table which translates the abbreviations to their full text (e.g. CS: Cold Steel; SS: Stainless Steel).

I assume you start with a material with specific shape and dimensions? From that, do you select a joint type by material or by shape and size? How is the weld process determined – by material, by size, by shape?

Is it correct to assume there is a fixed list of characteristics? By this I mean a list of materials which can be used, dimensions for those materials and weld processes for them? More importantly, will these lists change for any reason in future (e.g. new materials added, dimensions adjusted etc.)? I ask because you can pre-populate combo boxes with fixed values or you can retrieve the lists dynamically. The choice is governed by things like volatility of items and performance of the database - not to mention maintainability

If you can explain the relationships for me, I'll suggest a way to manage the selection.

In the meantime, do some research on Data Normalisation – that will help you to plan your data structures. Have a look at this article http://www.access-programmers.co.uk/forums/showthread.php?t=172363&page=6 (it's a long chain, so just read the start to get the main relevance).

 

[igourine]

Table2 design is inappropriate for what you are trying to achieve. By repeating field groups for weld process and dimensions, it becomes much harder to manipulate, which is why you are having difficulty.

A better approach is to have a reference table which lists each material along with its weld process and dimensions. Any one material will have multiple records; one for each welding process which can be used with that material.

What is the relationship between 'Joint type', 'Material' and 'Weld Process'? I assume Joint types are dependent on Materials - but is there also a dependency on shape and dimensions?

What does 'CS', 'SS' etc. stand for? I thought CS might be 'Cold Steel'? It might be useful to include a table which translates the abbreviations to their full text (e.g. CS: Cold Steel; SS: Stainless Steel).

I assume you start with a material with specific shape and dimensions? From that, do you select a joint type by material or by shape and size? How is the weld process determined – by material, by size, by shape?

Is it correct to assume there is a fixed list of characteristics? By this I mean a list of materials which can be used, dimensions for those materials and weld processes for them? More importantly, will these lists change for any reason in future (e.g. new materials added, dimensions adjusted etc.)? I ask because you can pre-populate combo boxes with fixed values or you can retrieve the lists dynamically. The choice is governed by things like volatility of items and performance of the database - not to mention maintainability

If you can explain the relationships for me, I'll suggest a way to manage the selection.

In the meantime, do some research on Data Normalisation – that will help you to plan your data structures. Have a look at this article http://www.access-programmers.co.uk/forums/showthread.php?t=172363&page=6 (it's a long chain, so just read the start to get the main relevance).

Dear Roku,
thanks for valubale advice, however i was thinking to create multiple table but i could not do due to the requirement of this table because 1 welder can be certified in different type of materia & Welding process in the same time (CS-GTAW, CS-SMAW, SS-GTAW, SS-SMAW+GTAW ..etc)
i attached daigram for better understanding you may have idea how to create separate table.
also i try do devlope the previous code & it works with new table but need some modification.

 

[Roku]

The diagram helps - thanks.

It appears that there are six basic combinations (2 material types, each with three welding processes). What the diagram doesn't tell me is the dimension ranges for those six. I assume the welding process is governed by the choice of material with diameter and thickness? I also assume that each of the six variants will have independent shapes and dimensions?

If my assumptions are correct, then your reference table will have exactly six rows, per the example attached.

What I can't see from your sample is what your tables are trying to record. You mention certification for welders, so I assume you have a table of welder names. I also assume that any one welder can be certified in one or more welding processes for one or more materials?

If you could explain the business context for me, I can suggest possible approaches. I don't want to guess at the requirements and advise you wrongly based on my own assumptions.

 

[igourine]

The diagram helps - thanks.

It appears that there are six basic combinations (2 material types, each with three welding processes). What the diagram doesn't tell me is the dimension ranges for those six. I assume the welding process is governed by the choice of material with diameter and thickness? I also assume that each of the six variants will have independent shapes and dimensions?

If my assumptions are correct, then your reference table will have exactly six rows, per the example attached.

What I can't see from your sample is what your tables are trying to record. You mention certification for welders, so I assume you have a table of welder names. I also assume that any one welder can be certified in one or more welding processes for one or more materials?

If you could explain the business context for me, I can suggest possible approaches. I don't want to guess at the requirements and advise you wrongly based on my own assumptions.

you attachement abslotly correct, but the welder shall have like matrix with all type of material & welding process & diameter and thickness, i attached the screen snap for the excel format it is like matrix
for concepts:
type of material:

CS : carbon steel
SS: Stailess steel
CuNi: copper nickle

welding process:
Name of welding operation
GTAW
SMAW
GTAW+SMAW .

Please see attached so you can understand every thing.

Please take a look also to the mdb database i attached previously i also perform new code

 

[igourine]

you attachement abslotly correct, but the welder shall have like matrix with all type of material & welding process & diameter and thickness, i attached the screen snap for the excel format it is like matrix
for concepts:
type of material:

CS : carbon steel
SS: Stailess steel
CuNi: copper nickle

welding process:
Name of welding operation
GTAW
SMAW
GTAW+SMAW .

Please see attached so you can understand every thing.


Dear Roku,
i attached also better example. hope it is clear now
sorry for wasting your time & thanks for all your clarification.

sorry for wasting your time & thanks for all your clarification.

sorry for wasting your time & thanks for all your clarification.

 

[Roku]

OK, that helps.

I envisage three tables here:
1. Table of welders.
2. Table of weld processes etc (per previous message)
3. Table of certifications by welder.

The third table is essentially the link between two sets of related (but independent) data. It points to welders and processes, using foreign keys to the other two tables. Each time a welder is certified for a material/process, an entry is made in the certifications table.

Think of the form as the 'presentation layer'. Its job is to pull all of the table data together in a logical fashion, using various Access constructs. The 'data layer' can be constructed independently, using the principle of one entity type ('thing') per table.

The form would use a query to retrieve the welder details. You might use a subform to list each certification for the current welder; the subform would use its own query to retrieve the relevant certification details.

Again, I am making assumptions here which might not be valid

If you would like a practical example, I'll construct an illustration DB for you. It will take me a day or so to complete, as I have other things to do... Please let me know if you would like this?

 

[igourine]

OK, that helps.

I envisage three tables here:
1. Table of welders.
2. Table of weld processes etc (per previous message)
3. Table of certifications by welder.

The third table is essentially the link between two sets of related (but independent) data. It points to welders and processes, using foreign keys to the other two tables. Each time a welder is certified for a material/process, an entry is made in the certifications table.

Think of the form as the 'presentation layer'. Its job is to pull all of the table data together in a logical fashion, using various Access constructs. The 'data layer' can be constructed independently, using the principle of one entity type ('thing') per table.

The form would use a query to retrieve the welder details. You might use a subform to list each certification for the current welder; the subform would use its own query to retrieve the relevant certification details.

Again, I am making assumptions here which might not be valid

If you would like a practical example, I'll construct an illustration DB for you. It will take me a day or so to complete, as I have other things to do... Please let me know if you would like this?

Dear Roku,
thanks for your explanation, but it would be pleasure to build your idea in example then we can make it work in proper way.
meanwhile i will try to create table & it relationship maybe i can get the issue solved

thanks again Roku.

 

[Roku]

I've made an example DB for you which has three tables. It's not clear to me what your DB is for, so I have made a number of assumptions (see table of assumptions)

The query 'qProcessList' is used to allow selection of material type, joint type and welding process from the available permutations. The key from this query is stored in the certificates table and is used to show the individual fields for material, joint and weld on form 'fListCert'. This is done by using different query variations in each of the three combo boxes (i.e. all three use the same key to retrieve separate fields from the table).

I assume that a certification is based on a specific piece of material with its own dimensions which are to be validated against a range. I have done this for thickness, but not diameter (which I realise is probably not correct). You should be able to adapt what I have done for thickness range to accommodate a diameter range as well.

There are three forms defined. 'fWelder' is the main one (which opens automatically); 'fListCert' is the subform in 'fWelder' and 'fNewCert' is called from 'fWelder' to add certifications.

Have a look at this to see where it leads you. I hope it will give you some ideas to develop the DB as you want it. Please bear in mind that this is an example only and is not suitable for productive use without further development and testing.

 

[igourine]

I've made an example DB for you which has three tables. It's not clear to me what your DB is for, so I have made a number of assumptions (see table of assumptions)

The query 'qProcessList' is used to allow selection of material type, joint type and welding process from the available permutations. The key from this query is stored in the certificates table and is used to show the individual fields for material, joint and weld on form 'fListCert'. This is done by using different query variations in each of the three combo boxes (i.e. all three use the same key to retrieve separate fields from the table).

I assume that a certification is based on a specific piece of material with its own dimensions which are to be validated against a range. I have done this for thickness, but not diameter (which I realise is probably not correct). You should be able to adapt what I have done for thickness range to accommodate a diameter range as well.

There are three forms defined. 'fWelder' is the main one (which opens automatically); 'fListCert' is the subform in 'fWelder' and 'fNewCert' is called from 'fWelder' to add certifications.

Have a look at this to see where it leads you. I hope it will give you some ideas to develop the DB as you want it. Please bear in mind that this is an example only and is not suitable for productive use without further development and testing.

Dear Roku,
the attachement was very nice idea, but since you are not in the field it is bit defficult, what i want is like that:
whenever i add new welder in my data base (form 2) the code has to go to (Qry-1 or Welder_tb or Form 1) & check if this welder is certified if this material-process based on the material data information i am using in form 2.
i build weld matrix (form 1) that how it should be looks like the welder interface.
note that you have to have all these tables & forms created since each welder will be qualified in many material-process.

i hope this form 1 will show you how it work, my issue is only code to verifiy if the welder is certified or not
please see also the Relationship

 

[igourine]

Dear Roku,
good day to you, i hope you did not forget my problem i finish every thing in my database just this problem is making hadacke to me.:banghead:

thanks for you effort & kind support

 

[igourine]

Dear All,
anyone can help from his end??:banghead:

 

[igourine]

Dear All,
any help Please

 

[igourine]

Dear ALl,
anyone can help in this forum :banghead: