Hi again everyone,

I am hoping that there might be an easy way to achieve this before I
go and create bucket loads of queries. I have a need to produce a
report with five sets of data, side by side. They are effectively
lists based around a single field. I have previously generated a query
that has a column for each list (call each here a channel), as well as
an extra set of columns that would allow for filtering. Because not
every 'item' is in every list, I have some nulls in each Channels
lists.

What I want to do is to create a report, based on this query (if
possible) that does two things:
1/ Lists only the items in each channel filtered by whatever filter
criteria I set on the report
2/ Not have any nulls show in the output - ie/ the channel columns
will be of different lengths for each channel and the records will not
have any null or 'gap' fields in the listing. Would kind of look like
a 'waterfall' type approach.

Is this possible to do in a single report or am I going to have to
break this up into lots of sub reports (or possible queries) in order
to feed the main report and get the right results and layout?

If anyone has some pointers here I am all ears....

Cheers

The Frog

On Aug 10, 9:11*am, The Frog <mr.frog.to.... (AT) googlemail (DOT) com> wrote:
Here's a shot in the dark:

Separating into groups based on a single field:

http://groups.google.com/group/comp....789cb9e5da76d4

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

On 10/08/2010 16:37:46, "James A. Fortune" wrote:
Dunno if this will work, but create your 5 groups set all fields to vertical
and then print the whole report as it were at rightanlges

Phil

Hi James, Phil,

Thankyou for the feedback and ideas. I understand the point made in
the link James posted. It is almost exactly the same problem that I am
trying to solve by the look of it. The underlying tables are
normalised and can be simply represented as five columns when viewed
through a query - category, subcategory, segment, channel, ITEM.

There are 5 channels, and each has items listed against it. Each item
is classified with a category, subcategory and segment. What I am
trying to do is to have 5 columns on the report, one for each channel,
and then filter the results by setting the reports filter property to
whatever category, subcategory or segment is desired. The actual
category, subcategory or segment values will never be shown on the
report and are only there for filtering purposes.

Is there a way to do this? Am I overcomplicating the whole thing?

I will play with the ideas in the link you posted James. I can sort of
see what is trying to be achieved there but I cant say I understand it
fully, even understanding each individual part I dont see the whole
(so to speak).

Cheers

The Frog

Ah!
Access will (in the background) build a different query to use when it runs
the report. If you don't use the field in the report, it will drop the field
from the query results.

So add controls for category, subcategory, and segment to your report (they
can even be a section of the report that you don't print. You can set the
visible property on the controls to false (No).

Now you should be able to apply a filter or where string to filter the output.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

The Frog wrote:

Thanks John, nailed it :-) Now I have 26 pages produced without having
to write 26 individual reports. Sweet. I knew there must be a simpler
way to do this.

Very much appreciated

The Frog

On Aug 11, 3:21*am, The Frog <mr.frog.to.... (AT) googlemail (DOT) com> wrote:

That's perfectly understandable :-) since I didn't go into as much
detail as I normally do in that post.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com