Solved

Access Query- Transpose

Posted on 2016-10-31
20
31 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

910 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

22 Experts available now in Live!

Get 1:1 Help Now