Reports with values from multiple record expanding SIDEWAYS

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
jgdeutschAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
jgdeutsch,

Here is a sample.

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

JeffCoachman
Access-EEQ23513292FlattenAccumul.mdb
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
where is rptEmails.doc?
0
 
jgdeutschAuthor Commented:
Thought I included it here you go thanks
EmailReport.mdb
rptEmails.doc
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
jgdeutschAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
And give a few employees more that two Products
0
 
Jeffrey CoachmanMIS LiasonCommented:
jgdeutsch?
Do you still need help with this?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.