Solved

Access Row Data to Columns

Posted on 2013-11-12
12
360 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
[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
12 Comments
 
LVL 41

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 25

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 25

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
 

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 25

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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

615 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