Solved

Access Query- Transpose

Posted on 2016-10-31
20
28 Views
Last Modified: 2016-11-08
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
Comment
Question by:Anthony6890
  • 10
  • 7
  • 2
20 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 41866977
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41866979
try creating a crosstab query using the wizard
create > query wizard > Crosstab query wizard
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 41867022
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
 
LVL 1

Author Comment

by:Anthony6890
ID: 41867042
Hi Rey,

The crosstab doesn't allow for multiple column headings, so that doesn't work for me...
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41867203
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41867214
<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
 
LVL 1

Author Comment

by:Anthony6890
ID: 41867256
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
 
LVL 45

Expert Comment

by:aikimark
ID: 41867258
Put data and code examples in a CODE snippet
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 41867264
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:Anthony6890
ID: 41867384
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
 
LVL 45

Expert Comment

by:aikimark
ID: 41867396
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
 
LVL 1

Author Comment

by:Anthony6890
ID: 41867399
Yeah, that's correct.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41867405
You may only need to run one query, looking for the number of DependentID values < current value within the ParentID group.
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 41867409
Ok, let me see if I can run one.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41867428
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
 
LVL 1

Author Comment

by:Anthony6890
ID: 41867443
With your result, how would I then run one query to append the results to the parent's record going horizontally?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41867626
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
 
LVL 1

Accepted Solution

by:
Anthony6890 earned 0 total points
ID: 41872716
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
 
LVL 1

Author Closing Comment

by:Anthony6890
ID: 41878479
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now