SteveEClarke (9/27/2012)

---

Okay - my challenge today, is that I need to create some labels for a "Picking List" ( BOM ) - One lable per component required.

Table ONE
WorksOrder
ParentPart
QtyToMake

Table TWO
WorksOrder
Component
QtyPer

View will contain repeated records for ( based on QtyToMake x QtyPer )
WorksOrder
ParentPart
Component

So if I need to make 3 ParentParts and it requires 2 Components per Parent - then the view ( or new table ) would contain ; WorksOrer, ParentPart, Component, ( possibly counter ) - 6 times !

From this view I can then create a report and print off the records for labels.

I am choosing to create a VIEW as I need to produce a report which unfortunately would be more complicated passing a field value as the number of records to print.

SteveEClarke (9/27/2012)

---

Okay - my challenge today, is that I need to create some labels for a "Picking List" ( BOM ) - One lable per component required.

Table ONE
WorksOrder
ParentPart
QtyToMake

Table TWO
WorksOrder
Component
QtyPer

SteveEClarke (9/27/2012)

---

Sorry - the question is ;

How can I create a view inserting repeated records based on the calculation of two fields;

MysteryJimbo (9/27/2012)

---

Also I think your table definition is not ideal. I'm assuming each "Part" is made using 1 or more "Components" and there's potential for each "Component" type to be used in more than one "Part". In which case I'd have four tables.

Table ONE
WorksOrder
ParentPartId
QtyToMake

Table TWO
ParentPartId
ParentPart

Table THREE
ParentPartId
CompentID
QtyPer

Table FOUR
CompentID
Component

J Livingston SQL (9/29/2012)

---

something along these lines......??

--=== this relies on a "numbers" or "tally" table
--=== http://www.sqlservercentral.com/articles/T-SQL/62867/ by Jeff Moden

; WITH CTE as
(
SELECT dbo.WIPMaster.WorksOrder , 
       dbo.WIPMaster.Parent , 
       dbo.WIPMaterials.Components , 
       dbo.WIPMaster.QtytoMake * dbo.WIPMaterials.Qtyper AS cnt
  FROM
       dbo.WIPMaster INNER JOIN dbo.WIPMaterials ON dbo.WIPMaster.WorksOrder = dbo.WIPMaterials.WorksOrder
)

SELECT c.WorksOrder , 
       c.Parent , 
       c.Components , 
       T.N
  FROM
       cte c INNER JOIN Tally t ON c.cnt >= T.N