Bill of material type query

[DKS]

Hi,

I have a table with all the components that I need to build a product. I
have the following information in each record

(a) product name
(b) component name (not pertinent to my question)
(c) component type
(d) status of the component
(e) lots of other info not pertinent to my question

A product has several components. A component type can be used in several
products.

One of the component types is "ABC".

For components of type = "ABC" I have ensured that I have a status of the
component. For other components this value can be blank in my table.

My need is as follows:
I want to list all the items in my table with a status value. If the
component has a non-blank status value then that must be displayed. If the
component has a blank status value then the following rule must be used.


Based on the status of the component type = "ABC", I want to apply the same
status to every other component belonging to the same product, only if it's
status value is blank.

I tried doing some join queries but did not succeed. And so this call for
assistance.

Many thanks in anticipation.
(a123)

 

[Phil Smith]

If I understand what you are looking for, you need to run a seperate
query that will produce a list of valid status codes by component type,
(and probably product name) and join that to your existing query.

Then, for the status field for each record, use an
iff(status="",statusfromotherquery,status)

Phil

 

[DKS]

Hi Phil,

You have replied to one part. Yes that is one of the things I needed.
Thanks.

The other part is the writing of the separate query. I am unable to write
that separate query. Any help is appreciated.

Thanks in anticipation.

 

[Phil Smith]

Tough without knowing your data structure.

You will have perhaps a dozen records for any product name, 1 for each
component. Right?
Will you ever have more then one component of the same type for any
product name?
If so, and both have valid status, which status would you wish to use?
One of your components is a polybag. Lots of your products have this
component. Is "status" strictly related to polybags, or would the
status of "polybags" vary depending on which product it is assembled into?
You say "Based on the status of the component type = "ABC","
every product have that particular component type in it's BOM?

I would say that your Database structure is probably not optimal here.

I would have a table for Products, one for Components, and one for
Assemblies, which would have a record for each component/product
combination. Status of the component would be stored in the component
table, if the status is component related. For instance, "Status" means
"do we have it in stock?" It does not matter what product you are
assembling, either this stuff is in stock or it is not.
If the status varies based on which product it is assembled with, then
that status would be stored in the Assembly table.
Examples of teh types of products compnents and "status" might help.