Solved

Concatenation

Posted on 2013-01-17
4
216 Views
Last Modified: 2013-01-21
I had been using Crosstab queries, but my customer wants me to combine all the related values of a particular record into one field. This has to do with surgeries where you can have multiple components and other items per surgery. What they want is to have all the components from a surgery concatenated into one field instead of using a cross tab where it has one column for each possible component.

Is this possible?
0
Comment
Question by:rawilken
[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
4 Comments
 
LVL 8

Expert Comment

by:H
ID: 38789460
I'm not sure I follow. Could you show a before and after example possibly?
0
 
LVL 26

Accepted Solution

by:
jerryb30 earned 100 total points
ID: 38789725
If the surgery has a unique identifier:

Create a module with this function:

Function CatComponent(vSurgeryID as string) as string
dim strTemp as string
dim rs as dao.recordset
dim strSQL as string
strSQL = "SurgeryComponent from SurgeryTable where surgeryID = '" & vSurgeryComponent & "'"
set rs = currentdb.openrecordset (strSQL)
rs.movefirst
do while not(rs.eof)
if strTemp = "" then
strTemp = rs!SurgeryComponent
else
strTemp & vbCRLF & rs!SurgeryComponent
endif
rs.movenext
loop
CatComponent = strTemp
end function

Then, call by query

Select SurgeryID, CatComponent(SurgeryComponent) from SurgeryTable
Group by SurgeryID, CatComponent(SurgeryComponent)
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38789733
If this is unclear, please post a sample db with dummy data (no personal identification data), with records of one surgery and multiple components.
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 38792059
Create a query and create 1 column that concatenates the fields you wish to combine, plus all the data requred for your crosstab.

Use this query as your crosstab source.

Combined_Field: Field1 & " " & Field2 & " " .........

Scott C
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

617 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