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
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
SELECT {DISTINCT} {TOP <Limit>} <ConcatColumns>
FROM <Tbl>
{WHERE <Criteria>}
{ORDER BY <ConcatColumns> Asc|Desc}
<xxx> ---> function arguments
{xxx} ---> optional items
SELECT DConcat("Account","Sample") AS Accounts
FROM Sample
GROUP BY DConcat("Account","Sample");
Returns:
Accounts
-----------------------------------------------------------------------------
Acct1, Acct10, Acct2, Acct3, Acct4, Acct5, Acct6, Acct7, Acct8, Acct9
SELECT Account, DConcat("ProjectNum","Sample","[Account] = '" & [Account] & "'") AS Projects
FROM Sample
GROUP BY Account;
Returns:
Account Projects
-----------------------------------------------------------------------------
Acct1 11111, 49632
Acct10 50396
Acct2 38278, 42987, 51304
Acct3 42805, 48450, 51853, 51994
Acct4 50167, 51908
Acct5 50819
Acct6 51906
Acct7 54883
Acct8 53683
Acct9 55444
SELECT Account, ProjectNum, DConcat("User","Sample","[Account] = '" & [Account] &
"' And [ProjectNum] = " & [ProjectNum]) AS Users
FROM Sample
GROUP BY Account, ProjectNum;
Returns:
Account ProjectNum Users
-----------------------------------------------------------------------------
Acct1 11111 User2
Acct1 49632 User1, User2
Acct10 50396 User1
Acct2 38278 User1, User13, User15, User17, User18, User2, User3, User4, User5, User8
Acct2 42987 User1, User10, User13, User15, User2, User3, User5, User8
Acct2 51304 User1
Acct3 42805 User1, User16, User2, User5, User6
Acct3 48450 User1, User3, User5
Acct3 51853 User1
Acct3 51994 User1
Acct4 50167 User1, User7
Acct4 51908 User1, User3
Acct5 50819 User1, User10, User8, User9
Acct6 51906 User1, User3
Acct7 54883 User1, User3
Acct8 53683 User1, User10, User11, User12, User13, User3
Acct9 55444 User1, User10, User12, User14
SELECT ProjectNum, DConcat("Task,User","Sample","[ProjectNum] = " & [ProjectNum],
"; ",", ") AS Users
FROM Sample
GROUP BY ProjectNum;
Returns:
ProjectNum Users
-----------------------------------------------------------------------------
11111 Project Management, User2
38278 Analysis & Model Config Design, User1; Analysis & Model Config Design, User2; Benchmarking Analysis & Findings, User1; Benchmarking Analysis & Findings, User2; Benchmarking Model Building, User1; Benchmarking Model Building, User2; Central Administrator Training, User13; Data Collection, User1; Data Collection, User2; Data Collection Prep, User1; Data Integration, User1; Data Integration, User2; Data Integration, User3; Findings and Recommendations, User1; Findings and Recommendations, User2; Implementation Study, User1; Implementation Study, User15; Implementation Study, User17; Implementation Study, User2; Implementation Study Prep, User1; Implementation Study Prep, User15; Implementation Study Prep, User17; Implementation Study Prep, User2; Implementation Study Prep, User8; Installation, User4; Model Building, User1; Model Building, User2; Model Building, User5; Model Design, User1; Model Design, User2; Planning, User1; Planning, User2; Study Analysis and Findings, User1; Study Analysis and Findings, User18; Study Analysis and Findings, User2
42805 Analysis Add On, User1; Analysis and Model Configuration Design, User2; Analysis and Model Configuration Design, User6; Data Collection, User16; Data Collection, User2; Data Collection, User6; Data Collection Preparation, User16; Data Collection Preparation, User2; Data Collection Preparation, User6; Model Template Delivery and Orientation, User1; Model Template Review, User1; Model Template Review, User5; POC Analysis and XML File Delivery, User1; POC Model Load, User1
42987 Data Integration, User1; Data Integration, User2; Data Integration, User3; Findings and Recommendations, User1; Findings and Recommendations, User2; Installation, User1; Model Building, User1; Model Building, User2; Model Building, User5; Model Configuration Design, User1; Model Configuration Design, User2; Planning, User1; Planning, User2; Rollout - First Market Findings, User1; Rollout - Preparation/Planning, User1; Rollout - Preparation/Planning, User2; Rollout - Support First Market, User1; Rollout - Support First Market, User15; Rollout - Support First Market, User8; WFM - Develop Scheduling Program, User1; WFM - Develop Scheduling Program, User2; WFM - Process Design, User1; WFM - Process Design, User2; WFM - Tool Construction, User1; WFM - Tool Construction, User2; WFM - Tool Construction, User5; WFM - Tool Installation & Training, User1; WFM - Tool Installation & Training, User10; WFM - Tool Installation & Training, User13
48450 Analysis Support, User1; Data Integration, User1; Data Integration, User3; Existing Model Information Capture, User1; Extended Model QA, User1; Matrix, User1; Model Build, User1; Model Build, User5; Model Design, User1; Model Design, User5; Model QA, User1; MSI Delivery, User1; Partial Model Delete, User1; Planning, User1; Role Allocation Matrix Revision, User1; Variance Report I, User1; Variance Report II, User1
49632 Project Management, User2; WES PM, User1
50167 Configuration, User1; Configuration, User7; Final Analysis, User1; Planning and Approach, User7; POC Monitoring, User1; POC Monitoring, User7
50396 WES Project Manager, User1
50819 Analysis and Model Configuration Design, User1; Call Center (Branch) Set-Up and Configuration, User1; Data Collection, User1; Data Collection, User8; Data Collection Prepartation, User1; End User Training, User10; Forecast Build, User9; Forecast Install, User9; Plan Install, User9; Planning, User9; Project Planning, User1; Schedule Build, User9; Schedule Install, User9
51304 Transactions Report, User1
51853 Auto Generate Reports, User1; Consolidate Report Date, User1; Extract Branch Attributes, User1; Monitor Report Progress, User1; Scheduling Query, User1
51906 Configuration, User1; DAM, User3; Planning and Approach, User1
51908 DAM, User1; DAM, User3; Foundation, User1; Foundation, User3; Planning and Approach, User1; Process Analysis Configuration, User1
51994 ABR Analysis (Change Order IIII - 54644), User1; Change In Sales Averaging Period, User1; Change In Wait Time Analysis, User1; CSR Team Consolidation (Change Order II - 54134), User1; Extended Wait Time Pilot (Change Order I - 54135), User1; Wait Time Pilot Prepartation, User1; Wait Time Pilot Support and Data Analysis, User1
53683 Analysis and Model Configuration Design, User1; Data Collection, User1; Data Collection Preparation, User1; Forecast Build, User11; Forecast Build, User3; Forecasting Training, User13; Planning, User1; Planning, User11; Planning, User3; Scheduling, User12; Train the Trainer Training, User10
54883 DAM, User1; DAM, User3; Process Analysis Configuration, User1
55444 Adherence Adapter Development and Test, User1; Adherence Adapter Development and Test, User12; Analysis and Model Configuration Design, User1; Central User Training, User14; Forecast, User14; Forecast Build, User1; Forecast Build, User12; Forecast Build, User14; Planning, User1; Scheduling, User1; Scheduling, User12; Scheduling, User14; Train the Trainer Training, User10
SELECT ProjectNum, DConcat("Task,User","Sample","[ProjectNum] = " & [ProjectNum],
"; ",", ",True,"Asc",3) AS Users
FROM Sample
GROUP BY ProjectNum;
Returns:
ProjectNum Users
-----------------------------------------------------------------------------
11111 Project Management, User2
38278 Analysis & Model Config Design, User1; Analysis & Model Config Design, User2; Benchmarking Analysis & Findings, User1
42805 Analysis Add On, User1; Analysis and Model Configuration Design, User2; Analysis and Model Configuration Design, User6
42987 Data Integration, User1; Data Integration, User2; Data Integration, User3
48450 Analysis Support, User1; Data Integration, User1; Data Integration, User3
49632 Project Management, User2; WES PM, User1
50167 Configuration, User1; Configuration, User7; Final Analysis, User1
50396 WES Project Manager, User1
50819 Analysis and Model Configuration Design, User1; Call Center (Branch) Set-Up and Configuration, User1; Data Collection, User1
51304 Transactions Report, User1
51853 Auto Generate Reports, User1; Consolidate Report Date, User1; Extract Branch Attributes, User1
51906 Configuration, User1; DAM, User3; Planning and Approach, User1
51908 DAM, User1; DAM, User3; Foundation, User1
51994 ABR Analysis (Change Order IIII - 54644), User1; Change In Sales Averaging Period, User1; Change In Wait Time Analysis, User1
53683 Analysis and Model Configuration Design, User1; Data Collection, User1; Data Collection Preparation, User1
54883 DAM, User1; DAM, User3; Process Analysis Configuration, User1
55444 Adherence Adapter Development and Test, User1; Adherence Adapter Development and Test, User12; Analysis and Model Configuration Design, User1
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (27)
Commented:
A simple (and possibly stupid) question.
Regarding the query qryUsersByAcctAndProject in the DConcat.mdb sample database
How should one use the quotes and double quotes (' , ") If the ProjectNum field was a text instead of a number?
query (ProjectNum as number)
Users: DConcat("User","Sample","[
query (ProjectNum as text)
.....
I've tried some combinations of this query with a text but I'm always stucked and a bit confused with the syntax and alwaus get an error
Users: DConcat("User","Sample","[
Thanks,
Best regards,
Pedro
Commented:
Commented:
Bit of a novice I am, but I have a question.
DConcat("IMAGEORIG","Searc
My DConcat gets truncated to the 255 character limitation in it's results.
UPDATE: Sorry, I read the rest of the threads and found that when you set the field to "Expression" instead of "Groupby" - then it provides the full results. - Thanks again Patrick!
Commented:
Note: The query datasheet view shows correct results.
Note: I'm running this in an Access 2007 environment.
Open in new window
Testing code here. The vItem variable is the sample query name.Open in new window
Feel free to message me to explore/solve offline.Commented:
i don't want Sort the details in the consolidated cell , what i was list the same order need to be consolidated in single cell
how to remove the Sort option from VB code,
i try to remove the but get error
pls advice me
thank & regards,
View More