ms-access - microsoft - ¿cuáles son los métodos para crear una consulta de tabla de referencias cruzadas?

Microsoft Access condensa múltiples líneas en una tabla

Question

Tengo una pregunta en MS Access 2007 y espero que alguien tenga la respuesta. Tengo una tabla larga pero simple que contiene los nombres de los clientes y los días de la semana en que se hacen las entregas. Me gustaría resumir esta tabla al listar el nombre y todos los días en un nuevo campo "ALLDays" mientras aún se conservan todos los datos.

La tabla fuente se ve algo como esto:

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

Me gustaría tener una consulta que arroje resultados como este:

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

Gracias.

Answer 1

Aquí hay una solución simple que no requiere VBA. Utiliza una consulta de actualización para concatenar valores en un campo.

Lo mostraré con el ejemplo que estoy usando.

Tengo una tabla "emails_by_team" que tiene dos campos "team_id" y "email_formatted". Lo que quiero es recopilar todos los correos electrónicos de un equipo determinado en una sola cadena.

1) Creé una tabla "team_more_info" que tiene dos campos: "team_id" y "team_emails"

2) rellene "team_more_info" con todo "team_id" de "emails_by_team"

3) crear una consulta de actualización que establece "emails_by_team" en NULL Nombre de la consulta: team_email_collection_clear ACTUALIZACIÓN team_more_info SET team_more_info.team_emails = Null;

4) Este es el truco aquí: crear una actualización una consulta Nombre de la consulta: team_email_collection_update ACTUALIZACIÓN 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 ] & ";" y [email_formatted]);

5) para mantener la información actualizada, cree una macro que ejecute las dos consultas cuando sea necesario

Primero: team_email_collection_clear

Segundo: team_email_collection_update

QED

Answer 2

Dado que esto es solo una pequeña gama de opciones, otro enfoque sin VBA sería establecer una serie de declaraciones IIF y concatenar los resultados.

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

Si eres un perfeccionista, incluso podrías deshacerte de la última coma como esta

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

También puede considerar mantenerlos en columnas separadas, ya que esto puede ser más útil si accede a esta consulta desde otro. Por ejemplo, encontrar solo instancias con un martes sería más fácil de esta manera. Algo como:

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

Answer 3

Normalmente, debe escribir una función que le permita crear una lista concatenada. Esto es lo que he usado:

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

La versión de Remou tiene la característica adicional de que puede pasar una matriz de valores en lugar de una instrucción SQL.

La consulta de muestra puede verse así:

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