Solved

Access Row Data to Columns

Posted on 2013-11-12
12
329 Views
Last Modified: 2014-01-29
Experts. I have a database (see attached example). In this database there is a query called qryAuditReport. This is used to generate a report with specific criteria.

Issue: The user wants to see only one line item for each property. Duplicates show due to multiple "Limited Partners" and "Lenders". If there are multiple Limited Partners and Lenders I would like to separate them out into additional columns versus displaying the duplicate rows. Below is an example of what I'd like to see.

Property   Audit_Firm   Ownership   Limited Partner_1   Limited Partner_2   Lender_1    Lender_2

Anyone have any idea how to achieve this?
AuditTaxTracking.accdb
0
Comment
Question by:acramer_dominium
12 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 39643293
Post your query. Not able to see your query in the attachment as it is prompting to select one of the reports.
0
 
LVL 24

Expert Comment

by:chaau
ID: 39643479
I have enhanced the query. Please have a look
AuditTaxTracking.accdb
0
 

Author Comment

by:acramer_dominium
ID: 39644965
chaau: This combines all the limited partners and lenders into one field separated by commas. I would like each one to go into it's own field. Is that possible?

Sharath_123: If you hold shift while you open the database you will open it in design view and see the query.
0
 
LVL 24

Expert Comment

by:chaau
ID: 39645788
You will have to build a transform query dynamically within the vba code and assign it to the query. Afaik, Access is not that good at dynamic queries.
Alternatively you can use OLE automation to populate the Excel file cell by cell.
BTW, I believe that my solution is good, because the fields are always at the same position. Putting the values at different columns will make the file unmanageable by the people who receives the file.
if you don't like commas, I can separate by other symbol, like semicolon, or line break
0
 

Author Comment

by:acramer_dominium
ID: 39652102
Anyone else have any suggestions on how to get data dynamically into two different columns? We need it separated because the report will be in excel and then the user will sort accordingly.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:acramer_dominium
ID: 39657874
I need multiple columns due to the fields having commas in the text strings themselves. The end user will never know one lender from the next, etc.

Do you have an example of how to dynamically make a transform query with this data? I'm looking and I'm finding examples but they all use the actual data as the column headings. I would want to use a generic "lender 1" "lender 2" as the headings and have the data fall under that.
0
 
LVL 24

Expert Comment

by:chaau
ID: 39658113
Sometimes users do not know what they really want. I suggest you talk to them and advise of the solution I have proposed.

Let's say we will come up with the solution to put the values in each separate column. This will meet the user requirements. However, the end product will become unmanageable. If the Excel file is used in later calculations by some other tools then the  tools will not be able to adjust very easy, as the number of columns will be different. In fact, what you are proposing will push other columns farther to the right after all lenders and limited partners.

With an option to have them all in one column (comma separated, or delimited by other symbols, like line feed) will ensure that the data model stays the same regardless of the number of these dynamic elements. If they want to see the individual entries, they can always do it using Excel formulas.

So, do not afraid to speak up. Go and workshop it with the users (I guess, it is not users, but managers). I bet they will accept this as a proper solution
0
 

Author Comment

by:acramer_dominium
ID: 39659505
It doesn't matter if it pushes the spreadsheet columns further to the right. The user needs to be able to sort by different lenders, auditors, etc. Having them all in one field will not allow us to complete this.

Thank you for your efforts.
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 39672822
My Access Archon article on Switching Fields to Records might be useful:

http://www.helenfeddema.com/Files/accarch137.zip
0
 

Author Closing Comment

by:acramer_dominium
ID: 39818085
Going to try and manipulate this to work with my scenario
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

911 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

18 Experts available now in Live!

Get 1:1 Help Now