Unfortunately no, the parts won't always be the first and last. Some of them will only have the first (but it will have a Qty of 2+). In that case, I'd like to be able to output that item as many times as the Qty says, but I don't have to.
Your solution is closer to what I'm looking for though in that its straight SQL. Is there a way to alter your Query to look at another Table/Query to see if it should output the PartComponentID or not?
For example, the ACTUAL (current) Table layout I'm working with is as follows
Note that the Tables that define values for PartMasterGLASS.ColorID and PartMasterASSY.PartAssyTypeID are not shown. Also note that PartID values 7279 and 7280 exist in another table called PartMasterOTHER (not shown).
Table PartMasterGLASS:
Code:
PartID|PartColorID|PartLT|PartThick|PartSupplierID|PartIsInventoried|PartIsActive|PartUMUseID
101|14|89.00%|3.00|1|True|True|1
102|16|90.00%|3.20|1|True|True|1
179|22|26.00%|3.00|1|True|True|1
Table PartCrossRef:
Code:
PartParentPartID|PartID|PartIsTempered|PartIsFretted
101|321|True|False
102|155|True|False
102|750|True|False
102|863|True|True
179|180|True|False
179|907|True|True
Table PartMasterASSY:
Code:
PartParentID|PartSeqID|PartComponentID|PartQtyPer|PartIsInner|PartAssyTypeID
001|0|155|2|False|1
001|1|7279|1|False|1
001|2|7280|1|False|1
004|0|155|1|True|1
004|1|7279|1|False|1
004|2|7280|1|False|1
004|3|323|1|False|1
I want to create a "virtual" Field that consists of the different PartMasterASSY.PartComponentID values, but ONLY if they exist in either PartMasterGLASS.PartID or PartCrossRef.PartID (I have a UNION Query that combines the two called qryPartMasterGLASS that lists all of the PartID values in a single Recordset).
Although it will need to go through a few more Queries after this one, the end result is to end up with a Recordset with the following information:
Code:
GlassID|GlassDesc
001|155 / 155 Insulated
004|155 / 323 Insulated
101|Xmm YYY color ZZ% LT, Annealed
102|Xmm YYY color ZZ% LT, Annealed
155|Xmm YYY color ZZ% LT, Tempered
179|Xmm YYY color ZZ% LT, Annealed
180|Xmm YYY color ZZ% LT, Tempered
321|Xmm YYY color ZZ% LT, Tempered
750|Xmm YYY color ZZ% LT, Tempered
863|Xmm YYY color ZZ% LT, Tempered, Fretted
907|Xmm YYY color ZZ% LT, Tempered, Fretted
The only part I'm having trouble with are the two lines that say "Insulated" in them, the rest I've figured out already.