Solved

Concatenation

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

840 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