Solved

Access Row Data to Columns

Posted on 2013-11-12
12
317 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
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

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Read about achieving the basic levels of HRIS security in the workplace.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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.

759 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