Concatenate field in table if IDs match

[hext2003]

Here is sample data

Test Table 1
ID NUM DESC
1 1 Hi
1 2 Every
1 3 One
2 1 See
2 2 You
3 1 Welcome
4 1 Thank
4 2 You

I want to make this table become

Test Table 2
ID NUM DESC
1 1 Hi Every One
2 1 See You
3 1 Welcome
4 1 Thank You

I would like this automated with VB - It doesn't have to create a NEW table if there is a way to do this without. There are 22,000 records in this table and it will shrink down to 15,000 after this concatenate. I have an button that will trigger this cleanup.

Any Thoughts?

 

[Remou]

Have a look here faq701-4233

 

[hext2003]

Yes Actually I have looked there, and was having some trouble understanding parts of the Code (Newbe Here)

I have a Module and this code copied into it.

I have a button that triggers the update.

Do I put the code section

'SELECT ID,
'Concatenate("SELECT Desc FROM testTable1
' WHERE ID =""" & [ID] & """") as Desc
'FROM testTable1

In my On Click Function and it will call the Concatenate function in the Module???

In the Where line what does the As FirstNames (I changed it to As Desc Mean?

TIA

 

[Remou]

Paste this into a query (SQL view), and see how it suits:

SELECT CT.ID, Concatenate("SELECT [Desc] FROM CT  WHERE ID =" & [ID]) AS Message
FROM CT
GROUP BY CT.ID, Concatenate("SELECT [Desc] FROM CT  WHERE ID =" & [ID])

Change CT to the name of your table.

Both Num and Desc are reserved words, so they should be bracketed, or better yet, not used.

 

[hext2003]

Thank You! I'm Almost there.

Here is the Code I have...

SELECT csm_legal.LEGL_PROP, Concatenate("Select [LEGL_DATA] FROM csm_legal WHERE LEGL_PROP ="""& [LEGL_PROP]&"""") AS message
FROM csm_legal
GROUP BY csm_legal.LEGL_PROP, Concatenate("Select [LEGL_DATA] FROM csm_legal where LEGL_PROP =""" & [LEGL_PROP]&"""");

I ran this Query and it did what I wanted but... it took literally 10 mins to run. OUCH. That is to long.



OK, Here is the code I have

Private Sub CSM_LEGAL_UPDATE_Click()

'Show the hourglass so you know access is thinking
DoCmd.Hourglass True

'Transfer From Excel
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "csm_legal", "g:\Tracker\ProgramFiles\csm_legal.xls", True

'Make copy of the csm_legal file structure only
'The last argument "True" to copy only the structure.
DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb().Name, acTable, "csm_legal", "csm_legal_temp", True

' Set the Tax Key field in the temp file as a primary Key
Dim SQL1 As String
SQL1 = "CREATE INDEX PrimaryKey ON csm_legal_temp(LEGL_PROP) WITH PRIMARY"
DoCmd.RunSQL SQL1

SQL1 = "INSERT INTO csm_legal_temp SELECT csm_legal.* FROM csm_legal; "

DoCmd.RunSQL SQL1

'QUERY RUN HERE


DoCmd.Hourglass False
End Function

????????????
How do I get the first code (the query code) to run at the point where I have QUERY RUN HERE.

I have tried setting SQL1 = but I get all kinds of errors. I have put quotes here there and every where.

Also... The ID field name is LEGL_PROP and is a text field

TIA

 

[hext2003]

Ooops Sorry

Take out the second SQL1

SQL1 = "INSERT INTO csm_legal_temp SELECT csm_legal.* FROM csm_legal; "

DoCmd.RunSQL SQL1

That was one of my earlier attempts to do this.

 

[Remou]

Let me get this straight. You wish to insert data from an Excel sheet into a new table, concatenating the various comments into one line, is that correct?

 

[hext2003]

Yes

The Excel Spread Sheet is Created from another software program. Each night it runs in Batch to Create this Spreadsheet. I want To Click on an Update button - Have Access Grab that Spread sheet and concatenate those comments. There are approx 22,000 data rows in that spreadsheet, and the concatenation takes it down to about 15,000.

Is there an easier way, and I'm just being a putz?

 

[Remou]

I am not sure. You could add only the unique IDs to a table and then run and update to add the comments. Here are some notes.

INSERT INTO CT3 ( ID )
SELECT DISTINCT CT.ID
FROM CT

UPDATE CT3 INNER JOIN CT ON CT3.ID = CT.ID SET CT3.[DESC] = CT3.[desc] & CT.[desc] & Chr(13)+Chr(10);

The concatenate query in ann append query.

INSERT INTO CT2 ( ID, NUM, [DESC] )
SELECT q.ID, q.MinN, q.Message
FROM (SELECT CT.ID, Min(CT.Num) As MinN, Concatenate("SELECT [Desc] FROM CT  WHERE ID =" & [ID]) AS Message
FROM CT
GROUP BY CT.ID, Concatenate("SELECT [Desc] FROM CT  WHERE ID =" & [ID])) As q

 

[hext2003]

Do you know of someplace where I can find the correct Syntax to use Access's VB with SQL Code.

I have learned to use the

Dim SQL1 as String

SQL1 = "INSERT INTO csm_legal_temp SELECT csm_legal.* FROM csm_legal; "

DoCmd.RunSQL SQL1

but all of the " get confusing to me. I can follow the code you posted above but I don't know how to get that into my VB module and get it to run.

 

[Remou]

There are not too many rules. For the most part, use single quotes (') in place of double quotes (") in the string, alternatively, replace each double quote with two double quotes:
WHERE LEGL_PROP =""" & [LEGL_PROP] & """") AS message

or (easier to read):
WHERE LEGL_PROP ='" & [LEGL_PROP] & "'") AS message

Here are some more notes:

http://www.tek-tips.com/viewthread.cfm?qid=1493117

So:

strSQL="INSERT INTO CT2 ( ID, NUM, [DESC] ) " _
& "SELECT q.ID, q.MinN, q.Message " _
& "FROM (SELECT CT.ID, Min(CT.Num) As MinN, " _
& "Concatenate('SELECT [Desc] FROM CT  " _
& "WHERE ID =' & [ID]) AS Message " _
& "FROM CT " _
& "GROUP BY CT.ID, " _
& "Concatenate('SELECT [Desc] FROM CT  " _
& "WHERE ID =' & [ID])) As q"

I did not test the above, but I think it is ok.

 

[PHV]

it took literally 10 mins to run
And the following ?

SELECT LEGL_PROP, First(Concatenate("SELECT LEGL_DATA FROM csm_legal WHERE LEGL_PROP ='"& [LEGL_PROP] & "'")) AS message
FROM csm_legal
GROUP BY LEGL_PROP

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hext2003]

Here is what I have

SQL1 = "INSERT INTO csm_legal (legl_prop, legl_recno, legl_data ) " _
& "SELECT q.legl_prop, q.MinN, q.Message " _
& "FROM (SELECT csm_legal.legl_prop, Min(csm_legal.legl_recno) As MinN, " _
& "Concatenate('SELECT legl_data FROM csm_legal " _
& "WHERE legl_prop =' & [legl_prop] & '') AS Message " _
& "FROM csm_legal " _
& "GROUP BY csm_legal.legl_prop, " _
& "Concatenate('SELECT legl_data FROM csm_legal " _
& "WHERE legl_prop =' & [legl_prop] & '')) As q"

DoCmd.RunSQL SQL1


With the Step Through I get past the DoCmd.RunSQL line. It jumps over to the Concatenate function then stops at this line..

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

With and error of

Run Time Error (long Number)

Syntax error (missing operator) in query expression "legl_prop = 1010 001'.

i have a feeling it's that quote thing. legl_prop is a text field. So in my where statements I bet I need more quotes???

 

[dhookom]

Try:

SQL1 = "INSERT INTO csm_legal (legl_prop, legl_recno, legl_data ) " _
& "SELECT q.legl_prop, q.MinN, q.Message " _
& "FROM (SELECT csm_legal.legl_prop, Min(csm_legal.legl_recno) As MinN, " _
& "Concatenate('SELECT legl_data FROM csm_legal  " _
& "WHERE legl_prop ='" & [legl_prop] & "') AS Message " _
& "FROM csm_legal " _
& "GROUP BY csm_legal.legl_prop, " _
& "Concatenate('SELECT legl_data FROM csm_legal  " _
& "WHERE legl_prop ='" & [legl_prop] & "')) As q"
    
        DoCmd.RunSQL SQL1

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

I'd try this:

 SQL1 = "INSERT INTO csm_legal (legl_prop, legl_recno, legl_data) " _
& "SELECT legl_prop, Min(legl_recno) As MinN, " _
& "First(Concatenate(""SELECT legl_data FROM csm_legal " _
& "WHERE legl_prop='"" & [legl_prop] & ""'"")) AS Message " _
& "FROM csm_legal " _
& "GROUP BY legl_prop "

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hext2003]

Dhookom: I tried your code, but I kept getting an error
Field "|" does not exist. now sure what that is all about. I'm sure I messed something up


PHV: I used your code. I took out the First( ) part before the concatenate and it works ok. Still a bit slow (5 to 6 mins) but works. What does that First( ) do?? When I used the code exactly the way you had it I let the computer run a good 15 mins before I did a cntl Break to stop it. The records are in order in the spread sheet (I sort first on LEGL_PROP then LEGL_RECNO - so maybe (in the future) I can work on a way to speed this up further.

REMOU: Thank you! I have learned a lot in this thread

Thank you ALL!

 

[dhookom]

I have worked with another Tek-Tips user to speed up the code. We created a global variable for the database to use when creating the recordset. This cut out the time of creating and destroying the database. If you are using ADO, you could probably re-use the recordset rather than creating and setting to nothing.

It is very important that legl_prop is indexed.

Duane
Hook'D on Access
MS Access MVP