Need to concatenate one field in new table

[MadTom]

I have two tables that are connected thur a one to many relationship. I did a Make Table query to get the data I need to make labels, but I need to

concatenate one field when all parts are in the same Bin, when NumComps > then 1 and others could be Grouped. I would like to make a new table or change the one I have in a query or VBA. Bin_NumComp (Number of Compartments), Bin_CompNum (Compartment Number). Thanks for all your help, I've learned a lot and most of the time I can find what I need, but still learning.

Bin_ID	P_Type	Bin_Num	Bin_Tag	Bin_NumComps	Bin_CompNum
1	Resistor	1	1-24 BlK	1	1
1	Resistor	1	1-24 Blk	1	1
2	Resistor	2	1-24 BLK	1	1
27	Diode	27	Led 2W	1	1
31	Capacitor	1	10pf-99pf	2	1
31	Capacitor	1	10pf-99pf	2	1
31	Capacitor	1	10pf-99pf	2	1
87	IC	17	7414	4	1
88	IC	18	7417	4	2
89	IC	19	7420	4	3
90	IC	20	7422	4	4

This is what I would like to have in new table from table above. (to make Labels)

Bin_ID	P_Type	Bin_Num	Bin_Tag_New	Bin_NumComps	Bin_CompNum
1	Resistor	1	1-24 BLK	1	1
2	Resistor	2	1-24 Blk	1	1
27	Diode	27	Led 2W	1	1
31	Capacitor	1	10pf-99pf	2	1
87-90	IC	17-20	1714, 7417, 7420, 7422	4	4

SELECT tblBins.Bin_ID, tblParts.P_Type, tblBins.Bin_Num, tblBins.Bin_Tag, tblBins.Bin_NumComps, tblBins.Bin_CompNum INTO tblTest
FROM tblBins INNER JOIN tblParts ON tblBins.Bin_ID = tblParts.P_Location
ORDER BY tblBins.Bin_ID;

[GinaWhipp]

Hmm, have a look at...
http://allenbrowne.com/func-concat.html

[accesstos]

MadTom, I believe that you can achieve the desired results with a Function like this:

Option Compare Database
Option Explicit

'Need a reference to Microsoft ActiveX Data Objects 2.# Library

Public Function ConcField(ByVal strTable As String, _
                          ByVal strFields As String, _
                          ByVal strConc As String, _
                          Optional ByVal strWhere As String = "1=1", _
                          Optional ByVal strFormat As String) As String
    Dim adoRS As ADODB.Recordset
    Dim strSQL As String
    Dim strRet As String

    strSQL = "SELECT " & IIf(Len(strFormat), "FORMAT(", "") _
             & strConc & IIf(Len(strFormat), ", """ & strFormat & """)", "") _
             & " FROM (SELECT " & strFields & " FROM " & strTable _
             & " WHERE " & strWhere _
             & " GROUP BY " & strFields _
             & " ORDER BY " & strConc & ") AS T"

    'Debug.Print strSQL

    On Error Resume Next
    Set adoRS = New ADODB.Recordset
    adoRS.Open strSQL, CurrentProject.Connection
    strRet = adoRS.GetString(adClipString, , , ", ", "...")
    adoRS.Close
    Set adoRS = Nothing
    If Len(strRet) Then
        strRet = Left(strRet, Len(strRet) - 2)
        ConcField = strRet
    End If
End Function

in a query like this:

SELECT IIf(Min([Bin_ID])=Max([Bin_ID]),Min([Bin_ID]),Min([Bin_ID])  & "-" & Max([Bin_ID])) AS MinMaxBinID, qryBins.P_Type, 
 IIf(Min([Bin_Num])=Max([Bin_Num]),Min([Bin_Num]),Min([Bin_Num]) &  "-" & Max([Bin_Num])) AS MinMaxBinNum, 
ConcField("qryBins","Bin_IDs, P_Type, Bin_Tag","Bin_Tag","P_Type='" & [P_Type] & "' AND  Bin_IDs=" & [Bin_IDs]) AS Bin_Tags, 
qryBins.Bin_NumComps,  Max(qryBins.Bin_CompNum) AS MaxBin_Comp
FROM qryBins
GROUP BY qryBins.P_Type, qryBins.Bin_NumComps, qryBins.Bin_IDs
ORDER BY IIf(Min([Bin_ID])=Max([Bin_ID]),Min([Bin_ID]),Min([Bin_ID]) & "-" & Max([Bin_ID]));

based on a query named qryBins like this:

SELECT tblBins.Bin_ID, Count(tblBins.Bin_ID) AS Bin_IDs, tblBins.P_Type, tblBins.Bin_Num, tblBins.Bin_Tag, tblBins.Bin_NumComps, tblBins.Bin_CompNum
FROM tblBins
GROUP BY tblBins.Bin_ID, tblBins.P_Type, tblBins.Bin_Num, tblBins.Bin_Tag, tblBins.Bin_NumComps, tblBins.Bin_CompNum;

Give it a try.

Cheers,
John

[MadTom]

John, this is great! but not quite there. The problem is I need to get MinMaxBinID when I have more than 1 Bin_NumComps. If I have 2 Bin_NumComps, MinMax would be 1-2, if I have 4 Bin_NumComps, MinMax would be 1-4 or x-y. I hope I'm making it clear, if I have more than one Compartment, MinMax would be x-y and P_Type is OK, MinMax BinNum would be x-y and BinTags would concatenate what is in Bin_Tag for that number of compartments if they are different and should be sorted on Bin_ID. I will go over your code and will see how it works, great way to learn. Thanks again!

MinMaxBin_ID	P_Type	MinMaxBin_Num	Bin_Tag_New	Bin_NumComps	Bin_CompNum
1	Resistor	1	1-24 BLK	1	1
2	Resistor	2	1-24 Blk	1	1
27	Diode	27	Led 2W	1	1
31-32	Capacitor	1	10pf-99pf	2	1
87-90	IC	17-20	1714, 7417, 7420, 7422	4	4
91-93	IC	21-23	7425, 7433, 7434	3	3

[accesstos]

The function uses the GetString() method of ADO.Recordset that makes the code more simple.
Works properly and no need any changes.

Try with function's arguments and grouping of queries until you take the desired results.

For example, give a try to this query:

SELECT IIf(Min([Bin_ID])=Max([Bin_ID]),Min([Bin_ID]),Min([Bin_ID]) & "-" & Max([Bin_ID])) AS MinMaxBinID, qryBins.P_Type, IIf(Min([Bin_Num])=Max([Bin_Num]),Min([Bin_Num]),Min([Bin_Num]) & "-" & Max([Bin_Num])) AS MinMaxBinNum, ConcField("tblBins","P_Type, Bin_Tag, Bin_NumComps","Bin_Tag","P_Type='" & [P_Type] & "' AND Bin_NumComps=" & [Bin_NumComps]) AS Bin_Tags, qryBins.Bin_NumComps, Max(qryBins.Bin_CompNum) AS MaxBin_Comp, qryBins.Bin_IDs
FROM qryBins
GROUP BY qryBins.P_Type, qryBins.Bin_NumComps, qryBins.Bin_IDs
ORDER BY IIf(Min([Bin_ID])=Max([Bin_ID]),Min([Bin_ID]),Min([Bin_ID]) & "-" & Max([Bin_ID]));

Cheers,
John

[MadTom]

Still working on the Query, can't get it right. How does the function work? If I could only get the Bin_Tag field to work.
Thanks!

[accesstos]

The [MaxMin...] fields are based on pure SQL expressions.

MinMaxBinID: IIf(Min([Bin_ID])=Max([Bin_ID]);Min([Bin_ID]);Min([Bin_ID]) & "-" & Max([Bin_ID]))

Only the [Bin_Tags] use the function ConcField() which, for each record of the container query, creates an inner "Group By" subquery from strTable argument (tblBins in our case), using the criteria of the strWhere to limit the records, with the strFields as fields list of inner subquery, and, returns a list of all unique values in subquery of the strConc formated as strFormat determines.

This is a schema of a simplier usage of function ConcField():



Query1...Query4 are the inner subqueries created by the function.
Uncomment the line 'Debug.Print strSQL in the code of the function to see the created SQL for each record into immediate window of VB Editor.

I hope Ι am more comprehensible now.

With previous suggestions I take those results for that data:



If you prefer the below view, remove the [Bin_IDs] field from the previous query.

[MadTom]

I'm only using two queries, qryBins and QueryBins. Both I copied from your thread. What you have is what I would like. Maybe I copied it wrong or missing something. I have the function installed. This is what I got after running 2nd Query. Thanks for all your time!
Tom
What you are saying there are four subqueries in the function?

Debug.Print strSQL prints lots of lines?
I've messed with it so much, moving names around, some names I changed to test, maybe where the problem is?

QueryBins 2nd

SELECT IIf(Min([Bin_ID])=Max([Bin_ID]),Min([Bin_ID]),Min([Bin_ID])  & "-" & Max([Bin_ID])) AS MinMaxBinID, qryBins.P_Type, 
 IIf(Min([Bin_Num])=Max([Bin_Num]),Min([Bin_Num]),Min([Bin_Num]) &  "-" & Max([Bin_Num])) AS MinMaxBinNum, 
ConcField("qryBins","P_Type, Bin_Tag","Bin_Tag","P_Type='" & [P_Type] & "' AND  Bin_IDs=" & [Bin_IDs]) AS Bin_Tags, 
qryBins.Bin_NumComps,  Max(qryBins.Bin_CompNum) AS MaxBin_Comp
FROM qryBins
GROUP BY qryBins.P_Type, qryBins.Bin_NumComps, qryBins.Bin_IDs
ORDER BY IIf(Min([Bin_ID])=Max([Bin_ID]),Min([Bin_ID]),Min([Bin_ID]) & "-" & Max([Bin_ID]));

qryBins 1st

SELECT tblTest.Bin_ID, Count(tblTest.Bin_ID) AS Bin_IDs, tblTest.P_Type, tblTest.Bin_Num, tblTest.Bin_Tag, tblTest.Bin_NumComps, tblTest.Bin_CompNum
FROM tblTest
GROUP BY tblTest.Bin_ID, tblTest.P_Type, tblTest.Bin_Num, tblTest.Bin_Tag, tblTest.Bin_NumComps, tblTest.Bin_CompNum
ORDER BY tblTest.Bin_ID;

[accesstos]

Hi Tom!

Use this SQL definition for the QueryBins:

SELECT IIf(Min([Bin_ID])=Max([Bin_ID]),Min([Bin_ID]),Min([Bin_ID]) & "-" & Max([Bin_ID])) AS MinMaxBinID, qryBins.P_Type, IIf(Min([Bin_Num])=Max([Bin_Num]),Min([Bin_Num]),Min([Bin_Num]) & "-" & Max([Bin_Num])) AS MinMaxBinNum, ConcField("tblBins","P_Type, Bin_Tag, Bin_NumComps","Bin_Tag","P_Type='" & [P_Type] & "' AND Bin_NumComps=" & [Bin_NumComps]) AS Bin_Tags, qryBins.Bin_NumComps, Max(qryBins.Bin_CompNum) AS MaxBin_Comp
FROM qryBins
GROUP BY qryBins.P_Type, qryBins.Bin_NumComps
ORDER BY IIf(Min([Bin_ID])=Max([Bin_ID]),Min([Bin_ID]),Min([Bin_ID]) & "-" & Max([Bin_ID]));

Below you can see only the function's call for the field Bin_Tags:

ConcField("tblBins","P_Type, Bin_Tag, Bin_NumComps","Bin_Tag","P_Type='" & [P_Type] & "' AND  Bin_NumComps=" & [Bin_NumComps]) AS Bin_Tags

And yes. for each record(or P_Type) in QueryBins, the function ConcField() creates an inner subquery (actually double) for each field that using it, to retrieve all values ​​for it's output, but, don't worry, this is a background function and nothing is stored into the database. Surely, with some tricks can become faster but, retrieving only the necessary records into 1st query (qryBins), I think that it's difficult to face effectiveness issues with your real data.

Every call of the function corresponds to a line in the immediate window and each printed line is a valid (if no error) SQL query definition. You can paste these lines (one by one) into SQL view of a new query to see from where the function retrieves its values. Just like my example above with the Query1, Query2, etc.

Cheers,
John

[MadTom]

Something is not right?

This is what I get. Not the same as your great pictures
Only 5 lines and Bin_Tags all on one line. Is the grouped by not right?
Thanks! Tom

[accesstos]

Hi Tom!

In my attached sample database, in query QueryBins, for the record with [P_Type]='IC ' and [Bin_NumComps]=4, the SQL below returns "7414, 7417, 7420, 7422"

SELECT Bin_Tag 
FROM (SELECT P_Type, Bin_Tag, Bin_NumComps FROM tblBins 
WHERE P_Type='IC ' AND Bin_NumComps=4 
GROUP BY P_Type, Bin_Tag, Bin_NumComps 
ORDER BY Bin_Tag) AS T

and for the next record with [P_Type]='IC ' and [Bin_NumComps]=3 returns "7425, 7433, 7434".

Give attention to the syntax of the expression of the field [Bin_Tags] which call the function as seems in my previous post and inspect the produced SQL of each line in immediate. Define carefully the function's arguments until take the desired results. In my sample, the arguments have filed as follows:

Argument	Value
strTable	"tblBins"
strFields	"P_Type, Bin_Tag, Bin_NumComps"
strConc	"Bin_Tag"
strWhere	"P_Type='" & [P_Type] & "' AND Bin_NumComps=" & [Bin_NumComps]

I can't analyse it farther.

Good luck with your project!
John

[MadTom]

John, Your Query works great, when I use my table is doesn't. So problem must be in table structure, will look into it more today. One little thing can tear your hair out.
I'm sure I can get it now.
Thanks for all your time! This forum is the best, lots of people willing to help other.
Thanks!

[accesstos]

One little thing can tear your hair out.

I'm not worry about that, I have too much to loose.
...But, as seems, you have to worry!

Thanks for all your time! This forum is the best, lots of people willing to help other.
Thanks!

You're welcome!
I'm happy to help.

Cheers,
John

[MadTom]

Hi John, After much testing, not a problem with table structure, has to be more to add.
I've attached my tables (BinsX). The Query should list Bin_ID and Bin_Num x - X, All the Tags for that one Bin, maybe from 1 to 5 compartments. Your sample looks good until you add more to it. The end result is a table to make label from for each Bin.
Thanks again for all your help!
Tom

[accesstos]

Hi Tom!

It's always good to have a lot of realistic data for testing.
Have a look in the new attachment for the tblBinsA.
Now, the first argument of the function, refers to the tblBinsA.

Tip: You have to store the numbers as numbers, not as text, to take properly results with sorting.

Excuse me now but, it's nine o'clock here, I have to go for an ouzo at the sunset.

Cheers,
John