Solved

Access Query- Transpose

Posted on 2016-10-31
20
48 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 
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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

740 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