If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sub Assemblys
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 |
#2
|
|||
|
|||
Sub Assemblys
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 Please feel free to quote anything I say here. Jedit wrote: 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 |
Thread Tools | |
Display Modes | |
|
|