[AccessD] Cross-tab (I think)

Hi Andy

No, this is not a crosstab but a query that concatenates a value from several rows per some key.

The general method is to use a subquery and some code to concatenate but that is very slow for a large recordset. Using a temp table is very good and will run _much_ faster than any other method. The reason is that the source table is read once only, and the target table is opened once only and DAO is way fast at adding records.

In this example the key is Company + Location + Department. At you it could be Order + Materials. Of course, adjust the other field and table names too.
Note the comment on Text/Memo field.

<snip>
The trick is this:

  ' Select an ordered list of Company, Location, Department.

First, create a table (tblEmployeesList) with identical field structure as the master (here called tblEmployees). 
If the list of employees can be very long, change the field type for Employees from Text to Memo.

Then run the code below, and the requested list will be filled into tblEmplyeesList.
Finally use tblEmployeesList as your source for a report or whatever.

<code>
Public Sub EmployeesConcatenate()
 
  Dim dbs         As DAO.Database
  Dim rstRead     As DAO.Recordset
  Dim rstList     As DAO.Recordset
  
  Dim strKey      As String
  Dim strKeyLast  As String
  
  Set dbs = CurrentDb
  ' Clean tblEmployeesList.
  dbs.Execute "Delete * From tblEmployeesList"
  ' Select an ordered list of Company, Location, Department.
  Set rstRead = dbs.OpenRecordset("Select * From tblEmployees Order By Company, Location, Department")
  Set rstList = dbs.OpenRecordset("Select * From tblEmployeesList")
  
  With rstRead
    ' Loop through the records.
    While .EOF = False
      strKey = !Company.Value & !Location.Value & !Department.Value
      If strKey <> strKeyLast Then
        ' New Company/Location/Department.
        ' Create new record.
        rstList.AddNew
          rstList!Company.Value = !Company.Value
          rstList!Location.Value = !Location.Value
          rstList!Department.Value = !Department.Value
          rstList!Employees.Value = !Employees.Value
      Else
        ' Same Company/Location/Department as previous record.
        ' Append separator and expand list of employees.
        rstList.MoveLast
        rstList.Edit
          rstList!Employees.Value = rstList!Employees.Value & ", " & !Employees.Value
      End If
      ' Save the record.
      rstList.Update
      ' Store the key to compare for the next record.
      strKeyLast = strKey
      .MoveNext
    Wend
    .Close
  End With
  rstList.Close
  
  Set rstList = Nothing
  Set rstRead = Nothing
  Set dbs = Nothing
  
End Sub
</code>
</snip>

/gustav


>>> andy at minstersystems.co.uk 27-05-2009 18:16 >>>
Hi folks
This should be simple but I can't see it.

Have a query which gives a list of raw materials and suppliers, ie

Mat1 SuppA
Mat1 SuppB
Mat1 SuppC
Mat2 SuppA
Mat2 SuppD

and so on

What I want is a query result for extract to Excel which transposes the
suppliers into columns giving a single row per material. Thus:

Mat1  SuppA   SuppB   SuppC
Mat2  SuppA   SuppD
Mat3  SupZZ
etc

We must have had this a zillion times but I can't get it to go.
Heeeeeeeeeeeellppppp!
--
Andy

________________________________________________
Message sent using UebiMiau 2.7.2

-- 
AccessD mailing list
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com