[AccessD] Cross-tab (I think)

I get it Gustav, many thanks. No wonder I couldn't see how to do it with a
crosstab.

Andy

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: 27 May 2009 16:42
To: accessd at databaseadvisors.com
Subject: Re: [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


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