With the additional clues I was able to tease out a possible solution.
To simplify the task of keeping things in order, I did rename some of
your objects (most of them were fields containing keys). For example,
the primary key of the [tblMAT-LIST] Table I renamed by appending "_ID"
to the Table name, calling it [tblMAT-LIST_ID].
Otherwise, I left two of your Tables pretty much alone.
[tblMAT-LIST] Table Datasheet View:
tblMAT-LIST_ID MATERIAL
-------------- --------
1 HA190
2 HA193
3 HA195
4 HE360
5 HE395
6 HE397
7 HM490
8 HA188
[tblRawmaterials] Table Datasheet View:
tblRawmaterials_ID Material
------------------ -----------
1 Tungsten
2 Nickel
3 Iron
4 Copper
5 Molybdenum
6 HA/HE
The [tblPowderbatches] Table actually contains more fields than shown
here, but the hidden fields are not used in linking to other Tables.
(Your message made reference to a [Test-Date] field, but I never saw one
in your example.) I renamed [Mech_ID] or [MECHT_ID] to
[tblPowderbatches_ID] and renamed [MAT_ID] to [tblMAT-LIST_ID].
[tblPowderbatches] Table Datasheet View:
tblPowderbatches_ID tblMAT-LIST_ID BATCH# Pow-Hist
------------------- -------------- ------ --------
42 5 1200 4
43 1 1201 1
44 1 1202 1
...
To make Table Datasheets easier to read, I usually define Lookup
properties on foreign keys, since foreign keys are usually kind of
meaningless to human beings. Since [tblMAT-LIST_ID] is a foreign key
(to the [tblMAT-LIST] Table), I defined a Lookup property on it to
display the [MATERIAL] value of its record instead of its key value.
So, instead of "5" in the first record, it displays "HE395". Some
people dislike using Lookup properties on the basis that you might be
misled, looking at it, into thinking that the value you see displayed is
the value stored in the Table. This Table still contains a value of 5,
but it means "HE395". The database will work equally well if you don't
use any Lookup properties, but for this example, if you do use it on the
[tblMAT-LIST_ID] field, your results might look like this. (Oh, yes, I
did add some records, numbers 1066-1073, to contain the missing [BATCH#]
values.)
[tblPowderbatches] Table Datasheet View:
tblPowderbatches_ID tblMAT-LIST_ID BATCH# Pow-Hist
------------------- -------------- ------ --------
42 HE395 1200 4
43 HA190 1201 1
44 HA190 1202 1
45 HA190 1203 1
46 HA190 1204 1
1066 HA190 004 0
1067 HA190 014 0
1068 HA190 1031 0
1069 HA190 1034 0
1070 HA190 1035 0
1071 HA190 3205 0
1072 HA190 3305 0
1073 HA190 3505 0
I similarly renamed [tblPowdermix] fields. I added a new primary key,
[tblPowdermix_ID], and changed [NPOW-ID] to [tblPowderbatches_ID],
[Rawmat] to [tblRawmaterials_ID], and [MI-Batch] to
[tblPowderbatches_Component]. Each of the keys now has a name that
begins with the name of the Table to which it belongs.
[tblPowdermix] Table Datasheet View:
tblPowder tblPowder tblRawma Weight tblPowder
mix_ID batches_ID terials_ID batches_
Component
----------- ---------- ---------- ------ ---------
-1311845803 43 6 108 1069
-1176213494 45 6 104 1068
-1097640206 43 6 108 1070
...
As before, I find raw key values hard to work with, so I defined Lookup
properties on the foreign keys. For the last one, I defined a new Query
to look up a batch number given the value of
[tblPowderbatches].[tblPowderbatches_ID]. The Query uses the following
SQL ...
[QL_Powderbatches_Batch] SQL:
SELECT [tblPowderbatches_ID], [BATCH#]
FROM tblPowderbatches
ORDER BY [BATCH#];
.... and produces output looking like this, with values taken from the
[tblPowderbatches] Table ...
[QL_Powderbatches_Batch] Query Datasheet View:
tblPowderbatches_ID BATCH#
------------------- ------
1066 004
1067 014
1068 1031
...
Setting Lookup properties for foreign keys [tblRawmaterials_ID] and
[tblPowderbatches_Component] (and using [QL_Powderbatches_Batch] to do
this for [tblPowderbatches_Component]), the results look like this:
[tblPowdermix] Table Datasheet View:
tblPowder tblPow tblRawma Weight tblPowder
mix_ID derbat terials_ID batches_
ches_ID Component
----------- ------- ---------- ------ ---------
-1311845803 43 HA/HE 108 1034
-1176213494 45 HA/HE 104 1031
-1097640206 43 HA/HE 108 1035
-1024504407 46 HA/HE 114 1034
-991036776 42 Nickel 9.21 3505
-288161197 42 Tungsten 250 3305
-152275640 45 HA/HE 105 1034
270070341 46 HA/HE 114 014
339123015 44 HA/HE 101 1035
452566201 42 Iron 3.95 3205
500182733 45 HA/HE 105 014
555121846 46 HA/HE 114 1035
612331519 46 HA/HE 114 1031
631335454 44 HA/HE 101 1034
767936980 46 HA/HE 114 004
857687491 45 HA/HE 105 004
1272649084 45 HA/HE 105 1035
1360764528 44 HA/HE 102 014
1374601862 43 HA/HE 108 014
1652798603 44 HA/HE 102 004
1832686991 43 HA/HE 108 1031
2059715620 43 HA/HE 108 004
2117302705 44 HA/HE 101 1031
Now, I don't know exactly what you wanted your Query to display, but
after I changed the names to match my new field names, my version of its
SQL looked like this:
[Query1] SQL:
SELECT PB_1.[BATCH#],
PB_2.[BATCH#] AS [MI-BATCH],
PM.tblRawmaterials_ID AS Rawmat
FROM tblPowderbatches AS PB_1
INNER JOIN (tblPowdermix AS PM
INNER JOIN tblPowderbatches AS PB_2
ON PM.tblPowderbatches_Component
= PB_2.tblPowderbatches_ID)
ON PB_1.tblPowderbatches_ID
= PM.tblPowderbatches_ID
WHERE (((PB_1.[BATCH#])=1200));
.... and the results looked like what follows.
[Query1] Query Datasheet View:
BATCH# MI-BATCH Rawmat
------ -------- --------
1200 3305 Tungsten
1200 3505 Nickel
1200 3205 Iron
If you had something else in mind, it would help if you can post sample
data with enough records to support the kind of Query you want, and to
list what you'd like the Query's output to look like, based on your
sample data.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
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?
:
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
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
