M
Michael Firsching
Hi everybody,
I am searching for a way to copy the structure/data of a query into a new table for further processing, but I can not find any elegant way to do so. Is there any?
I have the following query recordset, which is based on a pivot table:
Week | Year | Sum( Fld1) | Sum( Fld2) | Sum(fld3) ......(column# is dynamic)
1 2004 5 0 0 ......
3 2004 3 ....... ...... ......
5 ..... ...........
.......
I want to copy its structure/data into a table and enter 0 Values for weeks that are not in the query. To achieve this, I can make a dynamic CREATE TABLE statement, cycle through weeks and years and copy the query data or missing rows into the new table. Afterwards i have persistent data that can be used for further processing. But i wonder, is there another simpler way?
Two things would make life easier and my VBA Code more structured:
1)Is there a way to insert the missing weeks with sql?
2)You can easily copy the structure of a table using the following code:
DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, strOldTableName, strNewTableName, True
Can this be also done for queries?
Thanks a lot for any help!
Best Regards,
Michael
I am searching for a way to copy the structure/data of a query into a new table for further processing, but I can not find any elegant way to do so. Is there any?
I have the following query recordset, which is based on a pivot table:
Week | Year | Sum( Fld1) | Sum( Fld2) | Sum(fld3) ......(column# is dynamic)
1 2004 5 0 0 ......
3 2004 3 ....... ...... ......
5 ..... ...........
.......
I want to copy its structure/data into a table and enter 0 Values for weeks that are not in the query. To achieve this, I can make a dynamic CREATE TABLE statement, cycle through weeks and years and copy the query data or missing rows into the new table. Afterwards i have persistent data that can be used for further processing. But i wonder, is there another simpler way?
Two things would make life easier and my VBA Code more structured:
1)Is there a way to insert the missing weeks with sql?
2)You can easily copy the structure of a table using the following code:
DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, strOldTableName, strNewTableName, True
Can this be also done for queries?
Thanks a lot for any help!
Best Regards,
Michael