transpose multiple record values into a single record

[Basil via AccessMonster.com]

My current query results contain the following fields: PatientID, Claimnum,
Activitynum, Diag1, Diag2, Diag3, Diag4, Proc. Each patient can have
multiple claims. Each claim can have multiple activities. Each activity
will have Diags 1-4 and a Proc.

I currently have one record per patient/claim/activity. I need a way to
transpose the data so the result set contains only one record per
patient/claim (i.e. for each patient/claim, I will have a string of multiple
Diags and Procs contained in all related activities.) Is there a way to do
this in a query?

 

[Guest]

Why would you need to?

 

[kingston via AccessMonster.com]

You can do this with the help of a temporary table. Use a unique composite
index PatientID/Claimnum. Populate all of the PatientID and Claimnum data
via an append query. Then create an update query that adds Diag1, Diag2...
to the existing field. In other words, the update command would look like
[Field] & ", " & [NewData].

 

[Duane Hookom]

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Basil via AccessMonster.com]

Thanks. Could you provide a little more information on this solution so I
can research how to set up the code? Is there a site you would recommend
where I can research how to do some of these things (i.e. unique composite
index, append query)?

You can do this with the help of a temporary table. Use a unique composite
index PatientID/Claimnum. Populate all of the PatientID and Claimnum data
via an append query. Then create an update query that adds Diag1, Diag2...
to the existing field. In other words, the update command would look like
[Field] & ", " & [NewData].

My current query results contain the following fields: PatientID, Claimnum,
Activitynum, Diag1, Diag2, Diag3, Diag4, Proc. Each patient can have

[quoted text clipped - 6 lines]

Diags and Procs contained in all related activities.) Is there a way to do
this in a query?

 

[Basil via AccessMonster.com]

state government regulations requesting it that way

Why would you need to?

My current query results contain the following fields: PatientID, Claimnum,
Activitynum, Diag1, Diag2, Diag3, Diag4, Proc. Each patient can have

[quoted text clipped - 6 lines]

Diags and Procs contained in all related activities.) Is there a way to do
this in a query?

 

[Basil via AccessMonster.com]

This looks close, however, the values are combined into a single field.

Is there a way to use similar logic but dump each value into a separate field
- using your example the results might contain FamID, famLastName, FirstName1,
FirstName2, FirstName3, etc. in a single record for all matches found.

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

My current query results contain the following fields: PatientID,
Claimnum,

[quoted text clipped - 8 lines]

Diags and Procs contained in all related activities.) Is there a way to do
this in a query?

 

[kingston via AccessMonster.com]

A unique composite index is just a unique index made up of more than one
field. Create a new table with the following fields based on your existing
data structure:
[PatientID]
[Claimnum]
[AllResults] - Memo

Save the table and create an index in design mode (button at the top with a
lightning bolt) called PatClaim.
Select the two fields [PatientID] and [Claimnum] and change the Unique
property to Yes. You can probably delete any indexes that Access
automatically made. This is your temporary table.

Create a query to append [PatientID] and [Claimnum] and execute it. Do this
by making a select query (for [PatientID] and [Claimnum]) and changing the
query type to Append (append to your temporary table). You can save this
query so that you can call it in code using DoCmd.OpenQuery or use an SQL
string to duplicate its function. Because of the unique property of the
index, you will never have the same [PatientID] and [Claimnum] combination
more than once.

Next, create an update query. Create a select query and change the query
type to Update. Another row will appear in the results pane called Update To.
Add your temporary table and your data source. Join [PatientID] and
[Claimnum] and update the [AllResults] field as such:

[AllResults] = [AllResults] & IIF(IsNull([AllResults]),"",", ") &
[FieldFromSource]

When you run this query, it will process all of the records in your data
souce one by one and add them to the [AllResults] field. The only drawback
to this is that a temporary table is created and must be erased every time.
However, your task is accomplished in essentially three queries and the
temporary table:

1) Delete query - remove all records from temporary table.
2) Append query - add all [PatientID] and [Claimnum] data.
3) Update query - concatenate all data into an existing field.

There are lots of places to learn about database design, indexes, queries,
etc. I'm sorry but I don't know any of them off the top of my head. I'm
sure a lot of other people in these forums can point you in the right
direction. HTH.

Thanks. Could you provide a little more information on this solution so I
can research how to set up the code? Is there a site you would recommend
where I can research how to do some of these things (i.e. unique composite
index, append query)?

You can do this with the help of a temporary table. Use a unique composite
index PatientID/Claimnum. Populate all of the PatientID and Claimnum data

[quoted text clipped - 7 lines]

 

[Basil via AccessMonster.com]

thank you for the detail - I'll try this.

A unique composite index is just a unique index made up of more than one
field. Create a new table with the following fields based on your existing
data structure:
[PatientID]
[Claimnum]
[AllResults] - Memo

Save the table and create an index in design mode (button at the top with a
lightning bolt) called PatClaim.
Select the two fields [PatientID] and [Claimnum] and change the Unique
property to Yes. You can probably delete any indexes that Access
automatically made. This is your temporary table.

Create a query to append [PatientID] and [Claimnum] and execute it. Do this
by making a select query (for [PatientID] and [Claimnum]) and changing the
query type to Append (append to your temporary table). You can save this
query so that you can call it in code using DoCmd.OpenQuery or use an SQL
string to duplicate its function. Because of the unique property of the
index, you will never have the same [PatientID] and [Claimnum] combination
more than once.

Next, create an update query. Create a select query and change the query
type to Update. Another row will appear in the results pane called Update To.
Add your temporary table and your data source. Join [PatientID] and
[Claimnum] and update the [AllResults] field as such:

[AllResults] = [AllResults] & IIF(IsNull([AllResults]),"",", ") &
[FieldFromSource]

When you run this query, it will process all of the records in your data
souce one by one and add them to the [AllResults] field. The only drawback
to this is that a temporary table is created and must be erased every time.
However, your task is accomplished in essentially three queries and the
temporary table:

1) Delete query - remove all records from temporary table.
2) Append query - add all [PatientID] and [Claimnum] data.
3) Update query - concatenate all data into an existing field.

There are lots of places to learn about database design, indexes, queries,
etc. I'm sorry but I don't know any of them off the top of my head. I'm
sure a lot of other people in these forums can point you in the right
direction. HTH.

Thanks. Could you provide a little more information on this solution so I
can research how to set up the code? Is there a site you would recommend

[quoted text clipped - 6 lines]

 

[Duane Hookom]

You would need to create a ranking field from your data to build the Column
Heading expression. If other solutions don't work then come back.

--
Duane Hookom
MS Access MVP

This looks close, however, the values are combined into a single field.

Is there a way to use similar logic but dump each value into a separate
field
- using your example the results might contain FamID, famLastName,
FirstName1,
FirstName2, FirstName3, etc. in a single record for all matches found.

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

My current query results contain the following fields: PatientID,
Claimnum,

[quoted text clipped - 8 lines]

Diags and Procs contained in all related activities.) Is there a way to
do
this in a query?

 

[Basil via AccessMonster.com]

I am almost there, just stuck on the Update Query Expression for the
"AllResults" field ( I have 4 different fields that would probably be
considered the "Field From Source", and I'm not sure what the expression
should look like. Here is what I have so far:

[DiagsAll]=[DiagsAll] & IIf(IsNull([DiagsAll]),"",", ") & [Diag1] & [Diag2] &
[Diag3] & [Diag4]

A unique composite index is just a unique index made up of more than one
field. Create a new table with the following fields based on your existing
data structure:
[PatientID]
[Claimnum]
[AllResults] - Memo

Save the table and create an index in design mode (button at the top with a
lightning bolt) called PatClaim.
Select the two fields [PatientID] and [Claimnum] and change the Unique
property to Yes. You can probably delete any indexes that Access
automatically made. This is your temporary table.

Create a query to append [PatientID] and [Claimnum] and execute it. Do this
by making a select query (for [PatientID] and [Claimnum]) and changing the
query type to Append (append to your temporary table). You can save this
query so that you can call it in code using DoCmd.OpenQuery or use an SQL
string to duplicate its function. Because of the unique property of the
index, you will never have the same [PatientID] and [Claimnum] combination
more than once.

Next, create an update query. Create a select query and change the query
type to Update. Another row will appear in the results pane called Update To.
Add your temporary table and your data source. Join [PatientID] and
[Claimnum] and update the [AllResults] field as such:

[AllResults] = [AllResults] & IIF(IsNull([AllResults]),"",", ") &
[FieldFromSource]

When you run this query, it will process all of the records in your data
souce one by one and add them to the [AllResults] field. The only drawback
to this is that a temporary table is created and must be erased every time.
However, your task is accomplished in essentially three queries and the
temporary table:

1) Delete query - remove all records from temporary table.
2) Append query - add all [PatientID] and [Claimnum] data.
3) Update query - concatenate all data into an existing field.

There are lots of places to learn about database design, indexes, queries,
etc. I'm sorry but I don't know any of them off the top of my head. I'm
sure a lot of other people in these forums can point you in the right
direction. HTH.

Thanks. Could you provide a little more information on this solution so I
can research how to set up the code? Is there a site you would recommend

[quoted text clipped - 6 lines]

 

[kingston via AccessMonster.com]

That looks good. Can you tell me what isn't working? You can add a
delimiter between each Diag value too. However, it may be perfectly fine for
those four fields to be concatenated as you've shown.

You can use a convention where each record is separated by a "; " instead of
a ", ". Then each field within the record would be separated by a ", "
instead of nothing. You may not want to use the IIF statement at all if you
want to show empty records:

[DiagsAll]=[DiagsAll] & "; " & [Diag1] & ", " & [Diag2] & ", " & [Diag3] & ",
" & [Diag4]

Also, don't forget to make the field type Memo, not text, because this can
add up quickly.

I am almost there, just stuck on the Update Query Expression for the
"AllResults" field ( I have 4 different fields that would probably be
considered the "Field From Source", and I'm not sure what the expression
should look like. Here is what I have so far:

[DiagsAll]=[DiagsAll] & IIf(IsNull([DiagsAll]),"",", ") & [Diag1] & [Diag2] &
[Diag3] & [Diag4]

A unique composite index is just a unique index made up of more than one
field. Create a new table with the following fields based on your existing

[quoted text clipped - 45 lines]

 

[Basil via AccessMonster.com]

Thank you again for your continued help!

My update query code is working now, and I have a single field that holds all
the results for proc (I actually needed to concat the procs, which were
originally in a single field, instead of the diags). The code looks like
this:

UPDATE Temp_Transposed INNER JOIN Sil ON (Temp_Transposed.ClaimNUM=Sil.
CLAIMNUM) AND (Temp_Transposed.MemberID=Sil.MemberID) SET Temp_Transposed.
ProcsAll = [ProcsAll] & IIF(IsNull([Proc]),"",", ") & [Proc];

The last thing I need to do is separate each proc code from ProcsAll into a
unique field up to the first 5 procs (i.e. I would end up with 5 new fields
entitled Proc1, Proc2, Proc3, Proc4, Proc5 for each Member/Claim record). Is
there a way to do this?

That looks good. Can you tell me what isn't working? You can add a
delimiter between each Diag value too. However, it may be perfectly fine for
those four fields to be concatenated as you've shown.

You can use a convention where each record is separated by a "; " instead of
a ", ". Then each field within the record would be separated by a ", "
instead of nothing. You may not want to use the IIF statement at all if you
want to show empty records:

[DiagsAll]=[DiagsAll] & "; " & [Diag1] & ", " & [Diag2] & ", " & [Diag3] & ",
" & [Diag4]

Also, don't forget to make the field type Memo, not text, because this can
add up quickly.

I am almost there, just stuck on the Update Query Expression for the
"AllResults" field ( I have 4 different fields that would probably be

[quoted text clipped - 9 lines]

 

[kingston via AccessMonster.com]

Is there any way to distinguish between Proc1, Proc2, etc. in the original
data? Is there any consistency in the format of the field (e.g. it's always
a 6 character code). If there isn't and the new fields must match ProcAll,
then you may need to choose a less common delimiter and parse ProcAll into
separate pieces using that delimeter:

Unless the original field proc never contains a comma, choose a delimiter
that will not appear in the data itself (e.g. " | " or " :: "). Then, you
can parse ProcAll with relative confidence that you won't be cutting up data.
Proc1 = Left(ProcAll, 1, Instr(ProcAll, " | ")-1)
Proc2 = Mid(ProcAll, Instr(ProcAll, " | ")+3, Instr(Instr(ProcAll, " | ")+3,
ProcAll," | ")-(Instr(ProcAll, " | ")+3))
...

This allows you to parse the data in one query. However, you might run into
problems if there is no data and you may want more control. So another
approach would be to use a function:

Proc1 = Left(ProcAll, 1, Instr(ProcAll, " | ")-1)
String = Mid(ProcAll, Instr(ProcAll, " | ")+3)

Proc2 = Left(String, 1, Instr(String, " | ")-1)
String = Mid(String, Instr(String, " | ")+3)
...

Thank you again for your continued help!

My update query code is working now, and I have a single field that holds all
the results for proc (I actually needed to concat the procs, which were
originally in a single field, instead of the diags). The code looks like
this:

UPDATE Temp_Transposed INNER JOIN Sil ON (Temp_Transposed.ClaimNUM=Sil.
CLAIMNUM) AND (Temp_Transposed.MemberID=Sil.MemberID) SET Temp_Transposed.
ProcsAll = [ProcsAll] & IIF(IsNull([Proc]),"",", ") & [Proc];

The last thing I need to do is separate each proc code from ProcsAll into a
unique field up to the first 5 procs (i.e. I would end up with 5 new fields
entitled Proc1, Proc2, Proc3, Proc4, Proc5 for each Member/Claim record). Is
there a way to do this?

That looks good. Can you tell me what isn't working? You can add a
delimiter between each Diag value too. However, it may be perfectly fine for

[quoted text clipped - 16 lines]

 

[Basil via AccessMonster.com]

Yea, that did it! I had to create an IIF statement to make sure there was a
"," before using the function(s), else I just set the proc equal to the
ProcAll. I now have one record for each member/claim with all procs
separated out into different fields within the same record (I only kept the
first 10).

If anyone is trying to transpose multiple record values (info vertical) into
a single record listing each value in a single record (info horizontally),
this thread should help you accomplish the task.

kingston, you're wonderful to help me work through this, thanks again!

Is there any way to distinguish between Proc1, Proc2, etc. in the original
data? Is there any consistency in the format of the field (e.g. it's always
a 6 character code). If there isn't and the new fields must match ProcAll,
then you may need to choose a less common delimiter and parse ProcAll into
separate pieces using that delimeter:

Unless the original field proc never contains a comma, choose a delimiter
that will not appear in the data itself (e.g. " | " or " :: "). Then, you
can parse ProcAll with relative confidence that you won't be cutting up data.
Proc1 = Left(ProcAll, 1, Instr(ProcAll, " | ")-1)
Proc2 = Mid(ProcAll, Instr(ProcAll, " | ")+3, Instr(Instr(ProcAll, " | ")+3,
ProcAll," | ")-(Instr(ProcAll, " | ")+3))
...

This allows you to parse the data in one query. However, you might run into
problems if there is no data and you may want more control. So another
approach would be to use a function:

Proc1 = Left(ProcAll, 1, Instr(ProcAll, " | ")-1)
String = Mid(ProcAll, Instr(ProcAll, " | ")+3)

Proc2 = Left(String, 1, Instr(String, " | ")-1)
String = Mid(String, Instr(String, " | ")+3)
...

Thank you again for your continued help!

[quoted text clipped - 18 lines]