AnsweredAssumed Answered

how much of each ingredient is required for each single invoice?

Question asked by gorgyr on Nov 7, 2009
Latest reply on Nov 9, 2009 by philmodjunk

Title

how much of each ingredient is required for each single invoice?

Post

Can anyone help me on the road?

I have 3 tables:
ingredients                     products                   invoices<!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:View&gt;Normal&lt;/w:View&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:HyphenationZone&gt;21&lt;/w:HyphenationZone&gt;   &lt;w:DoNotOptimizeForBrowser/&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  /* Font Definitions */ @font-face      {font-family:&quot;Baskerville Old Face&quot;;      mso-font-alt:&quot;Times New Roman&quot;;      mso-font-charset:0;      mso-generic-font-family:roman;      mso-font-pitch:variable;      mso-font-signature:3 0 0 0 1 0;} @font-face      {font-family:&quot;Arial Unicode MS&quot;;      mso-font-alt:&quot;MS Mincho&quot;;      mso-font-charset:128;      mso-generic-font-family:swiss;      mso-font-pitch:variable;      mso-font-signature:-1 -369098753 63 0 4129279 0;} @font-face      {font-family:&quot;\@Arial Unicode MS&quot;;      mso-font-charset:128;      mso-generic-font-family:swiss;      mso-font-pitch:variable;      mso-font-signature:-1 -369098753 63 0 4129279 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:&quot;Times New Roman&quot;;} p.Formuletitelfeestgids, li.Formuletitelfeestgids, div.Formuletitelfeestgids      {mso-style-name:&quot;Formuletitel feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      tab-stops:315.75pt;      font-size:14.0pt;      mso-bidi-font-size:16.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      text-decoration:underline;      text-underline:single;} p.Onderdeelformulefeestgids, li.Onderdeelformulefeestgids, div.Onderdeelformulefeestgids      {mso-style-name:&quot;Onderdeel formule feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      font-weight:bold;      mso-bidi-font-weight:normal;      font-style:italic;      mso-bidi-font-style:normal;} p.Titelfeestgids, li.Titelfeestgids, div.Titelfeestgids      {mso-style-name:&quot;Titel feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:24.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      color:#632423;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      font-weight:bold;      mso-bidi-font-weight:normal;      font-style:italic;      mso-bidi-font-style:normal;} @page Section1      {size:612.0pt 792.0pt;      margin:70.85pt 70.85pt 70.85pt 70.85pt;      mso-header-margin:35.4pt;      mso-footer-margin:35.4pt;      mso-paper-source:0;} div.Section1      {page:Section1;}   --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:View&gt;Normal&lt;/w:View&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:HyphenationZone&gt;21&lt;/w:HyphenationZone&gt;   &lt;w:DoNotOptimizeForBrowser/&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  /* Font Definitions */ @font-face      {font-family:&quot;Baskerville Old Face&quot;;      mso-font-alt:&quot;Times New Roman&quot;;      mso-font-charset:0;      mso-generic-font-family:roman;      mso-font-pitch:variable;      mso-font-signature:3 0 0 0 1 0;} @font-face      {font-family:&quot;Arial Unicode MS&quot;;      mso-font-alt:&quot;MS Mincho&quot;;      mso-font-charset:128;      mso-generic-font-family:swiss;      mso-font-pitch:variable;      mso-font-signature:-1 -369098753 63 0 4129279 0;} @font-face      {font-family:&quot;\@Arial Unicode MS&quot;;      mso-font-charset:128;      mso-generic-font-family:swiss;      mso-font-pitch:variable;      mso-font-signature:-1 -369098753 63 0 4129279 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:&quot;Times New Roman&quot;;} p.Formuletitelfeestgids, li.Formuletitelfeestgids, div.Formuletitelfeestgids      {mso-style-name:&quot;Formuletitel feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      tab-stops:315.75pt;      font-size:14.0pt;      mso-bidi-font-size:16.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      text-decoration:underline;      text-underline:single;} p.Onderdeelformulefeestgids, li.Onderdeelformulefeestgids, div.Onderdeelformulefeestgids      {mso-style-name:&quot;Onderdeel formule feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      font-weight:bold;      mso-bidi-font-weight:normal;      font-style:italic;      mso-bidi-font-style:normal;} p.Titelfeestgids, li.Titelfeestgids, div.Titelfeestgids      {mso-style-name:&quot;Titel feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:24.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      color:#632423;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      font-weight:bold;      mso-bidi-font-weight:normal;      font-style:italic;      mso-bidi-font-style:normal;} @page Section1      {size:612.0pt 792.0pt;      margin:70.85pt 70.85pt 70.85pt 70.85pt;      mso-header-margin:35.4pt;      mso-footer-margin:35.4pt;      mso-paper-source:0;} div.Section1      {page:Section1;}   --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:View&gt;Normal&lt;/w:View&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:HyphenationZone&gt;21&lt;/w:HyphenationZone&gt;   &lt;w:DoNotOptimizeForBrowser/&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  /* Font Definitions */ @font-face      {font-family:&quot;Baskerville Old Face&quot;;      mso-font-alt:&quot;Times New Roman&quot;;      mso-font-charset:0;      mso-generic-font-family:roman;      mso-font-pitch:variable;      mso-font-signature:3 0 0 0 1 0;} @font-face      {font-family:&quot;Arial Unicode MS&quot;;      mso-font-alt:&quot;MS Mincho&quot;;      mso-font-charset:128;      mso-generic-font-family:swiss;      mso-font-pitch:variable;      mso-font-signature:-1 -369098753 63 0 4129279 0;} @font-face      {font-family:&quot;\@Arial Unicode MS&quot;;      mso-font-charset:128;      mso-generic-font-family:swiss;      mso-font-pitch:variable;      mso-font-signature:-1 -369098753 63 0 4129279 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:&quot;Times New Roman&quot;;} p.Formuletitelfeestgids, li.Formuletitelfeestgids, div.Formuletitelfeestgids      {mso-style-name:&quot;Formuletitel feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      tab-stops:315.75pt;      font-size:14.0pt;      mso-bidi-font-size:16.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      text-decoration:underline;      text-underline:single;} p.Onderdeelformulefeestgids, li.Onderdeelformulefeestgids, div.Onderdeelformulefeestgids      {mso-style-name:&quot;Onderdeel formule feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      font-weight:bold;      mso-bidi-font-weight:normal;      font-style:italic;      mso-bidi-font-style:normal;} p.Titelfeestgids, li.Titelfeestgids, div.Titelfeestgids      {mso-style-name:&quot;Titel feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:24.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      color:#632423;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      font-weight:bold;      mso-bidi-font-weight:normal;      font-style:italic;      mso-bidi-font-style:normal;} @page Section1      {size:612.0pt 792.0pt;      margin:70.85pt 70.85pt 70.85pt 70.85pt;      mso-header-margin:35.4pt;      mso-footer-margin:35.4pt;      mso-paper-source:0;} div.Section1      {page:Section1;}   --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:View&gt;Normal&lt;/w:View&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:HyphenationZone&gt;21&lt;/w:HyphenationZone&gt;   &lt;w:DoNotOptimizeForBrowser/&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  /* Font Definitions */ @font-face      {font-family:&quot;Baskerville Old Face&quot;;      mso-font-alt:&quot;Times New Roman&quot;;      mso-font-charset:0;      mso-generic-font-family:roman;      mso-font-pitch:variable;      mso-font-signature:3 0 0 0 1 0;} @font-face      {font-family:&quot;Arial Unicode MS&quot;;      mso-font-alt:&quot;MS Mincho&quot;;      mso-font-charset:128;      mso-generic-font-family:swiss;      mso-font-pitch:variable;      mso-font-signature:-1 -369098753 63 0 4129279 0;} @font-face      {font-family:&quot;\@Arial Unicode MS&quot;;      mso-font-charset:128;      mso-generic-font-family:swiss;      mso-font-pitch:variable;      mso-font-signature:-1 -369098753 63 0 4129279 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:&quot;Times New Roman&quot;;} p.Formuletitelfeestgids, li.Formuletitelfeestgids, div.Formuletitelfeestgids      {mso-style-name:&quot;Formuletitel feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      tab-stops:315.75pt;      font-size:14.0pt;      mso-bidi-font-size:16.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      text-decoration:underline;      text-underline:single;} p.Onderdeelformulefeestgids, li.Onderdeelformulefeestgids, div.Onderdeelformulefeestgids      {mso-style-name:&quot;Onderdeel formule feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      font-weight:bold;      mso-bidi-font-weight:normal;      font-style:italic;      mso-bidi-font-style:normal;} p.Titelfeestgids, li.Titelfeestgids, div.Titelfeestgids      {mso-style-name:&quot;Titel feestgids&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:24.0pt;      font-family:&quot;Baskerville Old Face&quot;;      mso-fareast-font-family:&quot;Arial Unicode MS&quot;;      mso-bidi-font-family:&quot;Arial Unicode MS&quot;;      color:#632423;      mso-ansi-language:NL-BE;      mso-fareast-language:EN-US;      font-weight:bold;      mso-bidi-font-weight:normal;      font-style:italic;      mso-bidi-font-style:normal;} @page Section1      {size:612.0pt 792.0pt;      margin:70.85pt 70.85pt 70.85pt 70.85pt;      mso-header-margin:35.4pt;      mso-footer-margin:35.4pt;      mso-paper-source:0;} div.Section1      {page:Section1;}   -->   
Product_ID------=---------ID-------=------Product_ID

name                            name                   ID

Quantity/product                                      Quantity products

 

1 invoice can consist of several products.
1 product consists of several ingredients.
1 ingredient can avoid in several products.
1 product can be 'ordered' by several invoices.


I can see how much of each ingredient is needed in total for all invoices.
And I can see what ingredients are needed for each single invoice.
What I can not see is how much of each ingredient is required for each single invoice.

Should I fix this with a self join table or multikey? or something els?

Thanks.
A helping hand is enough!







Outcomes