Yes you can do that, Paddon. But the thing that many people miss about doing this sort of thing is that it has to be done with recordsets via VBA code because the query wizard cannot manipulate data like that.
what you can do is loop your table, after sorting the records (if need be) and check the records via some condition and/or some conditional field after every record is processed to determine if it should be concatenated into a new record line (new LINE in the field, NOT record), as you've shown in your last table example. consider a table like this, called OLDTABLE:
Code:
CONTACT COMPANY notes
adam company a dummy field
bob company a dummy field
jay company b dummy field
ron company b dummy field
brett company c dummy field
If I want to produce an output like what you've shown you want, I would write this kind of procedure:
Code:
sub newoutput
On Error Resume Next
dim c as string
dim sContacts as string
dim sNotes as string
Dim tbl As DAO.TableDef
dim rs as dao.recordset
dim rs2 as dao.recordset
Dim f As field
Dim db As DAO.Database
Set db = CurrentDb
Set tbl = db.CreateTableDef("companyinfo")
With tbl
.Fields.Append .CreateField("company", dbtext, 255)
.Fields.Append .CreateField("contacts", dbmemo)
.Fields.Append .CreateField("contactnotes", dbmemo)
End With
db.TableDefs.Append tbl
set rs = db.openrecordset("companyinfo")
set rs2 = db.openrecordset("SELECT * FROM oldtable " & _
"ORDER BY company, contact")
with rs2
.movelast
.movefirst
c = !company
do until .eof
while !company = c and not .eof
sContacts = sContacts & !contact & vbcrlf
sNotes = sNotes & !contact & " - " & !notes & vbcrlf
.movenext
wend
rs.addnew
rs!company = c
rs!contacts = sContacts
rs!contactnotes = sNotes
rs.update
c = !company
loop
end with
rs.close
rs2.close
db.close
set rs = nothing
set rs2 = nothing
set db = nothing
set tbl = nothing
end sub
And after all of that, you would get a new table that looks like this (I used the table tags here. The records with blank "company" field would be new lines in the actual company record. so, 3 records total for this example output):
Code:
COMPANY CONTACTS NOTES
company a adam adam - dummy field
bob bob - dummy field
company b jay jay - dummy field
ron ron - dummy field
company c brett brett - dummy field
Does that make sense? You can do the same thing with your assets. I'm not sure there is any other easy way to do it, because Access does not compliment this kind of manipulation at all. I've already said that though.
Windows 7 
Access 2010 (version 14.0)
Reply With Quote
