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

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

Concatenation

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
rawilken
Asked:
rawilken
  • 2
1 Solution
 
HCommented:
I'm not sure I follow. Could you show a before and after example possibly?
0
 
jerryb30Commented:
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
 
jerryb30Commented:
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
 
clarkscottCommented:
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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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