Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Reports with values from multiple record expanding SIDEWAYS

Posted on 2008-06-24
12
Medium Priority
?
453 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
[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
  • 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
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.

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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 Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

705 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