Multiple Field Parameter Query

[GoingCrazy]

I don't know if I can do this or not but here's what I'm dealing with:

I have a table set up as a matrix where the first column is a highest assembly part number. The second is a dash number corresponding to the assembly number and the third is a component part number that is required to build the assembly. For lack of a better way to handle it, the remaining columns are set up as yes/no fields that, when selected, will determine what parts are required on a particular aircraft by serial number.

The question is: Is there a way to set up a parameter query or something to allow a person to pull all the parts required by aircraft serial number? I am hoping to get away from having to write a hundred or more individual queries by aircraft serial number. I am at a standstill until I can resolve this problem. I am open to any suggestions, even if it means redesigning the tables. Anything you all can offer would be greatly appreciated.

 

[tlbroadbent]

Can you explain how the selection should work? I can't determine how serial numbers are related to the table you describe. Is another table involved or some sort of algorithm? Perhaps you could provide sample data. Terry

http://members.home.net/tlbroadbent/index.htm

 

[MichaelRed]

I also fail to see the Aircraft Serial number having any use in your scheme. In a very generic process, I would suggest that the design be a table for the aircraft.

It would have three (or so) fields. AirCraft (serial Number): Subassembly (Subassembly Id): Other info Pertinent to the AIRCRAFT as Necessary.

An additional Table, Subassembly, with three (or so fields):

Subassembly: PartNumber: Superassembly (Parent for sub-subassemblies): Other info Specific to the subassembly (Manufacturer, Weight, Height, Width, Length ...)

The second table would be built in the recursive sense, as indicated by the superassembly field.

In this schema, the aircraft is an entity made up entirely from subassemblies (which may also be made up from subassemblies, which may aslo be ...). If the subassemblies are reasonably able to be categorized (airframe, engine, flight controls, electronics, ..., other), you could add this as a field in the subassemblies. This could simplify the retrievial of groups of entries.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[GoingCrazy]

OK basically the table is set up as follows:

AssemblyPN Dash # CompPN SN1 SN2 SN3 SN4 SN5
30-555999 -200 123456-501 y y y n n
30-432566 -200 123555-100 y y y n n
30-665432 -300 222333-200 n n n y y
20-123456 -300 454545-001 n n y y y

And so on. The SN() columns show whether the assemblies and their associated parts are on that particular aircraft. There may be multiple components for each assembly. I have a form that allows the user to update the effectivity with a check box. What I want to do, for example, is run a report that will pull all the assemblies and their associated components for SN3. So anything that is checked yes under SN3 would be shown in the report. If I can break this down into 2 or 3 tables and get the same results, that's not a problem. I just don't see how to relate the information without a common field.

 

[MichaelRed]

Actually, you CAN do this in a single table. the problem with your approach is that you need a new column for each 'aircraft' nad - if one (aircraft) is taken from the system you either need to delete the column to 'carry' the excess baggage around.

On hte other hand, an Aircraft is just another 'assembly', so all you rerally need is an entry in the form of parent child, where the top level "parent" refers to the aircraft and the top level subasemblies refer to this "parent". Further levels of subassemblies then just refer to their 'parents'. A recursive query (search) from any subassembly either up (and/or)or down the tree gets whatever level of information desired.

Refering to my earlier post, including 'categories' for each subassembly also breaks down the aircraft for searches on particular "Types" of parts. Including serial numbers, manufacturers ... manufacturer date, inservice-date, maintenance dates, maintenance periodic scheduals ... would add the capability to find specific parts/subassemblies by a variety of approaches.

BUT, the above would require you to re-design the database (and particularly the table above), as well as getting to the point where terms like recursive query are not cause for confusion. (Actually for all the smoke and thunder, they are not all that hard!)


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[GoingCrazy]

Thanks for your help. But under your scenario, wouldn't I have to then list the associated parts multiple times so they would be linked to each effected top assembly (the aircraft)? I mean if I do the following:

AC_assy AssyPN Dash# CompPN
001 30-444444 -001 1234544-002

And AC_assy 002 and 004 required the same Assembly, I would have to list it at least two more times to show the relationship. Is that right? If so, I'm looking at a monster table. But if that's the only way Access will let me do it, I guess I'm stuck with it.

Thanks again for your assistance and I will research recursive queries further and think about your suggestions above.

 

[bmegenity]

What about the idea of linking your serial number to a date produced, from there to a bill-of-materials with a valid date range. When you access the serial number and date, it will use a bill-of-materials explosion to create a complete parts list (each of which has been qualified by sub-assembly and date parameters). Of course it would be more involved than this, but I would think that this approach might work easiest.

 

[MichaelRed]

GoingCrazy,

The parent-child in the single table is NOT the only way, just a convenient way for modest grooups of data. I have used it in situations involving a few thousand records, where there is a somewhat limited number of parents for a single child (3 OR 4 parents per child) and it doesnt really make the resulting data set all that much larger. If you are dealing with a lot of "top level" parents (aircraft) which are the same or very similar, then the parts breakdown by subassembly in the single table may not be the corrrect approach.

Back to relationl 101, if you have amny aircraft and many subassemblies (many items in common), then you end up with a "many to many" relationship at the first table relationship. This will require a translation table to get unique relationships. Without knowing (much more than I need to know) the overall application requirements, it is not pratical to offer detailed advice. In a generic sense, most database apps are just "models" of some process which can be carried out with pencil and paper. Look at what is going on in the paper version of the process and start with the concept of just moving all of the pencil processing to a keyboard. After you have this concept FIRMLY in hand (and mind), consider how the various pieces of paper are 'related' and filed. Include this as a set of tables which are filled via the keyboard. Eliminate some of the redundancy. Then start to refine the process.

I have no experience in aircraft records system. I only have some scraps of ideas from the media coverage of incidents involving them. That suggests that everything in/on/around an aircraft is tracked - not only the amke and model, but by serial number right down to the smallest removable part. This would sugggest the need to be able to track these assemblies at the same level of detail.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[GoingCrazy]

Thanks for taking the time to answer a not so simple problem after all. I will take a look at the many to many relationship and other options that you all mentioned. It's great to be able to kick ideas around with other folks.