"m.wanstall" <m.********@gmail.comwrote in message
news:11**********************@w5g2000hsg.googlegro ups.com...
I have a crosstab query that compiles data for Months of the year. I
have a stacked select query on top of that crosstab query that uses
the latest 2 months data and exports it to a fixed length flat file.
Ideally I would like to be able to just select the Last 2 Columns of
the Crosstab query as inputs to the Select query WITHOUT having to
go
in month after month and manually change it...
I may be asking a bit much here but is there a way to make this
happen?? I.e. this month we would be using the columns [0704] and
[0705] and next month we would use [0705] and [0706] because we
would
have a new months worth of data injected into the original tables.
The only reason I want this is because these queries do a heck of a
lot of heavy lifting and they are slugs to open/modify/save so I'd
prefer to just have to export it.
Any help is greatly appreciated!
m.wanstall,
Crosstab queries create dynamic column names based on the values of a
column in a table.
Every time such a query runs, the column names may be different.
You want to create a special query that dynamically reconfigures
itself so that it can always pick out the "correct" columns from the
ever-changing results of the crosstab.
The bad news is that there is no easy way to do that.
I can think of a way to alter your "second query" dynamically via VBA,
but it would be pointlessly complicated.
I recommend creating a VBA routine that is based on creating a
recordset using the crosstab query in question.
Using VBA, you can dynamically extract only the "last two months"
columns in the recordset and put the data into a new table you create
in the VBA code. Then run your "second query" on the new table. This
way, you never need to change your "second query".
There are various things you can do with this "intermediate" table.
You can delete it every time you run the VBA code so that it is out of
the way. You also can rename the last used "new table" by appending
the date and time (if the table actually exists) at the beginning of
your VBA code so that you keep several past copies. The second option
allows you to keep a generational history of the information you are
drawing on, so you have it later, but it also takes up more space.
Possibly Unnecessary Commentary:
I recommend examining your db schema for possible redesign of your
crosstab query, second query (with an eye to eliminating it), and
possibly the whole db. You shouldn't have to use this type of
indirection (using VBA to create intermediate quasi-temporary tables)
if your db and queries are designed correctly. That goes far beyond
the scope of your question, though.
Sincerely,
Chris O.
