Cascading cboBox linked to multiple Tables depending on selection of 1st Value list cboBox (1 Viewer)

[DeVos]

Hi there,

Please forgive me if I'm posting in the wrong place but not only am I new to Access but also new to forums.

So... I'm currently working as a welding & fabrication inspector / quality manager for a company in South Africa but while working I've been studying and have completed the theoretical portion of my education that will enable me to become AIA / 3rd party inspector. However, there is a practical component to the certification. This involves execution of 18 task on 5 pressure vessels / boilers and or high pressure piping systems all in the presence of an already certified inspector. I'm also required to keep documented proof of all 90 tasks (18 tasks x 5 equipment). Thus in a moment of infinite wisdom I decided to compile a database to help me keep track of my progress and eventually present my recordkeeping in a professional manner to the panel of experts appointed by the South African Institute of Welding.

Initially, I envisage my database consisting of 6 tables:

1. tbl_Task List - This will just be a table with 18 tasks and will have and ID field, Task Description Field & a Min Requirements Field
2. tbl_Vessels - Here I will store all the pertinent information about each individual pressure vessel inspected, ID, Serial #, Description, etc...
3. tbl_Boilers - Here I will store all the pertinent information about each individual Boiler / Steam Generator inspected, ID, Serial #, Description, etc...
4. tbl_Piping - Here I will store all the pertinent information about each individual piping system inspected, ID, Serial #, Description, etc...
5. tbl_Tasks Records - Here each task will be captured.
6. tbl_IPE - Which will be like a contacts table with the information of the certified inspectors that accompany me on the inspection.

Table 5 is where I get completely stuck, the fields I'd like it to contain are as follows:

1. ID
2. Task - cboBox getting the information from Table 1
3. Equipment type - Value List with 3 options Vessel, Boiler or Piping System
4. Equipment Inspected - I'd like this to be a cboBox that gets its data from either one of the 3 tables (tbl_Vessels, tbl_Boilers or tbl_Pyping System) which is dependant on the selection made in the previous field (3).
5. Inspector - cboBox listing the qualified inspector
6. Project - details of the project on which the inspection was done &
7. Attachement - Here I would like to save all the scanned paperwork / proof of the particular inspection.

That is it, nothing too fancy but damn it is easier said than done, in my case at least.

Please can any of the wise folks assist, my big problem is the correct setup of the database and then Table 5.

Thanking you all in advance,

DeVos

 

[theDBguy]

Hi. To make your life easier, I think you can combine your three tables: Vessels, Boilers, and Piping, into one tblEquiiment.

 

[arnelgp]

you also need to record the RFI number, date requested, actual date inspected.
who requested.
also if it is initial inspection or final.
what is the result: passed, failed.
reason why it fails.

you also need to add the equipment no/ heat number, etc.

 

[mike60smart]

Hi

Is this the type of Report you want to produce?

 

[arnelgp]

type of Report you want to produce

that is for welder test? it will be valuable if he is a welder test inspector.

 

[arnelgp]

welding & fabrication inspector / quality manager

as qa/qc manager it is hard to imagine that you don't have all
documents for this.
documentation is the very vital work of qa/qc together with inspection.
collection of these tools/documents are essential for building your quality assurance
book which you are required to submit to your client?

 

[mike60smart]

that is for welder test? it will be valuable if he is a welder test inspector.

Hi Arnelgp

This is a database that I helped a Welder correct a few issues with and he was happy that it meets all requirements of their Codes of Practice.

 

[arnelgp]

their Codes of Practice

actually it is Universal Standard (asme).
and all welding are subjected/adhere to that standard.

each project has their own piping material specs.
Written before the project kick-off.
this is their ruler and guide to each piping materials,
procedures, etc.

 

[mike60smart]

The Data Input Form is as shown below with random data entered:-

 

[DeVos]

Hi. To make your life easier, I think you can combine your three tables: Vessels, Boilers, and Piping, into one tblEquiiment.

Hi DBguy,

I also thought so but my concern is that the statutory information to be captured for each varies to a degree. I'm worried about blanks in my table?

 

[DeVos]

you also need to record the RFI number, date requested, actual date inspected.
who requested.
also if it is initial inspection or final.
what is the result: passed, failed.
reason why it fails.

you also need to add the equipment no/ heat number, etc.

I totally agree, but I'm not there yet and I'm dyslexic, near the end of my post was getting tired of typing

 

[DeVos]

as qa/qc manager it is hard to imagine that you don't have all
documents for this.
documentation is the very vital work of qa/qc together with inspection.
collection of these tools/documents are essential for building your quality assurance
book which you are required to submit to your client?

I do have the documentation but as I mentioned the purpose of the database is to consolidate potions of the documentation from multiple projects and inspections in one place for presentation as proof at a later date.

 

[DeVos]

Hi

Is this the type of Report you want to produce?

Hi Mike,

Thanks for the reply. Unfortunately as arnelgp pointed out that is a welder qualification, although I do do performance testing of welders it is not entirely relevant to what I need but if I can crack this current db then my next project my be a db to control the testing and re-certification of our welders.

 

[DeVos]

actually it is Universal Standard (asme).
and all welding are subjected/adhere to that standard.

each project has their own piping material specs.
Written before the project kick-off.
this is their ruler and guide to each piping materials,
procedures, etc.

In South Africa, we deal with multiple manufacturing standards not just ASME. We are sometimes required to perform repairs to old BS standards, ISO, DIN as well as our local SABS.

 

[DeVos]

The Data Input Form is as shown below with random data entered:-

Wow Mike,

That looks excellent. Would love to get my hands on a db like that.

 

[Pat Hartman]

Are all 18 tasks applied to each of the three types?
Do you collect the same information regardless of the type?

It might be helpful to post three completed reports so we can look at the data you need to collect. That will help us with the table structure. It is the responsibility of some other application to define specs so I am reluctant to repeat them here so I agree with the earlier suggestion that one table rather than three might work best.

 

[DeVos]

Good Day Pat,

Firstly, thank you for taking the time to read my post, for offering advice and going a step further by asking for more info to see how you can assist me further.

After giving it some thought I I believe combining the equipment types into one table should work. I think I could then include a field that gives you the option to define the equipment type. This table would contain all the relevant fields applicable to the 3 different equipment types. I then thought that I can design a data capture form that contains all the fields but as soon as I define the equipment type in the form it greys out the fields that are not applicable to that particular equipment type. Is this possible?

Then when the time comes to start capturing the individual tasks I would have one table for all the tasks, again using a cboBox field that allows me to define the task type. Once the task type is defined I can use a cboBox field to define the equipment type on which the task was performed, this would then filter the next field by equipment type where I choose the specific equipment that the task was performed on. Please give me your thoughts on this compilation?

Attached are the full requirements for certification as an Inspector Of Pressure Equipment (IPE) as stipulated by the South African Institute of Welding (SAIW) in this document you will see the 18 Tasks. I've also included a copy of a report that indicates the information required when capturing a individual piece of equipment.

I hope I've made sense.

Thanks again and best regards,

Rory.

 

[mike60smart]

Hi Rory
Can you upload a zipped copy of your current database?

 

[DeVos]

Hi Mike,

I will do as soon as I get home it is on my home pc.