Creating a table from series of Crosstab queries

[Sean M.]

I have a series of 10 crosstab queries that gather various per month counts.
The output of each crosstab is:
1 2 3 4 5 6 7 8 9 10 11 12
# # # # # # # # # # # #

My goal is to create a table with the output of each of the crosstab queries
as a separate record in the table. This table will then be queried through a
VB script to push the values to an Excel file for further processing.

The only trouble I'm having is total brain lock as far as how I can create
the table from the crosstabs. Can anyone help?

Thanks in advance!

Sean

 

[Duane Hookom]

You can use your crosstab as a source for a make table or append query.

 

[Sean M.]

Thank you for the reply Duane. I've gotten that far...it may be that I need
to look at the VB code again and figure out a different way to push the
numbers. I'm not well-versed in VB and a friend created the code for me and
I've been using that as a template to create the table to make the code work.

The code looks for a row key / key id to know that it is pulling the right
information. Is there a way that I can use, say, a blank table -- 1-12 as
fields for each row (which is what I have now) but add a key in each row,
that will remain the same forever, then use the crosstabs to populate the
table?

If this is not possible I may simply have to do the series of append queries
in a particular order then simply have the code rewritten to accommodate that
rather than searching for a key id.

Thanks for your help!

Sean

 

[Duane Hookom]

You can't use a crosstab in an update query. You can append from a crosstab
or write code to loop through one or two recordsets to update one from the
other.

Your "big picture" requirements have never been explained. A better solution
might be available if we knew what you were attempting to do.