Improve company productivity with a Business Account.Sign Up

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

Access Query- Transpose

Hi All,

I have a table that has data residing in columnar format.  i.e, we have an individual in one row, and their dependents in subsequent rows below it; however, there is a key field that links the dependents to the parent.  I need to change the layout of this table to list the dependents in the same row as the parent.  Is there an effective way that I can transpose the table from being vertical with the parent and dependents to being horizontal?
0
Anthony6890
Asked:
Anthony6890
  • 10
  • 7
  • 2
1 Solution
 
aikimarkCommented:
Do you need separate columns or a delimited string?  If delimited string, use the DConcat code described in this article:
https://www.experts-exchange.com/articles/2380/Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

If separate columns, you will need to post some details about the table and sample data.
0
 
Rey Obrero (Capricorn1)Commented:
try creating a crosstab query using the wizard
create > query wizard > Crosstab query wizard
0
 
Anthony6890Author Commented:
Hi Aikimark,

I need separate columns.  The end result will be an exported fixed width text file, that I will specify the column starting position and the width.

For example...

Original Data...
ParentID     DependentID      Last Name     First Name
1                                                 Smith              John
1                   2                            Smith              Jane
1                   3                            Smith              Joan

How I need it to be...
ParentID     Last Name     First Name    DependentID      Last Name           First Name  
1                   Smith              John               2                            Smith                    Jane
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Anthony6890Author Commented:
Hi Rey,

The crosstab doesn't allow for multiple column headings, so that doesn't work for me...
0
 
aikimarkCommented:
I'm not seeing Joan Smith from your posted example.

Also, a single set of rows isn't enough.  How about 3-5 sets of rows.
0
 
Rey Obrero (Capricorn1)Commented:
<The crosstab doesn't allow for multiple column headings, so that doesn't work for me... >

this header is also not permitted, field names repeated?
ParentID     Last Name     First Name    DependentID      Last Name           First Name
0
 
Anthony6890Author Commented:
Aikimark, the post doesn't allow me to go further to the right, which is why you don't see Joan Smith.  To the right of DependentID, Last Name, and First Name, there would be DependentID_2, Last_Name2, First_Name2.  That is where Joan would be.

Rey, I do have the same header columns, so I'm guessing, I first need to split out the dependents; however, I was considering doing this manually and assigning a value to the dependent.  Each set of dependents would have the same value (i.e. 1-10).  If would first run a query for anyone identified with a 1, then update the one field, then 2, and so on.  It would just be many queries to run at one time...
0
 
aikimarkCommented:
Put data and code examples in a CODE snippet
0
 
Anthony6890Author Commented:
Ah Ok, her goes...

ParentID	Last Name	First Name	Depdent ID	Last Name1	First Name1	Dependent ID2	Last Name 2	First Name 2	Dependent ID3	Last Name 3	First Name 3
1       	Smith   	John    	2       	Smith     	Jane    	3           	Smith   	Joan
11      	Jones   	Wilma   	4       	Jones   	Mary    	7           	Jones   	Ken         	13          	Jones   	Jeff
87      	Nash    	Steven  	44      	Nash    	Kyle

Open in new window

0
 
Anthony6890Author Commented:
Thanks.

So one way I got this to work, was by assigning a sequential numerical value to each record.  I found a module that for each Parent ID entry, the record will get a sequential numerical value.  For a parent, the returned value is 1.  For all other dependents, the value can be 2- (any maximum value).  I then run a query for each of these numbers and update the results for the main parent record with the column headings for dependent 1.  Then I execute another query that updates the results for dependent 2 and so forth.  I will have to run at least 10-11 of these queries; however, they don't take long to execute (Under 5 seconds)

This might be the best way of executing this issue...

-Anthony
0
 
aikimarkCommented:
So, the original sample data would have looked something like this?
ParentID	DependentID	Last Name	First Name
1       	        	Smith   	John
1       	2        	Smith   	Jane
1       	3       	Smith   	Joan
11      	        	Jones   	Wilma
11      	4       	Jones   	Mary
11      	7       	Jones   	Ken
11      	13      	Jones   	Jeff
87      	        	Nash    	Steven
87      	44      	Nash    	Kyle

Open in new window

0
 
Anthony6890Author Commented:
Yeah, that's correct.
0
 
aikimarkCommented:
You may only need to run one query, looking for the number of DependentID values < current value within the ParentID group.
0
 
Anthony6890Author Commented:
Ok, let me see if I can run one.
0
 
aikimarkCommented:
For example, this query
SELECT Q_28979990.ParentID, Q_28979990.DependentID, 
dcount("DependentID","Q_28979990", "ParentID=" & Q_28979990.ParentID & " And DependentID<" & DependentID) As OrdPosn
FROM Q_28979990
Where DependentID Is not null 

Open in new window

Produces the following results:
ParentID	DependentID	OrdPosn
1      	2              	0
1      	3              	1
11     	4              	0
11    	7              	1
11    	13             	2
87    	44            	0

Open in new window

0
 
Anthony6890Author Commented:
With your result, how would I then run one query to append the results to the parent's record going horizontally?
0
 
aikimarkCommented:
If you changed the DependentID value to the count value, you might be able to do a crosstab query on the result.  Otherwise, you would need to create either create your destination table with column names starting with zero or add 1 to the DCount() value.
0
 
Anthony6890Author Commented:
I ended up using this module...

Public Function increment(ivalue As String) As Long

If Nz(SSNum, "zzzzzzzzz") = ivalue Then
        GBL_Icount = GBL_Icount + 1
Else
        SSNum = ivalue
        GBL_Icount = 1
End If
increment = GBL_Icount
End Function

Open in new window


This created a specific number for each parent/dependent SSN combo and then resets the counter when it hits a new parent SSN.  I then have a query do an update to the main table that already has the parent's SSN's listed.  I run that same query a spcific number of times, changing the criteria field to just equal a new dependent number, i.e. 2 or 3.  This seemed to work the easiest and provided me with the results I needed.

-Anthony
0
 
Anthony6890Author Commented:
This is what allowed me to do what I needed.  The other solutions, were good; however, they didn't provide me with the kind of result set I needed to display.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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