query2table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 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 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
 
Posting your message three times will not get your answer three times
faster.


message 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 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 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
 
The exact error of the online news reader is and was:
Regards
Michael
************Cut*****************************************
An error occurred while sending your post
 
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?
Click to expand...

Change the query into a MakeTable query, or base a MakeTable query on
the stored query.

To get the missing weeks included, create a small auxiliary table with
every week in it, and join it to your query using an Outer Join -
check the join line in the query design window and select the option
"Show all records from AllWeeks and matching records from <your
table>".

And I join you in hoping that Microsoft gets the bogus error message
problem fixed... soon!!
 
Back
Top