ItemCount is essentially a counter of the records from the select statement. So, every new INSERT ... SELECT statement gets a new GroupCount (the next number up) and ItemCount represents the ID of the items in that new group. Does that make sense? - Thanks
>>> "Michael Dykman" <[EMAIL PROTECTED]> 1/12/07 8:13 AM >>> On 1/11/07, Ed Reed <[EMAIL PROTECTED]> wrote: > > I need some help creating an INSERT * SELECT statement that supplies a record counter in the returned items. Here's the task, > > I have t1 and t2. t1 is GroupCount, ItemCount, Field1, Field2, Field3. t2 is Field1, Field2, Field3. > > t1 has a multiple field unique key called Request that has GroupCount and ItemCount. I would like to create an INSERT * SELECT statement that takes the three fields from t2 and puts them into t1 and at the same time automatically fills the GroupCount and ItemCount field. My data in t1 should look like this. > > +----------+-----------+--------------+--------------+--------------+ > |GroupCount| ItemCount | Field1 | Field2 | Field3 | > +----------+-----------+--------------+--------------+--------------+ > | 1 | 1 | data from t2 | data from t2 | data from t2 | > | 1 | 2 | data from t2 | data from t2 | data from t2 | > | 1 | 3 | data from t2 | data from t2 | data from t2 | > | 2 | 1 | data from t2 | data from t2 | data from t2 | > | 2 | 2 | data from t2 | data from t2 | data from t2 | > | 3 | 1 | data from t2 | data from t2 | data from t2 | > | 3 | 2 | data from t2 | data from t2 | data from t2 | > | 3 | 3 | data from t2 | data from t2 | data from t2 | > | 3 | 4 | data from t2 | data from t2 | data from t2 | > | 3 | 5 | data from t2 | data from t2 | data from t2 | > | 3 | 6 | data from t2 | data from t2 | data from t2 | > | 4 | 1 | data from t2 | data from t2 | data from t2 | > | 4 | 2 | data from t2 | data from t2 | data from t2 | > | 4 | 3 | data from t2 | data from t2 | data from t2 | > | 4 | 4 | data from t2 | data from t2 | data from t2 | > | 4 | 5 | data from t2 | data from t2 | data from t2 | > | 4 | 6 | data from t2 | data from t2 | data from t2 | > | 4 | 7 | data from t2 | data from t2 | data from t2 | > | 5 | 1 | data from t2 | data from t2 | data from t2 | > | 5 | 2 | data from t2 | data from t2 | data from t2 | > | 6 | 1 | data from t2 | data from t2 | data from t2 | > | 6 | 2 | data from t2 | data from t2 | data from t2 | > | 6 | 3 | data from t2 | data from t2 | data from t2 | > +----------+-----------+--------------+--------------+--------------+ > > I can figure most of it out with the exception of the ItemCount value. What I have so far is this, > > INSERT INTO t1 (GroupCount, ItemCount, Field1, Field2, Field3) SELECT (SELECT Max(GroupCount)+1 From t1), ?????, Field1, Field2, Field3 FROM t2; > > The part with the ????? is what I can't figure out. > > Any ideas? > I'm not very clear what ItemCount is supposed to represent.. if you restate it, perhaps I can help -- - michael dykman - [EMAIL PROTECTED]
