Microsoft Access condense multiple lines in a table

Question

I have a question in MS Access 2007 and I hope someone has the answer. I have a long but simple table containing customer names and the days of the week that deliveries are made. I would like to summarize this table by listing the name and all the days into one new field "ALLDays" while still preserving all the data.

The source table looks something like this:

Name         Day  
CustomerA    Monday  
CustomerA    Thursday  
CustomerB    Tuesday  
CustomerB    Friday  
CustomerC    Wednesday  
CustomerC    Saturday  

I would like to have a query which returns results like this:

Name         ALLDays  
CustomerA    Monday, Thursday  
CustomerB    Tuesday, Friday  
CustomerC    Wednesday, Saturday  

Thanks.

Answer 1

Typically you have to write a function that will allow you to create a concatenated list. Here's what I've used:.

Public Function GetList(SQL As String _                             , Optional ColumnDelimeter As String = ", " _                             , Optional RowDelimeter As String = vbCrLf) As String 'PURPOSE: to return a combined string from the passed query 'ARGS: '   1. SQL is a valid Select statement '   2. ColumnDelimiter is the character(s) that separate each column '   3. RowDelimiter is the character(s) that separate each row 'RETURN VAL: Concatenated list 'DESIGN NOTES: 'EXAMPLE CALL: =GetList("Select Col1,Col2 From Table1 Where Table1.Key = " & OuterTable.Key)  Const PROCNAME = "GetList" Const adClipString = 2 Dim oConn As ADODB.Connection Dim oRS As ADODB.Recordset Dim sResult As String  On Error GoTo ProcErr  Set oConn = CurrentProject.Connection Set oRS = oConn.Execute(SQL)  sResult = oRS.GetString(adClipString, -1, ColumnDelimeter, RowDelimeter)  If Right(sResult, Len(RowDelimeter)) = RowDelimeter Then     sResult = Mid$(sResult, 1, Len(sResult) - Len(RowDelimeter)) End If  GetList = sResult oRS.Close oConn.Close  CleanUp:     Set oRS = Nothing     Set oConn = Nothing  Exit Function ProcErr:     ' insert error handler     Resume CleanUp  End Function 

Remou's version has the added feature that you can pass an array of values instead of a SQL statement.

---

Sample query might look like:

SELECT SourceTable.Name     , GetList("Select Day From SourceTable As T1 Where T1.Name = """ & [SourceTable].[Name] & """","",", ") AS Expr1 FROM SourceTable GROUP BY SourceTable.Name; 

Answer 2

Here is a simple solution that does not require VBA. It uses an update query to concatenate values onto a field.

I'll show it with the example I am using.

I have a table "emails_by_team" that has two fields "team_id" and "email_formatted". What I want is to collect all emails for a given team in one string.

1) I create a table "team_more_info" that has two fields: "team_id" and "team_emails"

2) populate "team_more_info" with all "team_id" from "emails_by_team"

3) create an update query that sets "emails_by_team" to NULL
Query name: team_email_collection_clear

UPDATE team_more_info  SET team_more_info.team_emails = Null; 

4) This is the trick here: create an update a query
Query name: team_email_collection_update

UPDATE team_more_info INNER JOIN emails_by_team    ON team_more_info.team_id = emails_by_team.team_id  SET team_more_info.team_emails =      IIf(IsNull([team_emails]),[email_formatted],[team_emails] & "; " & [email_formatted]); 

5) to keep the info up-to-date create a macro that runs the two queries whenever needed

First: team_email_collection_clear

Second: team_email_collection_update

QED

Answer 3

Since this is just a small range of options, another approach with no VBA would be to set up a series of IIF statements and concatenate the results.

SELECT name, 
   IIF(SUM(IIF(day = "Monday",1,0)) >0, "Monday, ") & 
   IIF(SUM(IIF(day = "Tuesday",1,0)) >0, "Tuesday, ") & 
   IIF(SUM(IIF(day = "Wednesday",1,0)) >0, "Wednesday, ") & 
   IIF(SUM(IIF(day = "Thursday",1,0)) >0, "Thursday, ") &
   IIF(SUM(IIF(day = "Friday",1,0)) >0, "Friday, ") &
   IIF(SUM(IIF(day = "Saturday",1,0)) >0, "Saturday, ") &
   IIF(SUM(IIF(day = "Sunday",1,0)) >0, "Sunday, ") AS AllDays
FROM Table1
GROUP BY name

If you a perfectionist, you could even get rid of the last comma like this

SELECT name, 
LEFT(
   IIF(SUM(IIF(day = "Monday",1,0)) >0, "Monday, ") & 
   IIF(SUM(IIF(day = "Tuesday",1,0)) >0, "Tuesday, ") & 
   IIF(SUM(IIF(day = "Wednesday",1,0)) >0, "Wednesday, ") & 
   IIF(SUM(IIF(day = "Thursday",1,0)) >0, "Thursday, ") &
   IIF(SUM(IIF(day = "Friday",1,0)) >0, "Friday, ") &
   IIF(SUM(IIF(day = "Saturday",1,0)) >0, "Saturday, ") &
   IIF(SUM(IIF(day = "Sunday",1,0)) >0, "Sunday, "),
LEN(
   IIF(SUM(IIF(day = "Monday",1,0)) >0, "Monday, ") & 
   IIF(SUM(IIF(day = "Tuesday",1,0)) >0, "Tuesday, ") & 
   IIF(SUM(IIF(day = "Wednesday",1,0)) >0, "Wednesday, ") & 
   IIF(SUM(IIF(day = "Thursday",1,0)) >0, "Thursday, ") &
   IIF(SUM(IIF(day = "Friday",1,0)) >0, "Friday, ") &
   IIF(SUM(IIF(day = "Saturday",1,0)) >0, "Saturday, ") &
   IIF(SUM(IIF(day = "Sunday",1,0)) >0, "Sunday, ")
) - 2
)
AS AllDays
FROM Table1
GROUP BY name

You may also consider keeping them in separate columns, as this may prove more useful if accessing this query from another. For instance, finding only instances with a Tuesday would be easier this way. Something like:

SELECT name, 
IIF(SUM(IIF(day = "Monday",1,0)) >0, "Monday") AS Monday,  
IIF(SUM(IIF(day = "Tuesday",1,0)) >0, "Tuesday") AS Tuesday,
IIF(SUM(IIF(day = "Wednesday",1,0)) >0, "Wednesday") AS Wednesday,
IIF(SUM(IIF(day = "Thursday",1,0)) >0, "Thursday") AS Thursday,
IIF(SUM(IIF(day = "Friday",1,0)) >0, "Friday") AS Friday,
IIF(SUM(IIF(day = "Saturday",1,0)) >0, "Saturday") AS Saturday,
IIF(SUM(IIF(day = "Sunday",1,0)) >0, "Sunday") AS Sunday
FROM Table1
GROUP BY name