Joining Values and Grouping

[sfalin]

I have a table (Call it TABLE1 - I receive it monthly and is pre-formatted) that reports mulitple instances for each person who has errored when creating a purchase order.

It is similar to this:

[B]
Mgr      CSR     PO     TtlPOs     TtlError[/B]
SMITH    CSR1    87654  50         4
SMITH    CSR1    85641  50         4
SMITH    CSR1    87954  50         4
SMITH    CSR1    87123  50         4
JONES    CSR2    87555  35         3
JONES    CSR2    87444  35         3
JONES    CSR2    87333  35         3

As you can see, the CSR has multiple entries (each PO that had an error), a Total # of POs processed, and the Total # of Errors (which always equals the # of POs displayed.

I would like to Report this as 2 rows of data, versus 7. In other words:

[b]
Mgr      CSR     PO                          TtlPOs     TtlError[/B]
SMITH    CSR1    87654, 85641, 87954, 87123  50         4
JONES    CSR2    87555, 87444, 87333         35         3

Any thoughts on how I could, at least, build a query to do this?

 

[ByteMyzer]

Everything else BUT what you want to do with the PO field is doable with just a query. To accomplish the PO Value concatenation you will actually have to implement some VB Code.

Here's a function you can paste into a new Code Module for the outlined example above:

[COLOR=blue]Public Function[/color] PO_Concatenate(CSR [COLOR=blue]As String[/color]) [COLOR=blue]As String[/color]

[COLOR=blue]Dim[/color] rst [COLOR=blue]As[/color] Recordset

[COLOR=blue]Set[/color] rst = CurrentDb.OpenRecordset("SELECT PO FROM TABLE 1 WHERE CSR='" _
    & CSR & "' ORDER BY PO;")

[COLOR=blue]While Not[/color] rst.EOF
    PO_Concatenate = PO_Concatenate _
        & IIf(Nz(PO_Concatenate, "") > "", ", ", "") & rst!PO
    rst.MoveNext
[COLOR=blue]Wend[/color]

rst.Close

[COLOR=blue]End Function[/color]

Save the module as PO Module.

Create a new query with the following Syntax:
[tt]SELECT Mgr, CSR, PO_Concatenate([CSR]) AS PO, First([TtlPOs]) AS TtlPOs, First([TtlError]) AS TtlError
FROM TABLE1;[/tt]

See if this works for you.

 

[sfalin]

There's a problem somewhere, but I'm not getting any errors or results. One thing is probably me. I thought I could use a sample of data, but I'm not good at reconfiguring. The data actually comes from 2 tables (tblTowsales and tblCSRs). So, here's what I have so far.

[b]saved as qryRPT-TowsaleSQL[/b]
SELECT [qryRPT-Towsale].ID, [qryRPT-Towsale].CSR, PO_Concatenate([CSR]) AS PO, First([TowSales]) AS TtlPOs, First([POs]) AS TtlError, [qryRPT-Towsale].DispDate, [qryRPT-Towsale].DispTime, [qryRPT-Towsale].Month, [qryRPT-Towsale].Year
FROM [qryRPT-Towsale];

[b]qryRPT-Towsale to Join tblTowsales and tblCSRs[/b]
SELECT tblCSRs.Site, tblCSRs.UM, tblTowSales.CSR, tblCSRs.CSR AS CSRName, tblTowSales.[PO#], tblTowSales.TowSales, tblTowSales.POs, tblTowSales.DispDate, tblTowSales.DispTime, [towsales]/[POs] AS [TS%], tblTowSales.Month, tblTowSales.Year, tblTowSales.ID
FROM tblCSRs, tblTowSales
WHERE (((tblTowSales.CSR) Like [a_id1] Or (tblTowSales.CSR) Like [a_id2] Or (tblTowSales.CSR) Like [a_id3]))
ORDER BY tblCSRs.Site, tblCSRs.UM, tblCSRs.CSR;

[b]PO Module[/b]
Public Function PO_Concatenate(CSR As String) As String

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT PO# FROM qryRPT-Towsale WHERE CSR='" _
    & CSR & "' ORDER BY PO#;")

While Not rst.EOF
    PO_Concatenate = PO_Concatenate _
        & IIf(Nz(PO_Concatenate, "") > "", ", ", "") & rst!PO#
    rst.MoveNext
Wend

rst.Close

End Function