By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,414 Members | 2,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,414 IT Pros & Developers. It's quick & easy.

Stacked crosstab query - dynamically update columns

P: n/a
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!

May 28 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a

"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.
May 29 '07 #2

P: n/a
"m.wanstall" <m.********@gmail.comwrote in
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!
How about taking the datediff() between date() and datefield
this will give column headers of -3,-2,-1, 0 for the last three
and the current months?

I use a set of calculated labels on a form or report to reverse
the process and show the correct period.Use the dateadd()
function.


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

May 29 '07 #3

P: n/a
I'd reccomend not using a handicapped database

move to SQL Server
"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...
"m.wanstall" <m.********@gmail.comwrote in
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!

How about taking the datediff() between date() and datefield
this will give column headers of -3,-2,-1, 0 for the last three
and the current months?

I use a set of calculated labels on a form or report to reverse
the process and show the correct period.Use the dateadd()
function.


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com
Jun 25 '07 #4

P: n/a
"Tom Wickerqueer" <tw@ewu.eduwrote in news:OQtGdKwtHHA.488
@TK2MSFTNGP04.phx.gbl:
I'd reccomend not using a handicapped database

move to SQL Server
What has SQL server got to do with this? -Nothing.
Go away, Don;t go away mad, just go away. [plonk]

>

"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...
>"m.wanstall" <m.********@gmail.comwrote in
news:11**********************@w5g2000hsg.googlegr oups.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!

How about taking the datediff() between date() and datefield
this will give column headers of -3,-2,-1, 0 for the last
three
>and the current months?

I use a set of calculated labels on a form or report to
reverse
>the process and show the correct period.Use the dateadd()
function.


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com



--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Jun 25 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.