Databases
StackOverflow2013
Postsdbo
POA query to combine multiple row values into one field in MS-Access 2003?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POA query to combine multiple row values into one field in MS-Access 2003?
    primarykey
    data
    text
    <blockquote> <p><strong>Possible Duplicate:</strong><br> <a href="https://stackoverflow.com/questions/92698/combine-rows-in-access-2007">Combine rows in Access 2007</a> </p> </blockquote> <p>If I have two tables: let's say OrderStatus and Comments, how can I sum up all comments which are made related to one order, so that I can put them into one field?</p> <p>Tabel OrderStatus:</p> <pre><code>OrderID OrderStatus OrderDate 64 OK 13.08.2011 77 Deleted 21.06.2011 99 OK 18.04.2011 </code></pre> <p>Table Comments:</p> <pre><code>CommID CommOrder CommText 1 64 "EAN 304" 2 64 "resent" 3 64 "no. 499" 4 99 "in stock" 5 99 "EAN 111" </code></pre> <p>What I want:</p> <pre><code>OrderID OrderStatus Comments 64 OK "EAN 304, resent, no. 499" 99 OK "in stock, EAN 111" </code></pre> <p>The total number of comments related to an order status is unknown.</p> <p>I am trying to achieve this with Access SQL-subqueries. I already managed with own VBA routines called from the query builder but the OrderStatus table has more than 30,000 records and VBA is too slow (takes more than 10 minutes to generate a report). In Microsoft Access, SQL is much faster than VBA but can be complicated. From Access 2007 on, Microsoft has included <a href="http://office.microsoft.com/en-us/access-help/guide-to-multivalued-fields-HA001233722.aspx" rel="nofollow noreferrer">multivalued fields</a>, however I'm using Access 2003 which isn't capable of automatically listing multiple values comma-separated.</p> <p>Thanks for your help!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USjohnnyciao
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTDuplicate
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POA query to combine multiple row values into one field in MS-Access 2003?
      1. This table or related slice is empty.
      1. VTModeratorReview
    1. COsee: http://stackoverflow.com/questions/92698/combine-rows-in-access-2007/93863#93863
      singulars
      1. POA query to combine multiple row values into one field in MS-Access 2003?
      1. USFionnuala
    2. COLike I said, I already managed with VBA in a similar way like the thread you suggested. VBA however is much slower than SQL.
      singulars
      1. POA query to combine multiple row values into one field in MS-Access 2003?
      1. USjohnnyciao
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload