Prev: Multiple Counts In Select Query
Next: Publishing a block of code for all to use
From: The Frog on 10 Aug 2010 09:02
Thanks for that James, I have bookmarked the url for future reference.
Thats a better explanation than the one I currently have. I appreciate
it.

The Frog
From: Salad on 10 Aug 2010 11:44
The Frog wrote:

> Hi Salad, thanks for having a crack at it. I ended up taking a
> completely different approach, one where a series of sub-selects are
> used to populate the columns as needed.
>

Just curious. Is this for a report or form? If report you could use a
running sum to break on bands and not bother with it.
From: Salad on 10 Aug 2010 11:52
The Frog wrote:

> Hi Salad, thanks for having a crack at it. I ended up taking a
> completely different approach, one where a series of sub-selects are
> used to populate the columns as needed.
>
> I made a query that brought together all the cat, subcat, seg.....and
> so on down to the individual product level. I used this query as the
> basis for another query, where the columns I really wanted are each
> done with the inline selects:
>
> SELECT DISTINCT category.category, subcategory.subcategory,
> segment.segment, IIf(InStr([variety].[variety],[product].[product])<>0,
> [variety].[variety],IIf(InStr([product].[product],[variety].
> [variety])<>0,[product].[product],[product].[product] & " " &
> [variety].[variety])) AS ITEM, IIf((SELECT chanel_id FROM
> variety_channel WHERE variety.variety_id = variety_channel.variety_id
> AND variety_channel.chanel_id = 1) Is Not Null,[ITEM],Null) AS
> Drogerie_Klein, IIf((SELECT chanel_id FROM variety_channel WHERE
> variety.variety_id = variety_channel.variety_id AND
> variety_channel.chanel_id = 2) Is Not Null,[ITEM],Null) AS
> Drogerie_Gro�, IIf((SELECT chanel_id FROM variety_channel WHERE
> variety.variety_id = variety_channel.variety_id AND
> variety_channel.chanel_id = 3) Is Not Null,[ITEM],Null) AS Trad_LEH,
> IIf((SELECT chanel_id FROM variety_channel WHERE variety.variety_id =
> variety_channel.variety_id AND variety_channel.chanel_id = 4) Is Not
> Null,[ITEM],Null) AS VM_Klein, IIf((SELECT chanel_id FROM
> variety_channel WHERE variety.variety_id = variety_channel.variety_id
> AND variety_channel.chanel_id = 5) Is Not Null,[ITEM],Null) AS VM_Gro�
> FROM ((category INNER JOIN subcategory ON category.category_id =
> subcategory.category_id) INNER JOIN segment ON
> subcategory.subcategory_id = segment.subcategory_id) INNER JOIN
> (subsegment INNER JOIN (product INNER JOIN variety ON
> product.product_id = variety.product_id) ON subsegment.subsegment_id =
> product.subsegment_id) ON segment.segment_id = subsegment.segment_id;
>
>
> It looks a lot more messy than it really is. There is a lot of
> repeated blurb with the IIf statements to get the right text
> formatting. Anyway, I end up with a list of each product by cat,
> subcat..... and a column for each channel with the product (ITEM)
> listed again in that column if it applies to the given channel. It
> works quickly enough for the moment. Might be a problem later if the
> product list really grows.
>
> Thanks for having a shot at it anyway.
>
> Cheers
>
> The Frog

I'm not a fan of subselects in Access, basically due to speed issues
from way in the past. Some are fans. I'd sooner have a
Query1
Query1Sub1
Query1Sub2
Query1Sub3
and then design Query1 to include the 3 sub queries and select the
inner, left, or right joins to suit. I have 3 extra queries and is snappy.
First  |  Prev  | 
Pages: 1 2
Prev: Multiple Counts In Select Query
Next: Publishing a block of code for all to use