Hi Viv,

Your new data is totaly different from your original example and you didn't provide adequate description of it.

>Thx. Below is a simple set of data I'm working with (using different table/column names than my original post I'm afraid - recipe is now the equivalent of component) together with a query that seems to basically give the correct results.
>
>However this data has a wrinkle - the 'portion' column indicates whether the 'quan' refers to a multiple of the recipe or to a fixed weight. For example assume recipe 231 is for Scotch & Water with 80gms of Scotch and 50gms of water. Id 94 (where portion=False) calls for 100g of Scotch & Water i.e. approx 61gms of id 64 (Scotch) and 38gms of id 65 (Water) (rather than 100 * 130gms) I'm stuck on this - especially since the two modes are mixed.
>
>Anyway:
>

>/****** Object:  Table [dbo].[RecipeList]    Script Date: 05/02/2007 14:32:46 ******/
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>CREATE TABLE [dbo].[TestR](
>	[id] [int] NOT NULL,
>	[recipeid] [int] NOT NULL,
>	[quan] [numeric](7, 2) NOT NULL,
>	[portion] [bit] NOT NULL,
>	[itemid] [int] NOT NULL,
>	[itemisrecipe] [bit] NOT NULL,
>	[expandthis] [bit] NOT NULL
>) ON [PRIMARY]
>GO
>INSERT INTO TestR(id, recipeid, quan, portion, itemid, itemisrecipe, expandthis)
>VALUES     (70, 23, 1.75, 1, 230, 1, 1)
>GO
>INSERT INTO TestR(id, recipeid, quan, portion, itemid, itemisrecipe, expandthis)
>VALUES     (71, 23, 70, 0, 21, 0, 0)
>GO
>INSERT INTO TestR(id, recipeid, quan, portion, itemid, itemisrecipe, expandthis)
>VALUES     (72, 23, 15, 0, 31, 0, 0)
>GO
>INSERT INTO TestR(id, recipeid, quan, portion, itemid, itemisrecipe, expandthis)
>VALUES     (62, 230, 125, 0, 4, 0, 0)
>GO
>INSERT INTO TestR(id, recipeid, quan, portion, itemid, itemisrecipe, expandthis)
>VALUES     (63, 230, 5, 0, 11, 0, 0)
>GO
>INSERT INTO TestR(id, recipeid, quan, portion, itemid, itemisrecipe, expandthis)
>VALUES     (94, 230, 100, 0, 231, 1, 1)
>GO
>INSERT INTO TestR(id, recipeid, quan, portion, itemid, itemisrecipe, expandthis)
>VALUES     (64, 231, 80, 0, 23, 0, 0)
>GO
>INSERT INTO TestR(id, recipeid, quan, portion, itemid, itemisrecipe, expandthis)
>VALUES     (65, 231, 5, 0, 11, 0, 0)
>

>Query:
>

>WITH Parts AS (
>-- Anchor
>SELECT
>id,
>recipeid,
>quan,
>itemid,
>itemisrecipe
>FROM TestR WHERE (recipeid = 23)
>
>UNION ALL
>-- Recursive
>SELECT
>r.id,
>r.recipeid,
>CAST(r.quan * p.quan AS numeric(7, 2)) AS quan,
>r.itemid,
>r.itemisrecipe
>FROM TestR r INNER JOIN Parts p ON p.itemid = r.recipeid
>WHERE (p.itemisrecipe = 1)
>)
>
>SELECT SUM(quan) quan, itemid,itemisrecipe
>FROM Parts AS p2 WHERE itemisrecipe=0 GROUP BY itemid,itemisrecipe
>

>Any suggestions gratefully accepted,
>Viv