Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Reports with values from multiple record expanding SIDEWAYS

Posted on 2008-06-24
12
Medium Priority
?
458 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

972 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