Fun with Concatenation

[bikermo]

I am compiling a mailing list from an ordering system file that is imported into Access from a comma separated text file. I may have 15 or more "unique" records for one customer/mailing address. We don't want to send them 15 letters, so I need to combine certain fields and separate data by a comma and a space (i.e. ship dates, customer purchase order numbers, etc.) and the quantity field needs to have a sum across all records (final result: a letter which uses the db fields to say something like- we sent you 21 (summed quantity) of item blah blah blah on 5/1/12, 10/15/12, 12/1/12, PO# 123456, 456789, 789123 (concatenated fields)). I found and edited code to make this work, but I have 3 problems.

First, I’m not getting a sum on those records only a count (I circumvented this problem by running a query to sum, then appending that data to the table data created in concatenation but it’d be nice if that step could be accomplished via this code).

Second, I’d like to remove duplicates. We may have had a distributor who ordered the same item several times on one order date and purchase order number (we’re working with IS to prevent this in the future but it hasn’t been implemented yet).

Third, I don’t want the extra comma and space at the end of every concatenated field. This is placed there via my code even when the field has not been combined with the next record, so I end up losing all the time I saved removing those characters from every remaining record.

Here’s the code I borrowed from another thread in this forum, edited to fit my table:

[FONT=Calibri][SIZE=3]Option Compare Database[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Option Explicit[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]Private Sub Command10_Click()[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]Dim db As Database[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim rsGet As Recordset[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim rsWrite As Recordset[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim varShip2Attention As Variant[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim varNextShip2Attention As Variant[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim strBuild As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim strBuild2 As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim strBuild3 As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim InvNum As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim Address1 As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim Address2 As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim Address3 As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim Address4 As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim address5 As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim Address6 As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim City As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim State As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim Zip As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim Country As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim PurchaserEmail As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim ProdCode As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim PrductDecrip As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim Size As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim intNumElements As Integer[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]Set db = CurrentDb()[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Set rsGet = db.OpenRecordset("SELECT Ship2Attention, InvNum, Address1, Address2, Address3, Address4, Address5, Address6, City, State, Zip, Country, PurchaserEmail, ProdCode, PrductDecrip, Size, ShipDate, PO, LotNum FROM tblEXPORT ORDER BY Address1, Ship2Attention, ShipDate")[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Set rsWrite = db.OpenRecordset("tblEXPORT2")[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]Dim mySQL As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]mySQL = "Delete * from tblEXPORT2"[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]CurrentDb.Execute mySQL, dbFailOnError[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]With rsGet[/SIZE][/FONT]
 
[SIZE=3][FONT=Calibri] Do While Not .EOF[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       varShip2Attention = ![Ship2Attention][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .MoveNext[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       If Not .EOF Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]           varNextShip2Attention = ![Ship2Attention][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Else[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]           varNextShip2Attention = "EOF"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       End If[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .MovePrevious[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]       InvNum = InvNum & ![InvNum][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Address1 = Address1 & ![Address1][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Address2 = Address2 & ![Address2][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Address3 = Address3 & ![Address3][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Address4 = Address4 & ![Address4][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       address5 = address5 & ![address5][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Address6 = Address6 & ![Address6][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       City = City & ![City][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       State = State & ![State][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Zip = Zip & ![Zip][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Country = Country & ![Country][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       PurchaserEmail = PurchaserEmail & ![PurchaserEmail][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       ProdCode = ProdCode & ![ProdCode][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       PrductDecrip = PrductDecrip & ![PrductDecrip][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Size = Size & ![Size][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       strBuild = strBuild & ![ShipDate] & ", "[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       strBuild2 = strBuild2 & ![PO] & ", "[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       strBuild3 = strBuild3 & ![LotNum] & ", "[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       intNumElements = intNumElements + 1[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]       If Not (varShip2Attention = varNextShip2Attention) Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]         'add record to table[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]           strBuild = Left(strBuild, Len(strBuild) - 1)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]           With rsWrite[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               .AddNew[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !Ship2Attention = rsGet![Ship2Attention][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !InvNum = rsGet![InvNum][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !Address1 = rsGet![Address1][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !Address2 = rsGet![Address2][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !Address3 = rsGet![Address3][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !Address4 = rsGet![Address4][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !address5 = rsGet![address5][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !Address6 = rsGet![Address6][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !City = rsGet![City][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !State = rsGet![State][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !Zip = rsGet![Zip][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !Country = rsGet![Country][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !PurchaserEmail = rsGet![PurchaserEmail][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !ProdCode = rsGet![ProdCode][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !PrductDecrip = rsGet![PrductDecrip][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !Size = rsGet![Size][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !ShipDate = strBuild[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !PO = strBuild2[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !LotNum = strBuild3[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               !Quantity = intNumElements[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               .Update[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]           End With[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]         're-initialize variables[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]           strBuild = ""[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]           strBuild2 = ""[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]           strBuild3 = ""[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]           intNumElements = 0[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       End If[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]       .MoveNext[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   Loop[/FONT][/SIZE]
 
[FONT=Calibri][SIZE=3]End With[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT]

I fully accept that I may be going about this using the wrong type of code, but it was the first chunk I could find and make work, and am open to any suggestions on changing it!

 

[tiger]

Yes! You are using a standard SELECT query. You need a summary query. Open your query with the query editor. Click the SUMMARY icon (Summary Option attachment).

Ensure you select either GROUP (eg for Customer) or SUM (eg for Quantity) or COUNT (eg for OrderID)
HTH
t

BTW: The recordset is ok for many fields but also look at DCount, DSUM functions for quickly getting just one or two vals.

 

[bikermo]

It's not running through a query though, it's VBA code attached to the "on click" event of a button on a form/menu.

Forgot to mention also that I'm using Access 2007.

 

[tiger]

Sorry for the slow reply:
You may be running it via VBA but it is still a SELECT query. Take the query

SELECT Ship2Attention, InvNum, Address1, Address2, Address3, Address4, Address5, Address6, City, State, Zip, Country, PurchaserEmail, ProdCode, PrductDecrip, Size, ShipDate, PO, LotNum FROM tblEXPORT ORDER BY Address1, Ship2Attention, ShipDate

and open the query editor. Select the SQL option rather than the Design option. Paste the query into it. Once it is in there switch back to Design mode. Click the TOTALS button (Greek letter Sigma - looks like a squished in capital E). A TOTALS row appears. Select COUNT, SUM or GROUP as appropriate. When you switch back to SQL View the query will now look something like this (NOTE: Count(InvNum) and the GROUP BY clause:

SELECT Ship2Attention AS Expr1, Count(InvNum) AS Expr2, Address1 AS Expr3, Address2 AS Expr4, Address3 AS Expr5, Address4 AS Expr6, Address5 AS Expr7, Address6 AS Expr8, City AS Expr9, State AS Expr10, Zip AS Expr11, Country AS Expr12, PurchaserEmail AS Expr13, ProdCode AS Expr14, PrductDecrip AS Expr15, Size AS Expr16, ShipDate AS Expr17, PO AS Expr18, LotNum AS Expr19
FROM tblEXPORT
GROUP BY Ship2Attention, Address1, Address2, Address3, Address4, Address5, Address6, City, State, Zip, Country, PurchaserEmail, ProdCode, PrductDecrip, Size, ShipDate, PO, LotNum, Ship2Attention, ShipDate
ORDER BY Address1, Ship2Attention, ShipDate;

To make it more effective remove most of the columns and split it into two queries. Use a GROUPed query to get the Totals, Sums etc and a SELECT query to look up the fill in details

You may find (I certainly do) it can be a lot easier to build your queries in the Query Editor, make sure they are returning what you want, then switch to SQL View, copy the sql code to the clipboard and paste it into your code.

It is also possible to build a more complex sql statement using nested queries to do what you want but I am guessing you may not be a SQL expert.

BTW: I notice you have two SET statements to open the recordsets. Do not forget to have two statements clearing the recordsets.

Set rsGet = nothing
Set RsWrite = nothing

to release the allocated resources. I'm not sure how good later versions are at releasing resources, someone else may like to comment on this. I learned early on to ensure ALL recordsets are released before exiting the sub/fn.

Also have a read of the W3 Schools notes on GROUPing here: w3schools dot com/sql/sql_groupby.asp (Sorry I do not have enough posts to post links)

HTH
td

 

[tiger]

Oooops
Also set the DB = nothing

When using your recordsets try to use snapshots for reads
eg SET rsGET = currentdb.openrecordset(sqlText, dbOpensnapshot)

Snapshots cannot be used for writing so
SET rsWRITE = currentdb.openrecordset(sqlText, dbOpenTable)

Opening a table for writing can be useful as it allows you to set an INDEX which can be used for very quick lookup using the SEEK to verify there is no existing record before you attempt to write.