Trouble inserting records into a table 1

[teach314]

hi to all

I start with a table like this...

  A   B   C     D     E
------------------------
  1   1   2    45   782
  1   1   3   199     6
  1   1   6    21    12

  1   2   1    34   552
  1   2   2    77    71
  1   2   4   110   322

  1   3   2   222   333  ... and so on

Note that records come in groups of 3. In each group of 3 records, C can be chosen from 1, 2, 3, 4, 5 or 6, with no repeats, and is listed in ascending order.

My problem: I need to add the MISSING values of C to each group. The corresponding values of A and B must also be filled in, but D and E are to be left blank for now. The result should look like below.

  A   B   C     D     E
------------------------
  1   1   1
  1   1   2    45   782
  1   1   3   199     6
  1   1   4
  1   1   5
  1   1   6    21    12

  1   2   1    34   552
  1   2   2    77    71
  1   2   3
  1   2   4   110   322
  1   2   5
  1   2   6

  1   3   1
  1   3   2   222   333   ... and so on

I'm sure this is straight forward, but I'm having a brain cramp.
many thanks

 

[dhookom]

First create a totals query that groups by columns A and B. Save the query as qgrpTeach123.

SELECT A, B
FROM LikeThis
GROUP BY A, B;

Then use a table [tblNums] with a numeric field [Num] and numbers from at least 1 to 6. Then create a cartesian query [qcarTeach123] of qgrpTeach123 and tblNums:

SELECT qgrpTeach314.A, qgrpTeach314.B, tblNums.Num
FROM qgrpTeach314, tblNums
WHERE tblNums.Num Between 1 And 6;

Then create a final query joining the cartesian query to the original table:

SELECT qcarTeach123.A, qcarTeach123.B, qcarTeach123.Num, LikeThis.D, LikeThis.E
FROM qcarTeach123 LEFT JOIN LikeThis ON (qcarTeach123.Num = LikeThis.C)
 AND (qcarTeach123.B = LikeThis.B) AND (qcarTeach123.A = LikeThis.A)
ORDER BY qcarTeach123.A, qcarTeach123.B, qcarTeach123.Num;

Duane
Hook'D on Access
MS Access MVP

 

[teach314]

excellent.

Thanks, Duane