Solved

Concatenation

Posted on 2013-01-17
4
215 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

752 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