Query- combine two lines into 1 (1 Viewer)

Number11

Member
Local time
Today, 09:09
Joined
Jan 29, 2020
Messages
607
So i have a table that contains

ID
AccountNumber
SerialNo
code


data is stored like this..

ID ¦ AccountNumber ¦ SerialNo ¦ Code
========================
2010 ¦ G1155854122 ¦ DHHT144744 ¦ 002GH112
2011 ¦ G1155854122 ¦ DHHT144744 ¦ 066577

And i need the query to show like this:

AccountNumber ¦ SerialNo ¦ CodeA ¦ CodeB
G1155854122 ¦ DHHT144744 ¦ 002GH112 ¦ 066577

duno how to do this please can you hlelp?
 
Last edited:

Number11

Member
Local time
Today, 09:09
Joined
Jan 29, 2020
Messages
607
think i got this, created two queries max and min of the ID then joined them together
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:09
Joined
Feb 19, 2013
Messages
16,553
for a crosstab the trick is being able to define a column heading - and there is a limit of 255 columns. At the moment I'm not seeing what that might be other than using the ID in some way - using dsum to calculate a row number for example
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:09
Joined
Feb 19, 2013
Messages
16,553
created two queries max and min of the ID then joined them together
so there are never more than two records for any account/serial number?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:09
Joined
Feb 28, 2001
Messages
27,001
Past a certain point, when merging lines that have some but not all parts in common, you might consider a function that takes a GROUP BY of the parts that ARE in common.

Be aware that in such cases, the resultant query will not be updateable and might be difficult to use as a .RecordSource for something else. If this query is used as the base for another query, that next query might also become impossible to update. Therefore, any such query will have to be limited in usage.

Do you actually need a query? Because it might be possible to do this kind of thing on a form or report.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:09
Joined
Feb 19, 2002
Messages
42,981
This is not a situation where you would use a crosstab query. I'm assuming you want to concatenate these records because you are exporting a file to a different application that needs a flat record. The concat function would probably be better for this use.

Here is code written by Patrick G. Matthews,

Code:
Concatenate many-side values
1Introduction

A common request in the various database zones at Experts Exchange involves returning
a concatenated list of the various items in ColumnB for each distinct value in ColumnA
from a particular table or query.  For example:

I have a table that currently holds student subject enrolments with the following fields

Student_ID, Subject_ID
1001, 11001
1001, 11023
1001, 11049
1002, 11023
1002, 11046
1003, 11023
etc

So a student can appear in the list a variable number of times depending on how many
subjects they are enrolled in. Some students may be in 10 subjects some maybe in as
little as one. I want to be able to transpose the information stored in this table and
export it in a linear format something similar to.

Student 1, subject 1, subject 2, subject 3 ... subject n
student 2, subject 1, ,,,subject n

Like other databases, Access does not offer native functions that will do this.  One
could use subqueries to return columns for each subject as above.  However, the
following limitations apply:

•In a Jet SQL solution, you would have to know how many detail values to allow for, and you could not simply let the SQL statement determine it dynamically


•You could use VBA to dynamically generate a SQL statement, but that is beyond the skill of beginning Access developers, and many intermediate developers as well


•Any such scheme relying on subqueries would require a column providing an ordinal for
the detail items within the group

To overcome these obstacles, and to make it easier for most developers to seamlessly
integrate this sort of functionality into their Access projects, I decided to create a
UDF patterned after the more familiar "domain aggregate" functions such as DSum or
DCount. So, the "DConcat" function was created.  As the name suggests, this function
concatenates the values of a detail column (or detail columns) based on the groupings
suggested by criteria the user supplies.

2The DConcat() Function

Here is the VBA source code for DConcat:

Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
    Optional Delimiter1 As String = ", ", Optional Delimiter2 As String = ", ", _
    Optional Distinct As Boolean = True, Optional Sort As String = "Asc", _
    Optional Limit As Long = 0)
    
    ' Function by Patrick G. Matthews, basically embellishing an approach seen in many
    ' incarnations over the years
    
    ' Requires reference to Microsoft DAO library
    
    ' This function is intended as a "domain aggregate" that concatenates (and delimits) the
    ' various values rather than the more usual Count, Sum, Min, Max, etc.  For example:
    '
    '    Select Field1, DConcat("Field2", "SomeTable", "[Field1] = '" & Field1 & "'") AS List
    '    FROM SomeTable
    '    GROUP BY Field1
    '
    ' will return the distinct values of Field1, along with a concatenated list of all the
    ' distinct Field2 values associated with each Field1 value.
    
    ' ConcatColumns is a comma-delimited list of columns to be concatenated (typically just
    '   one column, but the function accommodates multiple).  Place field names in square
    '   brackets if they do not meet the customary rules for naming DB objects
    ' Tbl is the table/query the data are pulled from.  Place table name in square brackets
    '   if they do not meet the customary rules for naming DB objects
    ' Criteria (optional) are the criteria to be applied in the grouping.  Be sure to use And
    '   or Or as needed to build the right logic, and to encase text values in single quotes
    '   and dates in #
    ' Delimiter1 (optional) is the delimiter used in the concatenation (default is ", ").
    '   Delimiter1 is applied to each row in the code query's result set
    ' Delimiter2 (optional) is the delimiter used in concatenating each column in the result
    '   set if ConcatColumns specifies more than one column (default is ", ")
    ' Distinct (optional) determines whether the distinct values are concatenated (True,
    '   default), or whether all values are concatenated (and thus may get repeated)
    ' Sort (optional) indicates whether the concatenated string is sorted, and if so, if it is
    '   Asc or Desc.  Note that if ConcatColumns has >1 column and you use Desc, only the last
    '   column gets sorted
    ' Limit (optional) places a limit on how many items are placed into the concatenated string.
    '   The Limit argument works as a TOP N qualifier in the SELECT clause
    
    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim ThisItem As String
    Dim FieldCounter As Long
    
    On Error GoTo ErrHandler
    
    ' Initialize to Null   
    DConcat = Null   
    ' Build up a query to grab the information needed for the concatenation
    
    SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & _
            IIf(Limit > 0, "TOP " & Limit & " ", "") & _
            ConcatColumns & " " & _
        "FROM " & Tbl & " " & _
        IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
        Switch(Sort = "Asc", "ORDER BY " & ConcatColumns & " Asc", _
            Sort = "Desc", "ORDER BY " & ConcatColumns & " Desc", True, "")
        
    ' Open the recordset and loop through it:
    ' 1) Concatenate each column in each row of the recordset
    ' 2) Concatenate the resulting concatenated rows in the function's return value
    
    Set rs = CurrentDb.OpenRecordset(SQL)
    With rs
        Do Until .EOF           
            ' Initialize variable for this row           
            ThisItem = ""           
            ' Concatenate columns on this row
            
            For FieldCounter = 0 To rs.Fields.Count - 1
                ThisItem = ThisItem & Delimiter2 & Nz(rs.Fields(FieldCounter).Value, "")
            Next
            
            ' Trim leading delimiter           
            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)           
            ' Concatenate row result to function return value           
            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
            .MoveNext
        Loop
        .Close
    End With   
    ' Trim leading delimiter   
    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)   
    GoTo Cleanup

ErrHandler:   
    ' Error is most likely an invalid database object name, or bad syntax in the Criteria   
    DConcat = CVErr(Err.Number)   
Cleanup:
    Set rs = Nothing   
End Function

The DConcat function will produce a concatenated list of all of the values for the
specified column(s) in a specified table or query pertaining to the records that meet
the criteria specified in the Criteria argument (or all rows in the table/query if
no criteria are specified).

The DConcat function takes the following arguments:

•ConcatColumns are the detail columns whose values are to be concatenated.  This will
usually be a single column, but can be multiple columns.  If more than one column, use
commas to delimit the column names.  If column names contain anything other than
letters, numerals, or underscores, column names must be enclosed in square brackets

•Tbl is the name of table or query from which the data are taken.  If the table/query
name contains anything other than letters, numerals, or underscores, the name must be
enclosed in square brackets

•(Optional) Criteria specifies the criteria to be applied in selecting and grouping
the detail information.  To specify multiple criteria, and And or Or.  Be sure to use
single-quotes around text values and # around dates/times.  I recommend using square
brackets around all column names


•(Optional) Delimiter1 is the delimiter to be used when concatenating each result
row to the return value.  The default value is comma + space

•(Optional) Delimiter2 is the delimiter to be used when concatenating the values in
the ConcatColumns for each result row.  The default value is comma + space

•(Optional) Distinct indicates whether all instances of the detail information is
returned, or just the distinct items.  True by default

•(Optional) Sort indicates whether the detail data are sorted before concatenation. 
Use "Asc" for ascending (default) or "Desc" for descending.  Any other values are
ignored.  Please note that if multiple columns are specified in ConcatColumns and if
you use

•(Optional) Limit specifies an upper limit for how many detail rows are concatenated. 
If less than one or omitted, then no limit is applied

Each time DConcat is called, the code will run a query structured like this:

SELECT {DISTINCT} {TOP <Limit>} <ConcatColumns>
FROM <Tbl>
{WHERE <Criteria>}
{ORDER BY <ConcatColumns> Asc|Desc}

<xxx> ---> function arguments
{xxx} ---> optional items

The code then loops through that result set:
•If ConcatColumns specifies more than one column, the column values for each returned
row are concatenated using the Delimiter2 value as the delimiter
•Those concatenated rows are then concatenated into a larger string, using the
Delimiter1 value as the delimiter
Implementing DConcat in Your Access Projects
To implement DConcat in your Access project, all you need to do is add the source
code above to a "regular" VBA module (i.e., not a class module, nor a code module
for a Form or Report).  Once you have done this, you will be free to use the function
in your queries, forms, or reports in the Access UI, or you may call DConcat from your
other procedures in the VBA project.
 

Users who are viewing this thread

Top Bottom