Помогите пожалуйста с решением задачи.

Имеем таблицы tblElement и tblNodeElement см. на схеме ниже. (схема взята из примера приведенного Программистом-Любителем, т.к. немного попроще чем подобная схема в моей базе.
Необходимо вывести суммарную норму расхода запчастей (при ремонте) и суммарное их количество в (корневом) изделии. Расчет нормы расхода и количества в изделии, как мне кажется, производится по следующим алгоритмам:
Норма расхода запчасти на изделие:
Низд = ((([НсбN] *[ КсбN])*[KсбN-1]+[НсбN-1]*[КсбN-1])*[KсбN-2]+ [НсбN-2]*[КсбN-2])…..
Количество запчастей в (корневом) изделии:
KЗизд =( ([KЗсбN]*[KсбN-1]+[КЗсбN-1])*[КсбN-2]+[КЗсбN-2])*КсбN-3+……
Где
Низд – норма расхода запчасти на изделие
КЗизд – суммарное количество запчасти в изделии
Ксб – количество сборок
N – количество уровней вложенности (изделие-сборка-подсборка-подподсборка…)
И соответственно
НсбN, КЗсбN, KсбN – норма, количество запчастей, количество подсборок самого нижнего уровня [НсбN-1], [КЗсбN-1], [КсбN-1] –норма, количество запчастей, количество подсборок на уровень выше.
На выходе должна получиться таблица с полями
iElementID_Root, iElementID, decNorm, decCnt
где для каждой запчасти корневого изделия должна быть указана (суммарная) норма расхода на ремонт, и общее количество запчастей на изделие.
Изначально вся схема сделана в MS_SQL Server, но так как вся работа с базой идет через Access (ADP проект Access 2003 + MS_SQL Server 2008 R2), то пишу в этой ветке.
Задача, по моему, сложная используется рекурсивный запрос (ниже приведу), на готовые решения не надеюсь, подскажите куда «бить головой» и хоть примерный ход решения.

...вообще описание сложное получилось, в жизни несколько попроще. Уровней вложенности у меня на текущий момент всего два и отчасти получалось решить эту задачку простой группировкой, не исключено впрочем увеличение количества уровней в будущем до максимум 5, поэтому привел "идеальный" алгоритм по которому их может быть сколько угодно. ... Правда как реализовать этот алгоритм через DML не представляю

Рекурсивный запрос. Опять же взял пример П-Л, т.к. в моем варианте черт ногу сломит. (

Да П-Л все верно, очень хорошо что сразу на тебя "нарвался" просто не понимаю как приспособить этот рекурсивный запрос именно под вычисление норм и количеств. Само по себе то все это работает на ура, дерево строится, все что нужно выдает, а вот итоговые расчеты ... тут загвоздка

Я помню был вариант с адресами, по которым вроде как легко прослеживалась каждая деталь, где стоит и сколько их, но потом от адресов мы ушли. Все только через этот запрос. Сейчас об адресах вспоминать поздно, так как наработано уже весьма не мало.
Плюс тут еще сложность что вычисляется не только количество деталей, но и их нормы расхода, а они вычисляются немного по другому алгоритму. В общем на выходе получается "швах". Я делал расчет группировкой на основе данных полученных рекурсивным запросом. Возникли проблемы при некоторых условиях. ... Блин. Не хочу еще усложнять, если эти условия описывать это довольно много добавить надо к исходному посту.

Во-во. Про эти адреса и писал :( У меня их нет.
Надо прочитать, подумать.

Чую, надо шаманить с этим:



Спасибо П-Л!

Суть понятна, попробую реализовать это в моем "кривом" коде.

Изерлонер...попробую реализовать это в моем "кривом" коде.
вот правда, код кривой до жути. Но если бы ты в свое время не направил, все было бы ишшо хуже. А кривости имеют вполне объективные причины, жуткая загрузка текущей работой (при этом использую то что уже наработал с базой, что с одной стороны где-то ускоряет процесс, но при этом закрепляет текущее состояние базы, так как внести серьезное изменение пусть даже в кривую, но используемую в работе базу - чревато...) и отсутствие опыта. По тихоньку ситуация исправляется, надеюсь время у меня еще есть. Было бы обидно уйти с этого места работы так и не реализовав всего вложенного труда и того потенциала что дает эта база. ... Блин. За идею работаю! Не так страшно работу потерять и зарплату (другую найду) как все эти наработки.

П-Л, в общем где-то я перемудрил со схемой данных изначально. Не получается у меня. Подозреваю что из-за схемы.
Ниже представлен кусок схемы данных. Проблема в том что данные о количестве деталей на изделие и нормы расхода разнесены по разным таблицам - выделил красным. Просто нету самой по себе нормы расхода на ремонт изделия (она есть как-бэ, и даже у меня в схеме отражена, но этот столбец фактически мной не используется, и вообще это не совсем верно, потому что норма на изделие зависит от частных норм по цехам, так что по идее это излишний столбец, данные в котором должны соответствовать сумме всех цеховых норм) Есть норма расхода запчастей на изделие В КОНКРЕТНОМ ЦЕХЕ и именно она важна. От нее плясать надо, именно этому служит таблица tblZehNorm.
Твой код рекурсивного запроса я использую не на одной таблице (tblNodeElement), а на запросе включающем в себя фактически все таблицы схемы (из которых сейчас важны именно две представленные таблицы). Рекурсивный запрос позволяет строить иерархическую схему входимости изделий и в принципе корректно показывает все данные. Однако когда я пытаюсь получить итоговые данные - суммарные нормы, суммарное количество на изделие... Суммарное количество увеличивается кратно количеству цехов в которых ставится запчасть на это изделие. (оно и понятно, просто исходя из схемы данных). Т.е. запрос собирает все данные по запчасти на изделии, суммирует их корректно... а затем умножает в два, в три и т.д. (по количеству цехов) раза. С нормами дело обстоит несколько лучше. Я могу получить корректные данные по нормам сделав запрос на группировку по данным рекурсивного запроса, однако непосредственно в самом рекурсивном запросе вывести эти данные пока не получается, где-то безнадежно туплю, все время норма получается равной нулю (ну или единице исходя из
)
В общем у меня два вопроса.
Первое, как бы ты организовал учет (хотя учет здесь не совсем корректное слово, это скорее просто такой справочник нормативов, учет ведется в другом месте и не мной), если его необходимо вести по отдельным подразделениям (цехам) и в целом по изделию. Годится ли представленная ниже схема для этого? По мне так схема годится, просто исходя из того что она себя уже достаточно показала, и больших проблем не было, но сейчас после нескольких ударов головой ап стену, у меня сомнения возникли
Второе, если твой вариант прилично отличается от представленного, то что вообще возможно сделать что бы поправить ситуацию и при этом не пришлось все переделывать с нуля? Рано или поздно все равно видимо придется, когда приобрету достаточно опыта и буду совершенно четко понимать что и как должно быть, и что вообще нужно

ЗЫ: слово Replaseable вычеркнул нафинг, что бы не путало. По факту использования нету там никакого Replaseable, я эту возможность внес в схему данных (при том здорово ее усложнив), но пока она остается "мертвой" и не используется.

Изерлонер что вообще возможно сделать что бы поправить ситуацию и при этом не пришлось все переделывать с нуля?
Если не трогать схему, то я пока как вариант вижу создание отдельных "хитрых" запросов, на основе результатов рекурсивного, отдельно выводящих данные по количеству запчастей в изделии, по нормам (это по сути уже есть, только чуть доработать), и наконец общего запроса объединяющего все эти отдельные данные. Минус - по моему горькому опыту работает подобный подход весьма медленно.

ЗЫ2: пусть тебя не смущают поля strPodrasdel.. iPodrasdel.. и иже с ними. Схема так или иначе постоянно улучшается дорабатывается, а хвосты остаются, либо по причине того что на них были сделаны запросы и они пока не исправлены, либо тупо по нехватке времени и опасения поломать что хоть криво, но работает.

Изерлонери вообще это не совсем верно, потому что норма на изделие зависит от частных норм по цехам
ЗЫ_3: я часто путаю что от чего зависит, потому что на практике и в теории разные подходы. В литературе (руководства по ремонту, альбомы и перечни) естественно нету никаких цехов. Норма всегда дается на ремонт изделия. Однако на практике каждому цеху на ремонт изделия назначается своя норма, в сумме это все конечно должно соответствовать. Так что очень не просто взять и выкинуть какие-то столбцы. Впрочем к сожалению нормы в литературе даются далеко не на весь перечень запчастей и материалов, на большую часть это все устанавливается исходя из практики ремонта пальцем в потолок и статистики. Для норм имеющих основание в ремонтной документации введен столбец decNodeElementNormObosn и столбцы strObosnovanie и strDopObosnovanie в которых прописывается ссылка на документ. Опять же на деле пока это все только задел на будущее, и не имеет сейчас критического значения.

Написать можно, только по опыту предыдущих попыток, я боюсь завязнуть в не существенных мелочах, а так же в своих хотелках. Всё не так уж и сложно, но человеку со стороны трудно вникнуть, отсюда попытки объяснить простые вещи своими словами что только увеличивает сложность описания. По моему мнению система не столь сложна, проблема с ее реализацией не опытным разработчиком (мной).
Что касается хотелок... Есть конечно и сложности, на которые я просто закрываю глаза. Какие то дикие требования что в одном случае нормы рассчитываются так, в другом иначе, в одном случае деталь подлежит обязательной замене, в другом заменяется только по дефектации... В этом всём в жизни то разобраться не получается, не то что реализовать это в базе. ...когда я обратился к главному инженеру с предложением автоматизировать всё это, дабы упорядочить весь этот бардак – его ответ был – если автоматизировать бардак, то получится автоматизированный бардак. После нескольких попыток просто разобраться, стал тупо игнорировать все эти сложности, они все равно погоды не делают, и на 80–90% можно закрыть все потребности упрощенной системой, которую и пытаюсь реализовать.

А оставшиеся 10–20% можно привести к нужному виду, либо на худой конец доработать ручками.

П–Л, я ещё боюсь что не так уж много времени мне осталось. На полноценную переработку схемы его нет. На днях мне капитально влетело от начальства с тем что не успел в срок сделать отчёт по ремонту, с лишением премии и всё такое. Правда полноценные исходные данные для этого отчета я получил лишь накануне, и сделать всё в срок возможно было бы если бы моя система в автоматически режиме готовила бы отчёт со всем оформлением и сбрасывала его в эксель уже оформленным, для дальнейшей проверки и распечатки. Пока я такого не реализовал.
Начальству же глубоко до лампочки что даже то что я сделал позволяет ускорить работу в разы, и сидела бы на моем месте тетя со средним образованием и эксель необходимого отчета они недели две дожидались бы.
«что там делать?! Разработал нормы расхода и контролируй» «Ты! Как нормировщик обязан копировать и собирать все счёт фактуры на защиту, это не работа экономистов»
А ещё я обязан разрабатывать нормы расхода материалов и запчастей для всего производства в целом и отдельных подразделений (цехов), на других предприятиях для такого работают целые отделы вообще то. А ещё составлять планы развития производства и отслеживать их выполнение. А ещё подменять отсутствующих технологов по цехам, так как народ не держится и уходит, и кому то надо и эту работу вести. (конечно не я один на замене, но нас мало). И еще много много других ещё.
Если при следующем отчете ситуация с задержкой отчета повториться (а так оно и будет! Иначе и быть не может, до тех пор как я запущу систему в полноценное использование) меня скорей всего уволят.
Как уже писал, жалко не столько рабочего места, сколько вложенного труда, и того потенциала который так и останется не реализованным.

Программист-ЛюбительНа количестве записей несколько десятков тысяч записей все подобные расчетв должны летать. Если не летает - увы, виноват радиус кривизны собственных рук. На том числе сущностей, что у тебя есть при нормально организованных индексах и запросах все эти нормы должны считаться ОЧЕНЬ быстро.

Здесь ситуация несколько исправилась. Не скажу что все прямо так летает, но в сравнении с тем что было раньше... да летает. По крайней мере есть несколько форм с отвязанным рекордсетом в которых при перемещении по записям ... скажем так в мастер форме, в подчиненной меняется рекордсорс по соответствующему запросу и высвечивается результат в доли ... ну максимум в секунду. Это вполне устраивает... Ну есть проблема с выводом иерархии в тривью, там расчет длится 30-60 секунд, просто так все организовал что расчитываются и выводятся все имеющиеся изделия, но форма с тривью мной используется очень редко, потому пока не заморачиваюсь. Там просто переделать ее надо, но это пока не критично и особо ни на что не влияет, потому пока не занимался. Есть вещи важнее.


Программист-ЛюбительНазначение системы - вести учет ремонта изделий.

Нет. Непосредственно учет ведется в бухгалтерии на их базе. Таблицы которой и использую. О том много уже писал, то к чему пришел сейчас с учетом - устраивает. Учет - не моя задача. Я только импортирую их данные и обрабатываю их. Моя задача что бы все что там у бухгалтеров учитывается не выходило за рамки норм. Ну и соответственно подготовка отчетов по ремонту изделий, вот сколько и чего списали, а вот ссылка на позицию в нормативе. Все должно биться. Если не бьется - меня линчуют и вполне обоснованно.

Программист-ЛюбительПри ремонте изделия ремонту могут подвергаться любые его элементы (составные части).
Ремонт может вестись в разных цехах. Один и тот же элемент изделия может ремонтироваться в разных (более чем одном цехе).

Так. Правда изделий одновременно ремонтируемых в разных цехах не так уж много, основная масса - одно изделие (сборка, агрегат) - один цех. Это и спасало, так как при таких условиях моя система выдает корректные данные. Но есть некоторые изделия и запчасти к ним которые проходят не по одному цеху. Ну например какие-то крепежные изделия, перемычки металлизации, которые списываются не на какую-то сборку, а на все большое изделие (вертолет) в целом, а соответственно проходят практически по всем цехам. Вот по этим деталям проходит удвоение, утроение и т.д. с чем и пытаюсь бороться, так как корректировать полученные данные на выходе как правило нет времени, и в нормативе по этим деталям получается чушь собачья. Хорошо что пока никто не обратил внимания.

Программист-ЛюбительВ каждом цехе существуют свои нормы расхода при ремонте элементов изделия.
Во время ремонта происходит фактическое выписывание (выделение) элементов для ремонта.
Общее количество всех элементов, выписанных за время ремонта изделия, суммируется и сравнивается с нормами расхода, чтобы контролировать экономию или перерасход в разрезе цехов и всего по изделию.

Совершенно точно. ..сравнивается с нормами расхода, и вообще с количеством сколько вообще таких деталей стоит на изделии. Превышение норм расхода еще не критично (если это не массовое явление) и можно объяснить, а вот превышение количества на изделия чревато проблемами при защите цены. Как может быть выписано на ремонт изделия А шесть штук подшипников типа... если их там всего два? Для этого у меня в системе есть индикация (условное форматирование) - превысило норму - ячейка окрашивается в оранжевый цвет, я как нормировщик принимаю решение пропустить или нет, могу вызвать из цеха ответственных "Ходи сюда - почему выписали больше? Если надо - пиши рапорт, что так надо и почему надо". Превышено количество на изделии (самом изделии как таковом, сборке, агрегате) - красный цвет. Тут же такое "требование" (документ на списание) отклоняю. Частенько происходит. А учитывая то что по некоторым деталям у меня идет удвоение, утроение, возникают мои ошибки.


Программист-ЛюбительВозвращаясь к более узкому вопросу - правильно я понимаю, что проблему вызывает запрос, подсчитывающий общее количество фактически израсходованных элементов и общие нормы в разрезе цехов/всего по изделию ?
Общее количество по выписанных по факту расчитывается сейчас корректно. Есть мелкие ошибки, по одной двум записям на изделие так же идет удвоение, пока не разобрался почему, ... так как эти ошибки достаточно легко обнаружить, ... не до них просто было и не заморачивался пока, просто держу в уме что такая проблема иногда возникает, при случае надо разобраться.
Нормы тоже более менее, критичных ошибок вроде нет. А вот количество на изделии которое должно быть... врет система. И причину я вижу совершенно четко. (схема данных на первой картинке), а вот обойти ее... при такой схеме наверное возможно созданием отдельного рекурсивного запроса только по таблице tblNodeElement без включения tblZehNorm. И затем созданием общего запроса с включением в него этих данных... Мне кажется это сейчас единственный вариант.