Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Concatenate records with same data across two fields

Posted on 2007-11-19
2
Medium Priority
?
1,210 Views
Last Modified: 2010-04-21
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
Comment
Question by:pxj05
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 20313359
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
 

Author Closing Comment

by:pxj05
ID: 31409957
x e lent - I can now use this as a basis. thank you very much
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question