CREATE TABLE [меню] (
	[ID] [int] IDENTITY ( 1 ,  1 ) NOT NULL ,
	[Блюдо] [char] ( 50 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[Количество] [char] ( 50 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	CONSTRAINT [PK_меню] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

CREATE TABLE [рецепты] (
	[ID] [int] IDENTITY ( 1 ,  1 ) NOT NULL ,
	[Блюдо] [char] ( 50 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[Компонент] [char] ( 50 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[Количество] [float] NOT NULL ,
	CONSTRAINT [PK_рецепты] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

CREATE TABLE [Расчет] (
	[ID] [int] IDENTITY ( 1 ,  1 ) NOT NULL ,
	[Продукт] [char] ( 50 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[Количество] [float] NOT NULL ,
	[Level] [int] NOT NULL CONSTRAINT [DF_Расчет_Level] DEFAULT ( 0 ),
	CONSTRAINT [PK_Расчет] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

CREATE VIEW dbo.m_Decode
AS
SELECT     C.Продукт, R.Компонент, C.Количество * R.Количество AS Количество, C.[Level]
FROM         dbo.Расчет C INNER JOIN
                      dbo.рецепты R ON C.Продукт = R.Блюдо
GO

CREATE VIEW dbo.m_Result
AS
SELECT     dbo.рецепты.Компонент, SUM(dbo.Расчет.Количество) AS Количество
FROM         dbo.Расчет INNER JOIN
                      dbo.рецепты ON dbo.Расчет.Продукт = dbo.рецепты.Компонент LEFT OUTER JOIN
                      dbo.рецепты рецепты_1 ON dbo.рецепты.Компонент = рецепты_1.Блюдо
WHERE     (рецепты_1.ID IS NULL)
GROUP BY dbo.рецепты.Компонент
GO

-- это надо переписать на VBA
CREATE PROCEDURE m_Calculate
AS
set nocount on

TRUNCATE TABLE dbo.Расчет 

INSERT INTO dbo.Расчет (Продукт, Количество)
SELECT     Блюдо, Количество
FROM         dbo.меню

declare @l int
set @l= 0 
while exists(select * from dbo.m_Decode where [Level]=@l) begin
    INSERT INTO dbo.Расчет (Продукт, Количество,[Level])
    SELECT     Компонент, Количество, [Level]+ 1 
    FROM         dbo.m_Decode where [Level]=@l
    set @l=@l+ 1 
end

select * from dbo.m_Result
RETURN