Combine Multiple Records into Single Record

[Rawb]

Hey all, it's me again!

This time I think I have a stumper! You see, I need a way to combine multiple records (from the same Table) into a single Record (just concatenating strings) based on criteria. . .

For example, I have the following Table:

PartID|PartComponentID|PartUM
001|003|Sq Ft
001|004|Sq Ft
002|005|Sq Ft
002|006|Sq Ft

I want to be able to turn it into this:

PartID|PartDesc
001|003 - 004
002|005 - 006

Note that while most of the "groups" will have the same number of Records (4 actually, although 2 of them won't be concatenated), not all will.

[RuralGuy]

This should be helpful: http://allenbrowne.com/func-concat.html

[Rawb]

I was hoping for a straight SQL option. . .

Oh well, I'll give this a try and see if it works! I'm a little concerned though because there's the potential for this current Query I'm working on to become a recursive one. . .

We'll see.

[weekend00]

Did you mean you want to concat the minimum number and the maximum number for each group and ignore the middle ones? your example didnot show us clearly.

for example:
PartID PartComponentID PartUM
001 003 Sq Ft
001 004 Sq Ft
002 005 Sq Ft
002 006 Sq Ft


turn into: 001 003-006

then use the query:
select partid,first(dmin("partcomponentid","yourtable","p artid='" & partid & "'") & " - " & dmax("partcomponentid","yourtable","partid='" & partid & "'")) as partdesc from yourtable group by partid

[Rawb]

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:

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:

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:

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:

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.

[Rawb]

I've been working on a custom VBA Function (based off of the linked ConcatRelated() function) that will do exactly what I want, but I've run into a problem.

The "list of all possible glass types" Recordset in the VBA Code doesn't apply the WHERE condition like I need it to.

My Query is a UNION Query formatted as follows:

SELECT [PartID] AS PartParentID, [PartID], 1 AS PartQtyPer FROM PartMasterGLASS
UNION SELECT [PartParentID], [PartID], 1 FROM PartCrossRef
UNION SELECT [PartParentID], [PartComponentID], [PartQtyPer] FROM PartMasterASSY
ORDER BY [PartParentID], [PartID]

The problem is that I have no idea which of the three Tables will hold the PartID I'm looking for. So, if I apply the WHERE condition to the first SELECT and it doesn't return any results, then the entire UNION Query fails!

I know I can just create an unfiltered, Saved UNION Query and then reference that directly with the WHERE condition, but I was hoping for something that I could implement without saving a Query in the DB when it will only be used for 1 thing (the VBA Function).

Is there a better way to do this, or should I just "man up" and go the Saved Query route?

[RuralGuy]

You do know that a query takes up very little space in your db, right? It is just a string and a little strategy.

[Rawb]

Whelp, I went ahead and made a Saved Query for the VBA Function.

I'm not happy about it, purely from a perfectionist's standpoint - I don't like making a bunch of stuff that'll only be used by one thing - but every thing works like it should. And I guess it's only one additional Query.

Solveded!

[RuralGuy]

You have chosen 'wisely'!