Report Design question
in [Help]
|
From: The Frog on 10 Aug 2010 09:11 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 10 Aug 2010 11:37 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 10 Aug 2010 12:39 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 11 Aug 2010 03:21 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 11 Aug 2010 08:53
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 |