Report accessing multiple rows from query
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.
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.
GRayL
Can you show us some typical query results? Looks like there may be a productID in there somewhere?
" 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
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
you may have to use a crosstab query to get the result you want.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.
That's why mike has a Beemer :-)
da MAN!
mx
da MAN!
mx
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!
ASKER
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?
"The 90/10 solution"
Similar to Beemer maintance ?
mx
Similar to Beemer maintance ?
mx
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.
ASKER
Nevermind I changed column widths to get it.
ASKER
Drum Roll. aesmike!!!!!
No, beemer maintenance is similar to the scene in Vacation: What's the damage, fellas? How much you got???"
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
mx