Microsoft Access condense multiple lines in a table
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
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
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;