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
Solved

Reports with values from multiple record expanding SIDEWAYS

Posted on 2008-06-24
12
430 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 34

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 34

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
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.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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.

790 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