Combine mutliple record into one record

[vierkarv]

Hi,
I have a table structured as follow:

[FONT=&quot]And I want them to be combined as follow:[/FONT]

[FONT=&quot]

[/FONT]

Is there a way to do this without using script?

What's a fast way to add a column in front of the table so that it would go through the table, and add a "Record" field?

[FONT=&quot]

[/FONT]

(In this example, each record will start with name field, and end with item field)


Any help or input will be much appreciated.

 

[statsman]

Try this:
If you are using the query wizard, open the query in design view and go to the first blank field on the right.

Enter the following:

George[name1]&[address1]&[item1])

Added:
I just noticed that my code creates a smiley face when posted. It should read:
George colon roundbracket. No spaces

 

[gblack]

Here's the answer, although it is much easier to do with "script":

First off, since you have not designated any field names in your tables, I am going to call them: [Type_Field], [Name_Field], [Address_Field], and [Item_Field]... respectively. I called the main table you posted "Table1"

The first thing we need to do is to make a New Table with a new "Record_Field" (i.e. the last question in your post).

How to create a new table with a "record_field":

First click on Design a new query.
Add your table to it (i.e. Table1).

In the query design mode where it says "field:" cut and paste this code (you will of course have to replace "Type_Field" with the actual name of your field. I suggest pasting this to notepad the doing a quick find and replace for "type_field", then pasting this code to the query design as previously stated):

Record_Field: IIf(Left([Type_Field],4)="Name","Record" & Mid([Type_Field],5,Len([Type_Field])-4),IIf(Left([Type_Field],4)="Addr","Record" & Mid([Type_Field],8,Len([Type_Field])-7),IIf(Left([Type_Field],4)="Item","Record" & Mid([Type_Field],5,Len([Type_Field])-4),"n/a")))

Now drag all your fields down from Table1 (i.e. Type_Field, Name_Field, Address_Field, and Item_Field) to the field section in the query design.

Change the Query to a "make table query" by clicking "make table query" in the "Query type" ribbon section (up top) (assuming you are using Access 2007 here). When you are prompted for a name enter whatever name you wish. I will "new_table" for the purposes of this illustration.

Then run the query (click the exclamation mark "!"), Access should prompt you to make a new table called "New_Table". Click OK and now you have a table with the new "Record_Field" you are looking for.


This next part is much more involved than it needs to be, but because you don't want to use "script"... here is the long way of getting the outcome you want:


Now that you have a record field, create one query for each Field from the "New_Table" you just created. Meaning: for each Field that you want to have in your combo_table... you will need a seperate query as follows:

Query1: Record_Field, Name_field
Query2: Record_Field, Address_field
Query3: Record_Field, Item_Field

Close and save each query.

Now open a new query design and pull all three of those queries, into it (i.e. This will be Query4). Then link all by these querys (Query1, Query2, and Query3) by "Record_field".

Now Pull Record_Field down once to the "Field:" section of the query design (It doesn't matter from which query you do this, any of the three should suffice) and then do the same for: [Name_field] from Query1, [Address_Field] from Query2, and [Item_Field] from Query3.

Change Query4's query type from a "select query" to a "make table query" (just the same as you did before to create the "new_table") and Call this table whatever you want (maybe "combo_table"?)... then run this query by hitting the exclamation mark (!). This should create the table you are looking for.

Hoepfully if my instructions weren't to tough to follow... This should give you exactly what you are looking for.

Good luck!
Gary

 

[vierkarv]

@statsman
Thanks for the reply
This would only create a column where the field name = the value of name1, address1 and item 1 combine. I needed something different


@Gary
Thanks for the detailed response
But I guess I might have oversimplified my table..

Record_Field: IIf(Left([Type_Field],4)="Name","Record" & Mid([Type_Field],5,Len([Type_Field])-4),IIf(Left([Type_Field],4)="Addr","Record" & Mid([Type_Field],8,Len([Type_Field])-7),IIf(Left([Type_Field],4)="Item","Record" & Mid([Type_Field],5,Len([Type_Field])-4),"n/a")))

^That's brilliant, I wouldn't have thought of it in such a way, however..

In my table, actually I won't have Name1, Address1, Item1, Name2, Address2, Item2, but rather, Name, Address, Item. (without the number)

I'm actually interested as do how you'd do this via scripting?

Thanks for the help!

 

[gblack]

If you can tell me how you know which "Names" "addresses" and "Items" are grouped together, then I can help you with this.

Of the type field is consistant... this will still work. In any event you have to have some way of denoting which fields are grouped... if not "Name1" "Address1" "Item1"... then what does it look like?

Or is it that every three records denotes a set?

 

[vierkarv]

If you can tell me how you know which "Names" "addresses" and "Items" are grouped together, then I can help you with this.

Of the type field is consistant... this will still work. In any event you have to have some way of denoting which fields are grouped... if not "Name1" "Address1" "Item1"... then what does it look like?

Or is it that every three records denotes a set?

Let's see..
They are somewhat like this:

Start
Name N1
Address A1
Item I1
End
Start
Name N2
Address A2
Item I2
End

Before the Name field, there'll be an empty record (Start) indicating it's an new set, and there's an empty record (End) indicating the ending of the set.

Would that help? (They will be identical to the each set though)

 

[gblack]

I am assuming you are using MS Access 2007.
Copy your table and call it “tblCombo”
Click on the “Create” tab in the ribbon. Choose the “other” section and the macro dropdown and click on module.
Click tools references. Scroll all the way down till you reach the Microsoft ActiveX data Objects and choose the latest version mine is ActiveX data Objects 6.0 library and ActiveX data Objects Recordset 6.0. You may only have up to 2.8 or even 2.7

Now take this code and paste it into the module click anywhere on the code and click run... Then check your tblCombo. Hopefully it will give you what you want... if all the field names match up correctly and your record order is consistent (meaning record 1 for each set of 5 is always blank/null, record 2 is always Name, three is always address, four is always Item and five is always blank and always ends the set).

It may take a little fiddling with and a couple tries, but it should work.

Public Function Fill_tbl_Combo()
Dim db As Database
Dim rs As ADODB.Recordset
Dim strSQL, strName, strAddress, strItem As String
Dim intCounter, intEndRecord As Integer

Set db = CurrentDb
Set rs = New ADODB.Recordset

strSQL = "SELECT * FROM tblCombo ORDER BY [Record_Order];"
'You will have to Change [Record_Order] with your Primary Key value
'(i.e. The field that keeps your blank records in the correct order)

With rs
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open (strSQL)
.MoveFirst
End With
intEndRecord = rs.RecordCount

intCounter = 0
Do While Not rs.EOF
intCounter = intCounter + 1
For i = 1 To 5
Select Case i
Case 2
rs!Type_Field = rs!Type_Field & intCounter 'NB. Type_Field will be changed to whatever field denotes Name, Address, and Item in your table
If Not IsNull(rs!name_Field) Then 'This assumes Name is always the Second record in the set of 5
strName = rs!name_Field
End If
Case 3
rs!Type_Field = rs!Type_Field & intCounter
If Not IsNull(rs!Address_Field) Then 'This assumes Address is always the Thrid record in the set of 5
strAddress = rs!Address_Field
End If
Case 4
rs!Type_Field = rs!Type_Field & intCounter
If Not IsNull(rs!Item_Field) Then 'This assumes Item is always the Fourth record in the set of 5
strItem = rs!Item_Field
End If
Case 5
rs!Type_Field = "Record" & intCounter 'This assumes a a null fifth record
If Not IsNull(strName) Then
rs!name_Field = strName
End If
If Not IsNull(strAddress) Then
rs!Address_Field = strAddress
End If
If Not IsNull(strItem) Then
rs!Item_Field = strItem
End If
End Select
rs.Update

If intCounter = intEndRecord Then
GoTo Exit_Function
End If
rs.MoveNext
Next

Loop
Exit_Function:
rs.Close
Set rs = Nothing

End Function

Sorry about the formatting... I don't know how to get this thing to format the indentions and such...

HTH
Gary