[AccessD] Appending to table

Hi Tim

That can be done by a query with a cartesian join.

First you need a table for multiplication, tbzFactor.
It has one Integer field, Factor. Fill it with values from 1 to 10 or
whatever your maximum number of weeks will be, say 100.

Then, all you need is this query to display your missing rows:

<SQL>

  SELECT
    tblWeeks.ItemNo,
    tblWeeks.LotNo,
    tblWeeks.SeqNo,
    tblWeeks.Weeks,
    tbzMultiply.Factor AS WkNo
  FROM
    tblWeeks,
    tbzMultiply
  WHERE
    tbzMultiply.Factor < Weeks
      AND
    tblWeeks.WkNo = 0;

</SQL>

Note the no inner or outer join is specified.

To append the missing rows directly, turn the select query into an
append query:

<SQL>

  INSERT INTO
    tblWeeks (
      ItemNo,
      LotNo,
      SeqNo,
      Weeks,
      WkNo )
  SELECT
    tblWeeks.ItemNo,
    tblWeeks.LotNo,
    tblWeeks.SeqNo,
    tblWeeks.Weeks,
    tbzMultiply.Factor AS WkNo
  FROM
    tblWeeks,
    tbzMultiply
  WHERE
    tbzMultiply.Factor < Weeks
      AND
    tblWeeks.WkNo = 0;

</SQL>

To prevent multiple inserts from running the append query more than
once, add one unique index on all five fields of table tblWeeks.

/gustav


> I have a table with the following data:

> ItemNo          LotNo           SeqNo           Weeks           WkNo
> 2556            401             1               3               0
> 2556            401             2               3               0
> 2556            401             3               6               0

> What I need to do is to append records to the table so that the WkNo is
> filled in starting at 1 to Weeks-1.  For example:
> ItemNo          LotNo           SeqNo           Weeks           WkNo
> 2556            401             1               3               0
> 2556            401             1               3               1
> 2556            401             1               3               2
> 2556            401             2               3               0
> 2556            401             2               3               1
> 2556            401             2               3               2
> 2556            401             3               6               0
> 2556            401             3               6               1
> 2556            401             3               6               2
> 2556            401             3               6               3
> 2556            401             3               6               4
> 2556            401             3               6               5

> What is the best way for this to be accomplished?  I'm blank at the moment.

> Tim Thiessen