?
Solved

Report accessing multiple rows from query

Posted on 2007-07-24
15
Medium Priority
?
208 Views
Last Modified: 2013-11-28
In Access I have a query that sorts out my customers by the top 5 spenders. When I run the query sometimes there may only be 1,2 3  or 4 spenders.

I have a report that organizes these spenders into 5 columns. Showing their name, delivery info, total expenditures etc. I would like my report to recognize each spender and put them and their information  in the report from 1 to 5. if there is less than 5 spenders, the report leaves the last colum(s) blank.

In other words, the report will enter into column1(report) all data from row 1 (query). Same with colum2(Report) row2 (query) etc.
0
Comment
Question by:ouestque
  • 5
  • 4
  • 4
  • +2
15 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 19557023
Can you show us some typical query results?  Looks like there may be a productID in there somewhere?
0
 
LVL 75
ID: 19557061
" the report leaves the last colum(s) blank."

Not sure what the Q is?

You can set the controls Can Grow / Can Shrink properties to Yes ... to eliminate white space if no data is present ...  is this what you mean?

mx
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 19557111
you may have to use a crosstab query to get the result you want.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Accepted Solution

by:
aesmike earned 2000 total points
ID: 19557416
ouestque,
1. Set up your fields as if you were going to have one column.
e.g. your controls on the report might be:
name
delivery info
total expenditures
etc.
2. Make these fields narrow enough so that you could fit 5 of them across the page.
3. On the report, go to Page Setup, click on the Columns tab.  Specify that you want 5 columns.  
4. Specify that you want the layout to go Across then Down

This will replicate your rows across the page as columns.  If you have 4 or less, you will have blank spaces on the right.  You may have to futz around if you have headers that need to be duplicated.
0
 

Author Comment

by:ouestque
ID: 19558797
Awesome aesmike!. The columns do repeat theirselves, but I have labels to the left of each column that tells what data is; see example below.

Example: Name                  aesmike
                delivery info.      Ship return
                total expdtrs       $10

How do I repeat columns while having labels to the left stay consistant? This is what I want it to look like

Example: Name                  aesmike                John                                 Jason
                delivery info.      Ship return           Shipment recieved           Shipment in transit
                total expdtrs       $10                       $100                                 $1000


0
 
LVL 10

Expert Comment

by:aesmike
ID: 19558819
yes, a little tricky.  You could move the labels to a new report and embed this multi-column one as a subreport.  Remove the labels from the multi-column report, of course.
0
 
LVL 75
ID: 19558820
That's why mike has a Beemer :-)

da MAN!

mx
0
 
LVL 10

Expert Comment

by:aesmike
ID: 19558827
BTW, You've got the 90/10 solution.  90% of the task is complete in 10% of the time. You will spend the remaining 90% of your time getting those pesky labels to look right!
0
 

Author Comment

by:ouestque
ID: 19558873
I deleted all items on my report except one column. and specified 5 columns in page setup, but it shows one column on multiple pages. Is it possible to fit it all 5 columns on one page?
0
 
LVL 75
ID: 19558885
"The 90/10 solution"

Similar to Beemer maintance ?

mx
0
 
LVL 10

Expert Comment

by:aesmike
ID: 19558946
Yes.  I forget which works: either you can make your detail band width narrow so it's only like 1 or 2" wide --or, in the Column tab of the page setup there's a checkbox saying that the columns should be the same width as the detail.  Uncheck that and type in a width that, when multiplied by 5 (plus a little more room) will fit on your page, allowing for the labels, of course.
0
 

Author Comment

by:ouestque
ID: 19558972
Nevermind I changed column widths to get it.
0
 

Author Comment

by:ouestque
ID: 19558975
Drum Roll. aesmike!!!!!
0
 
LVL 10

Expert Comment

by:aesmike
ID: 19558979
No, beemer maintenance is similar to the scene in Vacation: What's the damage, fellas?  How much you got???"
0
 
LVL 75
ID: 19559045
Hey ... I got two more EE t-shirts over the weekend ... that is FIVE now.  Man, we are almost dialed in for the wedding!  And you have one coming also.  And ... I should have one more by Friday or so!

mx
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

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 …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

850 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