Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1212
  • Last Modified:

Concatenate records with same data across two fields

Hi
I am using MS ACCESS 2003 on XP Pro and was hoping you could help me.

I have a query that displays duplicate records based on two fields. I would like concatenate all dupicate data into one record. For example (field names first row):
F1, F2, F3
A, 2, Church
A, 2, Library
B, 1, Station
B, 1, <Null>
B, 1, Post Office
C, 5, Hall

I would like to concatenate as follows:

F1, F2, F3
A, 2, Church Library
B, 1, Station <Null> Post Office
C, 5, Hall

Ideally I would like the solution in SQL but if its best to use vba please supply basis if you can.

Thanks
0
pxj05
Asked:
pxj05
1 Solution
 
rockiroadsCommented:
You could try this with a vba func
eg

public function GetF3(byval F1 as String, byval F2 as integer)

    dim rs as dao.recordset
    dim sF3 as string

    set rs=currentdb.openrecordset("SELECT F3 FROM mytable WHERE F1 = '" & f1 & "' and f2 = " & f2)
    do while rs.eof = false
        sF3 = sF3 & " " & rs!F3
        rs.movenext
    loop
    rs.close
    set rs=nothing

    GetF3 = trim$(sF3)
end function


save it then in a query do this

select f1, f2, GetF3(f1, f2) as F3Cat
from mytable
group by f1, f2
0
 
pxj05Author Commented:
x e lent - I can now use this as a basis. thank you very much
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now