G
Guest
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 are:
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
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 are:
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