sub-Assemblys

I’m struggling to build a query that lists the sub assembly/components of a
material.

E.g. I want to type in a batch number for a material say 1200 & display all
of the individual element batch numbers (MI-Batch) contained in it.

The problem.
I can’t get the BATCH# to contain previous batch numbers in the powder mix
table (MI-batch). E.g. When a BATCH#, 1205 for example, could be made up of
1200, 1201, 1202 in tblpowdermix.MI-Batch. These are old powder already used
& re processed.

The tables a

tblPowderbatches

MECHT_ID MAT_ID BATCH# Pow-Hist Entry Date Mix-Date
42 HE395 1200 4 22/02/2006 01/02/2006
43 HA190 1201 1 22/02/2006 01/02/2006
44 HA190 1202 1 22/02/2006 01/02/2006
45 HA190 1203 1 22/02/2006 02/02/2006
46 HA190 1204 1 22/02/2006 02/02/2006

tblPowdermix

NPOW-ID Rawmat Weight MI-Batch
42 Tungsten 250 3305
42 Nickel 9.21 3505
42 Iron 3.95 3205
43 HA/HE 108 014
43 HA/HE 108 1031
43 HA/HE 108 004
43 HA/HE 108 1034
43 HA/HE 108 1035
44 HA/HE 102 004
44 HA/HE 102 014
44 HA/HE 101 1031
44 HA/HE 101 1034
44 HA/HE 101 1035
45 HA/HE 105 1035
45 HA/HE 105 014
45 HA/HE 104 1031
45 HA/HE 105 004
45 HA/HE 105 1034
46 HA/HE 114 1034
46 HA/HE 114 1035
46 HA/HE 114 1031
46 HA/HE 114 004
46 HA/HE 114 014
I can create a query that shows the first level but get spurious results
when going deeper than 1 level, it’s a bit like a family tree??

SELECT tblPowderbatches.[BATCH#], tblPowdermix.[MI-Batch], tblPowdermix.Rawmat
FROM tblPowderbatches INNER JOIN tblPowdermix ON tblPowderbatches.MECHT_ID =
tblPowdermix.[NPOW-ID]
WHERE (((tblPowderbatches.[BATCH#])="1200"));

ANY help would be greatly appreciated, thanks in advance of your support &
help

Dear Jed:

This sounds like a rather typical heirarchical self-referencing table.
Except, you have put it into 2 tables. Or is it more than 2 tables?

The classic solution would be to build a separate query for each hierarchic
level in the "tree" representing a single product to be made.

All this may not be helpful, depending on your setup.

Now, you said:

"I can create a query that shows the first level but get spurious results
when going deeper than 1 level, it's a bit like a family tree??"

Now, I see that the "top level" table tblPowderbatches has the MECHT_ID
column that seems to reference tblPowdermix [NPOW-ID]. Your query has a
join for this.

Is it then the case that some of the rows in tblPowderMix are themselves
combinations? How have you represented this in the data?

The only likely candidate I see for that reference would be MI-BATCH. If
so, to what column in what table does it refer?

Also, if you have a failed query attempt to perform what you want, please
post that as well. Explain what it attempts to do, and in what way it
fails. That might lift some of the mystery here.

Given that there can be more than 2 levels of hierarchy, I would really have
recommended this be done in 1 table instead of 2. But the necessary
techniques will work nonetheless.

This may be a bit complex, what you have, and what you want. A clear
description of all the details would be helpful, but may be covered by the
queries and tables you have, of which you have shown quite a bit, but not
enough for me to go after this for you.

Tom Ellison


"Jedit" wrote in message
...
I'm struggling to build a query that lists the sub assembly/components of
a
material.

E.g. I want to type in a batch number for a material say 1200 & display
all
of the individual element batch numbers (MI-Batch) contained in it.

The problem.
I can't get the BATCH# to contain previous batch numbers in the powder mix
table (MI-batch). E.g. When a BATCH#, 1205 for example, could be made up
of
1200, 1201, 1202 in tblpowdermix.MI-Batch. These are old powder already
used
& re processed.

The tables a

tblPowderbatches

MECHT_ID MAT_ID BATCH# Pow-Hist Entry Date Mix-Date
42 HE395 1200 4 22/02/2006 01/02/2006
43 HA190 1201 1 22/02/2006 01/02/2006
44 HA190 1202 1 22/02/2006 01/02/2006
45 HA190 1203 1 22/02/2006 02/02/2006
46 HA190 1204 1 22/02/2006 02/02/2006

tblPowdermix

NPOW-ID Rawmat Weight MI-Batch
42 Tungsten 250 3305
42 Nickel 9.21 3505
42 Iron 3.95 3205
43 HA/HE 108 014
43 HA/HE 108 1031
43 HA/HE 108 004
43 HA/HE 108 1034
43 HA/HE 108 1035
44 HA/HE 102 004
44 HA/HE 102 014
44 HA/HE 101 1031
44 HA/HE 101 1034
44 HA/HE 101 1035
45 HA/HE 105 1035
45 HA/HE 105 014
45 HA/HE 104 1031
45 HA/HE 105 004
45 HA/HE 105 1034
46 HA/HE 114 1034
46 HA/HE 114 1035
46 HA/HE 114 1031
46 HA/HE 114 004
46 HA/HE 114 014
I can create a query that shows the first level but get spurious results
when going deeper than 1 level, it's a bit like a family tree??

SELECT tblPowderbatches.[BATCH#], tblPowdermix.[MI-Batch],
tblPowdermix.Rawmat
FROM tblPowderbatches INNER JOIN tblPowdermix ON tblPowderbatches.MECHT_ID
=
tblPowdermix.[NPOW-ID]
WHERE (((tblPowderbatches.[BATCH#])="1200"));

ANY help would be greatly appreciated, thanks in advance of your support &
help

Tom ,

tblPowderbatches
Table that contains a list of different materials we produce, each is given
unique number that starts from 1200, as defined by Quality Control department
(QC), Depending on a customer requirements, the material mix will vary to
create a material grade (Mat_ID). There is also a field that describes the
powders history from New to R3 (recrush 3, being the third time a particular
batch of powder has been re used), max of 3 times re crushed. Other fields
should be self explanatory. An example would be:
Record 42 =
The Material required is HE395
The powder batch number = 1200
Powder History = 4 (New powders only)
Entered on the 22/02/2006
Tested on the 01/02/2006

Mech_ID = Unique AutoNumber (link to Powdermix etc….)
MAT_ID = Number (Material name from Materials list table)
BATCH# =Designated powder batch number from QC
Pow-Hist =Describes level of powder from new to 3rd time re used
Entry Date = Date record was entered
Mix-Date =Date the powders were blended to create the batch number
Test-Date =Date the powder was tested (Chemical, density etc…)
Tested-by =Person who performed the tests.


tblPowdermix
Table that holds the information relating to the powder batch# mix, a mix
consists of up to 6 elements See tblRawmaterials for complete list. HA/HE
refers to a previous batch of powder that has been used & re crushed from the
main table. So our batch 1200 could appear in a later batch number as a base
element as long as it’s the same material grade “HE395�, Ans to your 1st
question?

Our example:
Record 42 has
250Kg of Tungsten with a QC batch number 3305
9.21Kg of Nickel with a QC batch number 3505
3.95Kg of Iron with a QC batch number 3205

Record 43
Is made up of 5 different powder batches 004, 014, 1031, 1034, 1035 all of
the same material grade HA190 & is the 1st time all these batches have been
re used.
Unfortunately I do not have the info in the table for this batch #.

tblRawmaterials

Table that holds a list of 6 base elements, includes HA/HE that is a re
used powder

tblRawmaterials
Rawmat_ID Material
1 Tungsten
2 Nickel
3 Iron
4 Copper
5 Molybdenum
6 HA/HE

tblMAT-LIST

Table that holds a list of different material grades we offer. Used in combo
look up on main form.

MAT-LIST
MAT_ID MATERIAL
1 HA190
2 HA193
3 HA195
4 HE360
5 HE395
6 HE397
7 HM490
8 HA188

Here are the Relations:

tblPowderbatches
1 to many on Mech_ID to tblPowdermix NPOW-ID

tblRawmaterials
1 to many on Rawmat_ID to tblPowderbatches Rawmat

MAT-LIST

MAT_ID to tblPowderbatches MAT-ID


As I said in my previous post, what I want to try & do is drill down to the
base elements used from materials 1-5, tblRawmaterials, & list their powder
batch numbers MI-BATCH
Hope this helps?


"Tom Ellison" wrote:

Dear Jed:

This sounds like a rather typical heirarchical self-referencing table.
Except, you have put it into 2 tables. Or is it more than 2 tables?

The classic solution would be to build a separate query for each hierarchic
level in the "tree" representing a single product to be made.

All this may not be helpful, depending on your setup.

Now, you said:

"I can create a query that shows the first level but get spurious results
when going deeper than 1 level, it's a bit like a family tree??"

Now, I see that the "top level" table tblPowderbatches has the MECHT_ID
column that seems to reference tblPowdermix [NPOW-ID]. Your query has a
join for this.

Is it then the case that some of the rows in tblPowderMix are themselves
combinations? How have you represented this in the data?

The only likely candidate I see for that reference would be MI-BATCH. If
so, to what column in what table does it refer?

Also, if you have a failed query attempt to perform what you want, please
post that as well. Explain what it attempts to do, and in what way it
fails. That might lift some of the mystery here.

Given that there can be more than 2 levels of hierarchy, I would really have
recommended this be done in 1 table instead of 2. But the necessary
techniques will work nonetheless.

This may be a bit complex, what you have, and what you want. A clear
description of all the details would be helpful, but may be covered by the
queries and tables you have, of which you have shown quite a bit, but not
enough for me to go after this for you.

Tom Ellison


"Jedit" wrote in message
...
I'm struggling to build a query that lists the sub assembly/components of
a
material.

E.g. I want to type in a batch number for a material say 1200 & display
all
of the individual element batch numbers (MI-Batch) contained in it.

The problem.
I can't get the BATCH# to contain previous batch numbers in the powder mix
table (MI-batch). E.g. When a BATCH#, 1205 for example, could be made up
of
1200, 1201, 1202 in tblpowdermix.MI-Batch. These are old powder already
used
& re processed.

The tables a

tblPowderbatches

MECHT_ID MAT_ID BATCH# Pow-Hist Entry Date Mix-Date
42 HE395 1200 4 22/02/2006 01/02/2006
43 HA190 1201 1 22/02/2006 01/02/2006
44 HA190 1202 1 22/02/2006 01/02/2006
45 HA190 1203 1 22/02/2006 02/02/2006
46 HA190 1204 1 22/02/2006 02/02/2006

tblPowdermix

NPOW-ID Rawmat Weight MI-Batch
42 Tungsten 250 3305
42 Nickel 9.21 3505
42 Iron 3.95 3205
43 HA/HE 108 014
43 HA/HE 108 1031
43 HA/HE 108 004
43 HA/HE 108 1034
43 HA/HE 108 1035
44 HA/HE 102 004
44 HA/HE 102 014
44 HA/HE 101 1031
44 HA/HE 101 1034
44 HA/HE 101 1035
45 HA/HE 105 1035
45 HA/HE 105 014
45 HA/HE 104 1031
45 HA/HE 105 004
45 HA/HE 105 1034
46 HA/HE 114 1034
46 HA/HE 114 1035
46 HA/HE 114 1031
46 HA/HE 114 004
46 HA/HE 114 014
I can create a query that shows the first level but get spurious results
when going deeper than 1 level, it's a bit like a family tree??

SELECT tblPowderbatches.[BATCH#], tblPowdermix.[MI-Batch],
tblPowdermix.Rawmat
FROM tblPowderbatches INNER JOIN tblPowdermix ON tblPowderbatches.MECHT_ID
=
tblPowdermix.[NPOW-ID]
WHERE (((tblPowderbatches.[BATCH#])="1200"));

ANY help would be greatly appreciated, thanks in advance of your support &
help