insert in loop

[Guest]

I have two tables: Table1 with 13 fields and Table 2 with 2 fields that I
also have in Table1.
I run the query to generate Table2, every time I have different number of
records in Table2 - could be one record also.
I need to insert all records from Table2 into Table1 and fill out all other
fields that do not belong to Table 2 with the same data.
Otherwise, with my insert I add only records with 2 new fields.
Any help?

 

[Guest]

Hi viola,

Use an insert query similar to this (remembering to use every field required):

insert into Table1 (field1, field2, field3)
select Table2.field1, Table2.field2, FIELD3_CONSTANT_VALUE as field3
from Table2

Hope this helps.

Damian.

 

[Guest]

Hi Damian,
Thank you for your help, but I think I did not explain well my situation.
Here is my query:
INSERT INTO tblPromotions ( PromoKey, PromoID, ChainID, PromoDescription,
StoreID, ProductID, PromoPrice, PromoCentsoff, PromoStartDate, PromoEndDate,
Status, CreateDt, ModifyDt, Manager )
SELECT [tblLastPromoKey.LastKey]+1 AS Expr1, Forms![Promotions
1024].PromoIDDUP AS Expr2, Forms![Promotions 1024].NewChainID AS Expr3,
Forms![Promotions 1024].NewProductID AS Expr4, Forms![Promotions
1024].NewStoreID AS Expr5, AffectedItems.ItemID AS Expr6, Forms![Promotions
1024].NewPromoPrice AS Expr7, Forms![Promotions 1024].NewPromoCentsoff AS
Expr8, Forms![Promotions 1024].NewPromoStartDate AS Expr9, Forms![Promotions
1024].NewPromoEndDate AS Expr10, Forms![Promotions 1024].Status AS Expr11,
Forms![Promotions 1024].CreateDt AS Expr12, Forms![Promotions 1024].ModifyDt
AS Expr13, Forms![Promotions 1024].NewManager AS Expr14
FROM tblLastPromoKey, AffectedItems, tblCount
WHERE AffectedItems.ProdPriceGroupID=Forms![Promotions 1024]!NewProductID
ORDER BY AffectedItems.ItemID;
It is always inserts only one row into tblPromotions from AffectedItems,
even I have many records. Should I use VBA to create loop with counter?
Thank you