From: The Frog on
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
From: James A. Fortune on
On Aug 10, 9:11 am, The Frog <mr.frog.to....(a)googlemail.com> wrote:
> 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

Here's a shot in the dark:

Separating into groups based on a single field:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/69c32afc8b1d9fe5/d0789cb9e5da76d4#d0789cb9e5da76d4

James A. Fortune
CDMAPoster(a)FortuneJames.com
From: Phil on
On 10/08/2010 16:37:46, "James A. Fortune" wrote:
> On Aug 10, 9:11�am, The Frog <mr.frog.to....(a)googlemail.com> wrote:
>> 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
>
> Here's a shot in the dark:
>
> Separating into groups based on a single field:
>
> http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/69c32afc8b1d9fe5/d0789cb9e5da76d4#d0789cb9e5da76d4
>
> James A. Fortune
> CDMAPoster(a)FortuneJames.com
>

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
From: The Frog on
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
From: John Spencer on
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:
> 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