creating multiple records from a single record

[MarcoPerez21]

I have a tbl with the records that I need on a single row,
how can I grab one "field" at a time and create a record in another table with the information on that one field?


thanks in advance,
Marco

 

[BNPMike]

In SQL if you have five repeating fields you can write a five-stage UNION like:

select f1, '001' from tableA
UNION
select f2, '002' from tableA
UNION
...

and use that has the basis of an append query into the target table.

SQL doesn't have a looping construct so if the number of fields is variable, you'll need VBA.

 

[MarcoPerez21]

Mike,
this is the sql that i have types and it is giving me an error in the from clause.

select f1, "wk1_startDate" from tbl_week/year
union
select f2, "wk1_endDate" from tbl_week/year

thanks,
Marco

 

[BNPMike]

f1 and f2 were supposed to be names of your fields. '001' and '002' were calculated fields to make sure the extracts from each select were kept separate.

So for 2 passes it's like:

SELECT TRANS.PaymentGross, '001' AS Transtype
FROM TRANS
UNION
SELECT TRANS.ReceiptGross, '002' AS Transtype
FROM TRANS;

Then turn that into an append by putting something like this in front:

Insert into TARGET (GrossAmt,Transtype)

Do you see the idea?