Hi Luke,
There probably is a simpler way but here's a way using VBA
Dim sSQLString As String
' SQL string to create temporary table Grouped By Field1 &
Field2
sSQLString = "SELECT field1, field2 " & _
"FROM Table4 " & _
"GROUP BY field1, field2 " & _
"ORDER BY field1, field2;"
Dim sSubRecordset, sFieldcombined As String
Dim rst, rst1 As DAO.Recordset
' recordset on above SQL statement Grouping By Field1 &
Field2
Set rst = CurrentDb.OpenRecordset(sSQLString)
' Loop thru above recordset until the End Of File
Do Until rst.EOF
' SQL Syntax to create subtable to loop thru
sSubRecordset = "Select * From Table4 WHERE Field1 =
'" & rst(0) & "' AND Field2 = '" & rst(1) & "'"
Set rst1 = CurrentDb.OpenRecordset(sSubRecordset)
' Loop thru subtable
Do Until rst1.EOF
' combine the fields
sFieldcombined = sFieldcombined &
rst1("field3") & ","
rst1.MoveNext
Loop
' Insert record into temporary table "Table5"
CurrentDb.Execute "INSERT INTO Table5 (field1, field2,
fieldCombined )" & _
"SELECT '" & rst(0) & "', '" & rst(1) & "', '" & Left(sFieldcombined,
Len(sFieldcombined) - 1) & "';"
' clear out the combined field and the subTable recordset
object
Set rst1 = Nothing
sFieldcombined = ""
rst.MoveNext
Loop
' End of Code
I hope this helps! If you have additional questions on this topic, please
respond back to this posting.
Regards,
Eric Butts
Microsoft Access Support
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<
http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <
http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."
--------------------
| Content-Class: urn:content-classes:message
| From: "Luke" <
[email protected]>
| Sender: "Luke" <
[email protected]>
| Subject: Consolidate 3 values into 1
| Date: Mon, 2 Feb 2004 10:13:50 -0800
| Lines: 23
| Message-ID: <
[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcPpuE8XtddzHqw8Rr63/0Pi3pbBTA==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.access.modulesdaovba
| Path: cpmsftngxa07.phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.modulesdaovba:123267
| NNTP-Posting-Host: tk2msftngxa09.phx.gbl 10.40.1.161
| X-Tomcat-NG: microsoft.public.access.modulesdaovba
|
| I need to consolidate values from a table containing 3
| fields (columns; the first 2 columns contain the same
| information, 3rd column is unique) into 1 field
| (column). There are multiple entries (rows), anywhere
| from 2 to 188.
|
| Following is an example what I need to do:
|
| Current set:
| Field1 Field2 Field3
| City Street Main
| City Street Belt
| City Street East
|
| Result I need to get:
| Field4
| City, Street, Main, Belt, East
|
| Any idea how to do this (VBA, query)? Your help or
| suggestions are greatly appreciated.
|
| Thank you.
|
|
