Fixed column names from a crosstab query.

[Guest]

I use a crosstab query to look at one week's worth of data each day. The
column headings created by the crosstab query are dates for example, 4/5/2005
4/6/2005 4/7/2005 etc. If I use the dates on a form, tomorrow they will be
different since they will all be incremented by one day. I am looking for
some way to dynamically change the column names to Day1 Day2 Day3 etc. so the
form will always work. The problem is that the crosstab column names change
every day. Help!!

 

[Guest]

Hi Dave,

You can enter fixed column headings in the column headings property of the
crosstab query. But, in order to get the data in the correct columns, you
will need to create an expression which will generate the proper column
heading for the records. For example, in a simple case, you could use
something like:

ColHeading: "Day" & Format([YourDateField]-Date(),"0")

or something like that. The exact expression would depend on whether your
data includes a time portion, and if so how you want to handle the rounding,
etc. In fact, maybe the DateDiff() function would be better than the
Format() function, but you could experiment to get the proper results.

The key will be to get the expression result (which will be defined as the
column heading) to match your pre-defined column headings.

HTH, Ted Allen

 

[Guest]

Ted,

Your suggestion worked perfectly! Thanks for your help.

Dave T

Hi Dave,

You can enter fixed column headings in the column headings property of the
crosstab query. But, in order to get the data in the correct columns, you
will need to create an expression which will generate the proper column
heading for the records. For example, in a simple case, you could use
something like:

ColHeading: "Day" & Format([YourDateField]-Date(),"0")

or something like that. The exact expression would depend on whether your
data includes a time portion, and if so how you want to handle the rounding,
etc. In fact, maybe the DateDiff() function would be better than the
Format() function, but you could experiment to get the proper results.

The key will be to get the expression result (which will be defined as the
column heading) to match your pre-defined column headings.

HTH, Ted Allen

I use a crosstab query to look at one week's worth of data each day. The
column headings created by the crosstab query are dates for example, 4/5/2005
4/6/2005 4/7/2005 etc. If I use the dates on a form, tomorrow they will be
different since they will all be incremented by one day. I am looking for
some way to dynamically change the column names to Day1 Day2 Day3 etc. so the
form will always work. The problem is that the crosstab column names change
every day. Help!!

 

[Guest]

My pleasure Dave, glad it worked for you.

Ted,

Your suggestion worked perfectly! Thanks for your help.

Dave T

Hi Dave,

You can enter fixed column headings in the column headings property of the
crosstab query. But, in order to get the data in the correct columns, you
will need to create an expression which will generate the proper column
heading for the records. For example, in a simple case, you could use
something like:

ColHeading: "Day" & Format([YourDateField]-Date(),"0")

or something like that. The exact expression would depend on whether your
data includes a time portion, and if so how you want to handle the rounding,
etc. In fact, maybe the DateDiff() function would be better than the
Format() function, but you could experiment to get the proper results.

The key will be to get the expression result (which will be defined as the
column heading) to match your pre-defined column headings.

HTH, Ted Allen

I use a crosstab query to look at one week's worth of data each day. The
column headings created by the crosstab query are dates for example, 4/5/2005
4/6/2005 4/7/2005 etc. If I use the dates on a form, tomorrow they will be
different since they will all be incremented by one day. I am looking for
some way to dynamically change the column names to Day1 Day2 Day3 etc. so the
form will always work. The problem is that the crosstab column names change
every day. Help!!