Stacked crosstab query - dynamically update columns

[m.wanstall]

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!

 

[Duane Hookom]

I'm not totally sure what you are asking other than maybe generating month
columns and wanting to return the most recent two months. If this is correct,
consider using a crosstab with relative months. You column heading expression
would be something like:
ColHead: "M" & DateDiff("m", [SomeDateField],Date())
Then set the column headings property to:
Column Headings: "M0","M1","M2"
Then get rid of the column you don't want.

 

[m.wanstall]

I'm not totally sure what you are asking other than maybe generating month
columns and wanting to return the most recent two months. If this is correct,
consider using a crosstab with relative months. You column heading expression
would be something like:
ColHead: "M" & DateDiff("m", [SomeDateField],Date())
Then set the column headings property to:
Column Headings: "M0","M1","M2"
Then get rid of the column you don't want.
--
Duane Hookom
Microsoft Access MVP

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!- Hide quoted text -

- Show quoted text -

Thanks Duane,

That looks like it will do the trick nicely! I'll give it a try this
afternoon.

Chris2,

We are constrained by our overseas counterparts to use the (however
badly designed) databases that they produce company-wide. I want to
cry every time I see the structure of some of this stuff...but short
of bashing my head daily against a brick wall there's nothing much I
can do about it!

I am also stuck in a world that insists on using Access even though we
have spare MS SQL licenses sitting around and when some of your
queries take 10+ minutes to run ad-hoc reports...you've really got to
wonder.

 

[Chris2]

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.

 

[Chris2]

Chris2,

We are constrained by our overseas counterparts to use the (however
badly designed) databases that they produce company-wide. I want to
cry every time I see the structure of some of this stuff...but short
of bashing my head daily against a brick wall there's nothing much I
can do about it!

I am also stuck in a world that insists on using Access even though we
have spare MS SQL licenses sitting around and when some of your
queries take 10+ minutes to run ad-hoc reports...you've really got to
wonder.

m.wanstall

Duane's answer was better than mine all the way around. I wish I had
thought of it, I should have, it's not like I didn't know about the
column-renaming feature for crosstabs.

I understand about not being able to change your db. I have seen
horrifying db design--a direct port of mainframe flat-files into a
relational database with no normalization of any kind--in major supply
chain software that sells for *lots* of money, so I definitely
understand.


Sincerely,

Chris O.

 

[Bob Quintal]

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.

 

[Tom Wickerqueer]

I'd reccomend not using a handicapped database

move to SQL Server

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]

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]

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.