[AccessD] Heck of an Access Question

On 21 Oct 2003 at 12:19, Frank Tanner III wrote:

> Ok.  I have a doozy of a problem this time.  My
> Marketing department has tasked me with creating a
> database/questionnaire for our customers.
> 
> In the data that I am going to be given, each customer
> has a specific customer number, however, based upon
> how many items that they purchased on their most
> recent invoice, they might be in the table multiple
> times.  If they purchased one item, they're in one
> time.  Two items, two times.  Et al.
> 
> My problem is this.  I need to "convert" these
> multiple entries into a single entry concatinating all
> of the non-matching data.  Such as items purchased. 
> Obviously, their customer info should be the same.
> 
> Any assistance that you can give me would be greatly
> appreciated.  Thank you.

Use a query which consolidates the common data using a GroupBy and a 
function to create the concatenated data 

ie 
Select Distinct CustomerID, CustomerName, ProductList(CustomerID) 
from SalesData 
Group By CustomerID, CustomerName

and write a function like:

Function ProductList(CustomerID) as String
Dim rs as Recordset
Dim strSQL as String
Dim strProducts as String
strSQL = "Select Itemname from SalesData " _ 
          &  "Where CustomerID = " & CustomerID 
              
Set rs = CurrentDB.OpenRecordset(strSQL)
With rs
    While not .EOF
         strProducts = strProducts and ", " & !Itemname
    Wend
End With
Set rs = Nothing
sProductList = Mid$(strProducts,3)
End Function

 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System 
Support.