Solved

Reports with values from multiple record expanding SIDEWAYS

Posted on 2008-06-24
12
428 Views
Last Modified: 2013-11-28
Ok we all know in Access reports when you have multiple records listed in the Detail section they expand DOWN, I want them to expand SIDEWAYS

SITUATION
This is a customer email questions and response report that will be sent to customers detailing their questions and the appropriate response to each one. Each question can be based on multiple Purchase Order IDs OR multiple Product IDs or a mixture of both.

INCLUDED
A sample database with all relevant and necessary tables and queries you can use those.
Check out rptEmails it has all the DATA I need for this report but it's in the wrong format.
See rptEmails.doc to see the format I want.

Jeff
0
Comment
Question by:jgdeutsch
  • 6
  • 2
  • 2
12 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 21862875
where is rptEmails.doc?
0
 

Author Comment

by:jgdeutsch
ID: 21863123
Thought I included it here you go thanks
EmailReport.mdb
rptEmails.doc
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 21866529
Hi Jeff,

While at work, I don't download from EE. First I will be able to get to it is Thursday evening.

Thanks,

Mike
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21879459
jgdeutsch,

My observations:
1. You have not, as of yet, established all of your relationships, in the relationship screen.

2. none of your relationships have referential integrity enforced.

3. All of your primary keys have the same name: ID
This causes Access to become totally confused when "enable Auto-Join" in queries is enabled
(Which it is by default)

4. Usually these types of "accumulations" (Going "sideways") occur in the Group footer or after the details. Your request reverses this.

5. Your use of nested subreports instead of a single Grouped reports make you report difficult to navigate in design view.

6. Some of you lookups seem to work in reverse. You display the ID in the field and show the Name only in when the list is dropped down.

7. You have not established a link between the EmailQuestion and the Purchase Order.(This would eliminate the need for the QuestionPurchaseOrdersAndProducts Table)


All of these factors make it difficult for me to to present a solution, without redesigning your DB to some extent.

Lets wait and see what eghtebas' observations are.

JeffCoachman
0
 

Author Comment

by:jgdeutsch
ID: 21995496
Basically the key here is that I have a one->many relationship and I want to display the values from the many side horizontally, not vertically. Can that happen?

For example, instead of seeing values as

States:
CA
MI
FL

I want

States: CA, MI, FL
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21996161
jgdeutsch,

Yes, I understand perfectly, I have done this before, see here:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_23487011.html

Yours is a bit more complicated because I have to make a query (Based on the Report layout) to get all the related values.
Then write the code....

This may take me a day or two.

In the mean time, perhaps eghtebas might jump in with a solution.

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21997195
jgdeutsch,

I may be close.
Can download the same sample you posted here (so everthing stays the same), and give Employees: Barry and Calvin some child "Records" like Rex?

I think I have it working for Rex, I just have to see if it still works when the employee changes.

Jeff
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22003948
And give a few employees more that two Products
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22043035
jgdeutsch?
Do you still need help with this?
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 22056483
jgdeutsch,

Here is a sample.

It works as specified, based on the sample you sbmitted.

JeffCoachman
Access-EEQ23513292FlattenAccumul.mdb
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
A Function to parse a text string 4 37
backup programme - VBA 3 24
Access: Retrieving Current Month's Orders for Invoice 6 29
MS Access from Delphi 31 32
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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, when working with VBA, learn some techniques for writing readable and easily maintained code.

803 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