acramer_dominium
asked on
Access Row Data to Columns
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
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
Sharath S
Post your query. Not able to see your query in the attachment as it is prompting to select one of the reports.
I have enhanced the query. Please have a look
AuditTaxTracking.accdb
AuditTaxTracking.accdb
ASKER
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.
Sharath_123: If you hold shift while you open the database you will open it in design view and see the query.
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
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
ASKER
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.
ASKER
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.
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.
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
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
ASKER
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.
Thank you for your efforts.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Going to try and manipulate this to work with my scenario