Dynamicaly Change Column Headings On Report

[pjd218]

I have a select query that is based on two crosstab queries.

One of the crosstabs uses the * to pull in all fields to the query. This is needed because the table data is not constant (i.e new products can be added, removed, etc.)

The select query gives me the data I need. However, the field names' syntax looks like this <QueryName>.FieldName

As stated above, this is a result of using the * in one of the queries.

Is there a way to strip/remove the QueryName portion of the field label so all that displays on the report is the FieldName portion of the label?

 

[dhookom]

Do your crosstabs return some of the same field names? If so, change them in one of the crosstabs so you don't have duplicates. This should remove the <queryname>.


Duane
Hook'D on Access
MS Access MVP

 

[pjd218]

dhookum,

I'll take a look. I only know of one duplicate field name from each cross tab.

By the way, you advice on this forum has been stellar. I also took a look at your dynamic report generator. Very nice.

 

[pjd218]

dhookum,

No joy.

Can't avoid the duplicates.

Any way to parse the field name using sql or vba?

 

[dhookom]

Why can't you avoid the duplicates? Can you share the SQL views and what makes you think you can't avoid duplicate field names?

Duane
Hook'D on Access
MS Access MVP

 

[pjd218]

dhookum,

I guess my understanding is a bit off.

In order to link the two xtabs, I need a common field, correct?

All of the data is coming from the same table, but I am using one xtab to genrate sales for each product type and then volume for each product type. The common link between the two xtabs is the sales date.

I can have mmany (or none) product sales for any given date.

I think I am making this more difficult that it has to be. My ultimate goal is to produce a daily sales ledger showing sales by each product type, volume by each product type, and other misc info relating to the sales date.

The use of the xtabs allows flexibility in case the products change (i.e. added or deleted)so in a sense, the xtabs create a "dynamic" report.

 

[dhookom]

You need a common value in a field to link the two crosstabs. The name of the column doesn't have to be the same. You should be able to alias the name of one of the columns/fields.

You can get a multi-value crosstab using the solution at faq701-4524. There are a number of solutions for create dynamic crosstab reports. You haven't provided any background on your columns and how you are generating the report.

Duane
Hook'D on Access
MS Access MVP

 

[pjd218]

Duane,

How do you alias a column using the "*" in the query. I need to include all of the fields from both xtabs.

Thanks again for the help.

 

[dhookom]

Change it in the crosstab.

Duane
Hook'D on Access
MS Access MVP