Rename Column Headers

[Qaspec]

I have a query that I would like to rename the columns depending on the
contents of a control on a form. For example my query [QryAll] contains
headers titled week1, week2, and week3 and the form [MainForm] has textboxes
[Start1], [Start2] and [Start3] which correspond to a date that has been
entered into the form. I would like the date that has been entered into the
form to show as my column header for week1 week2 and week3 in the query.
Thanks for any help in advance.

Here's what I hope to see -

MainForm
Start1 = 1/25/08
Start2 = 2/1/08
Sart3 = 2/8/08

Qry All
Type 1/25/08 2/1/08 2/8/08
Blue 15 25 20
Red 18 23 21
Gray 19 26 24

 

[Ron2006]

Here is a way that I have used and it works because I tend to NOT open
a query form except as a subform on an unbound form.

in the OnLoad event of the display form (the unbound form with the
QueryAll as a subform on it.
me.queryAllSubformname.form.labelnameofcolumn1.caption = forms!
[Main Form]!Start1
me.queryAllSubformname.form.labelnameofcolumn2.caption = forms!
[Main Form]!Start2
me.queryAllSubformname.form.labelnameofcolumn3.caption = forms!
[Main Form]!Start3

I believe the .form. is necessary but it may not be, autocomplete will
help you get to the proper format.

Ron

 

[KARL DEWEY]

It sounds like your table structure is not normalized but is a spreadsheet
layout. Post your table structure and sample data.

I think a crosstab query will do what you want with the correct table design.

 

[Qaspec]

SQL for QryAll
SELECT QryCategoryTypes.Category, QryCategoryTypes.Type,
QrySubTrendWeek0c.SumOfCount AS Week0, QrySubTrendWeek1c.SumOfCount AS Week1,
QrySubTrendWeek2c.SumOfCount AS Week2, QrySubTrendWeek3c.SumOfCount AS Week3
FROM (((QryCategoryTypes LEFT JOIN QrySubTrendWeek0c ON
(QryCategoryTypes.Category = QrySubTrendWeek0c.Category) AND
(QryCategoryTypes.Type = QrySubTrendWeek0c.Type)) LEFT JOIN QrySubTrendWeek1c
ON (QryCategoryTypes.Category = QrySubTrendWeek1c.Category) AND
(QryCategoryTypes.Type = QrySubTrendWeek1c.Type)) LEFT JOIN QrySubTrendWeek2c
ON (QryCategoryTypes.Category = QrySubTrendWeek2c.Category) AND
(QryCategoryTypes.Type = QrySubTrendWeek2c.Type)) LEFT JOIN QrySubTrendWeek3c
ON (QryCategoryTypes.Category = QrySubTrendWeek3c.Category) AND
(QryCategoryTypes.Type = QrySubTrendWeek3c.Type);

This doesn't come out exactly as my example is laid out but i tried to
simplify the example some. There are actually 4 weeks instead of 3 and there
is a category column and a type column. Category is color and type is the
various colors.

It sounds like your table structure is not normalized but is a spreadsheet
layout. Post your table structure and sample data.

I think a crosstab query will do what you want with the correct table design.

--
KARL DEWEY
Build a little - Test a little

I have a query that I would like to rename the columns depending on the
contents of a control on a form. For example my query [QryAll] contains
headers titled week1, week2, and week3 and the form [MainForm] has textboxes
[Start1], [Start2] and [Start3] which correspond to a date that has been
entered into the form. I would like the date that has been entered into the
form to show as my column header for week1 week2 and week3 in the query.
Thanks for any help in advance.

Here's what I hope to see -

MainForm
Start1 = 1/25/08
Start2 = 2/1/08
Sart3 = 2/8/08

Qry All
Type 1/25/08 2/1/08 2/8/08
Blue 15 25 20
Red 18 23 21
Gray 19 26 24

 

[Qaspec]

Ron,

I'm also trying to test this method.

 

[KARL DEWEY]

You are building a query for each week. Do you also have a different table
for each week?
I asked that you post your table structure and sample data.

--
KARL DEWEY
Build a little - Test a little

SQL for QryAll
SELECT QryCategoryTypes.Category, QryCategoryTypes.Type,
QrySubTrendWeek0c.SumOfCount AS Week0, QrySubTrendWeek1c.SumOfCount AS Week1,
QrySubTrendWeek2c.SumOfCount AS Week2, QrySubTrendWeek3c.SumOfCount AS Week3
FROM (((QryCategoryTypes LEFT JOIN QrySubTrendWeek0c ON
(QryCategoryTypes.Category = QrySubTrendWeek0c.Category) AND
(QryCategoryTypes.Type = QrySubTrendWeek0c.Type)) LEFT JOIN QrySubTrendWeek1c
ON (QryCategoryTypes.Category = QrySubTrendWeek1c.Category) AND
(QryCategoryTypes.Type = QrySubTrendWeek1c.Type)) LEFT JOIN QrySubTrendWeek2c
ON (QryCategoryTypes.Category = QrySubTrendWeek2c.Category) AND
(QryCategoryTypes.Type = QrySubTrendWeek2c.Type)) LEFT JOIN QrySubTrendWeek3c
ON (QryCategoryTypes.Category = QrySubTrendWeek3c.Category) AND
(QryCategoryTypes.Type = QrySubTrendWeek3c.Type);

This doesn't come out exactly as my example is laid out but i tried to
simplify the example some. There are actually 4 weeks instead of 3 and there
is a category column and a type column. Category is color and type is the
various colors.

It sounds like your table structure is not normalized but is a spreadsheet
layout. Post your table structure and sample data.

I think a crosstab query will do what you want with the correct table design.

--
KARL DEWEY
Build a little - Test a little

I have a query that I would like to rename the columns depending on the
contents of a control on a form. For example my query [QryAll] contains
headers titled week1, week2, and week3 and the form [MainForm] has textboxes
[Start1], [Start2] and [Start3] which correspond to a date that has been
entered into the form. I would like the date that has been entered into the
form to show as my column header for week1 week2 and week3 in the query.
Thanks for any help in advance.

Here's what I hope to see -

MainForm
Start1 = 1/25/08
Start2 = 2/1/08
Sart3 = 2/8/08

Qry All
Type 1/25/08 2/1/08 2/8/08
Blue 15 25 20
Red 18 23 21
Gray 19 26 24

 

[Qaspec]

Local Issue Table
Created Date/Time Format Short Date
Created By Text
Communication Type Text
Category Text
Type Text
Resolution Text
Comments Text

The Table is Formatted to show the Date as Short Date but also contains Time
Data

Sample Data From Table
Created|Created By|Communication Type|Category| Type |Resolution|Comments|
1/27/08 MG Phone Color Red
Sold None
1/28/08 DC Phone Color Blue
Open None
1/29/08 HL Phone Size Large
Open None

The queries for each week come from the same table.

You are building a query for each week. Do you also have a different table
for each week?
I asked that you post your table structure and sample data.

--
KARL DEWEY
Build a little - Test a little

SQL for QryAll
SELECT QryCategoryTypes.Category, QryCategoryTypes.Type,
QrySubTrendWeek0c.SumOfCount AS Week0, QrySubTrendWeek1c.SumOfCount AS Week1,
QrySubTrendWeek2c.SumOfCount AS Week2, QrySubTrendWeek3c.SumOfCount AS Week3
FROM (((QryCategoryTypes LEFT JOIN QrySubTrendWeek0c ON
(QryCategoryTypes.Category = QrySubTrendWeek0c.Category) AND
(QryCategoryTypes.Type = QrySubTrendWeek0c.Type)) LEFT JOIN QrySubTrendWeek1c
ON (QryCategoryTypes.Category = QrySubTrendWeek1c.Category) AND
(QryCategoryTypes.Type = QrySubTrendWeek1c.Type)) LEFT JOIN QrySubTrendWeek2c
ON (QryCategoryTypes.Category = QrySubTrendWeek2c.Category) AND
(QryCategoryTypes.Type = QrySubTrendWeek2c.Type)) LEFT JOIN QrySubTrendWeek3c
ON (QryCategoryTypes.Category = QrySubTrendWeek3c.Category) AND
(QryCategoryTypes.Type = QrySubTrendWeek3c.Type);

This doesn't come out exactly as my example is laid out but i tried to
simplify the example some. There are actually 4 weeks instead of 3 and there
is a category column and a type column. Category is color and type is the
various colors.

It sounds like your table structure is not normalized but is a spreadsheet
layout. Post your table structure and sample data.

I think a crosstab query will do what you want with the correct table design.

--
KARL DEWEY
Build a little - Test a little


:

I have a query that I would like to rename the columns depending on the
contents of a control on a form. For example my query [QryAll] contains
headers titled week1, week2, and week3 and the form [MainForm] has textboxes
[Start1], [Start2] and [Start3] which correspond to a date that has been
entered into the form. I would like the date that has been entered into the
form to show as my column header for week1 week2 and week3 in the query.
Thanks for any help in advance.

Here's what I hope to see -

MainForm
Start1 = 1/25/08
Start2 = 2/1/08
Sart3 = 2/8/08

Qry All
Type 1/25/08 2/1/08 2/8/08
Blue 15 25 20
Red 18 23 21
Gray 19 26 24

 

[Ron2006]

The situation that I use it for is that I have a query that counts the
number of assignments by employee by date. However for the purposes of
what I am showing I need to list the employees and then the number of
assignments for just the last 5 days so that the user can see how many
assignments each employee had for the last 5 days.

So I use a query to get the last 5 dates and change the captions for
each date column. The data is normalized etc just fine but the display
is easier to read and more meaningfull (with a limited amount of
desktop to display it on) if the column titles are the dates. It ends
up looking exactly like your example but the columns are for days not
weeks and the colors are employee names.

Ron

 

[KARL DEWEY]

Try this query ---
TRANSFORM Count([Local Issue Table].Category) AS CountOfCategory
SELECT [Local Issue Table].Type
FROM [Local Issue Table]
GROUP BY [Local Issue Table].Type
PIVOT Format([Created],"Short Date");

--
KARL DEWEY
Build a little - Test a little

Local Issue Table
Created Date/Time Format Short Date
Created By Text
Communication Type Text
Category Text
Type Text
Resolution Text
Comments Text

The Table is Formatted to show the Date as Short Date but also contains Time
Data

Sample Data From Table
Created|Created By|Communication Type|Category| Type |Resolution|Comments|
1/27/08 MG Phone Color Red
Sold None
1/28/08 DC Phone Color Blue
Open None
1/29/08 HL Phone Size Large
Open None

The queries for each week come from the same table.

You are building a query for each week. Do you also have a different table
for each week?
I asked that you post your table structure and sample data.

--
KARL DEWEY
Build a little - Test a little

SQL for QryAll
SELECT QryCategoryTypes.Category, QryCategoryTypes.Type,
QrySubTrendWeek0c.SumOfCount AS Week0, QrySubTrendWeek1c.SumOfCount AS Week1,
QrySubTrendWeek2c.SumOfCount AS Week2, QrySubTrendWeek3c.SumOfCount AS Week3
FROM (((QryCategoryTypes LEFT JOIN QrySubTrendWeek0c ON
(QryCategoryTypes.Category = QrySubTrendWeek0c.Category) AND
(QryCategoryTypes.Type = QrySubTrendWeek0c.Type)) LEFT JOIN QrySubTrendWeek1c
ON (QryCategoryTypes.Category = QrySubTrendWeek1c.Category) AND
(QryCategoryTypes.Type = QrySubTrendWeek1c.Type)) LEFT JOIN QrySubTrendWeek2c
ON (QryCategoryTypes.Category = QrySubTrendWeek2c.Category) AND
(QryCategoryTypes.Type = QrySubTrendWeek2c.Type)) LEFT JOIN QrySubTrendWeek3c
ON (QryCategoryTypes.Category = QrySubTrendWeek3c.Category) AND
(QryCategoryTypes.Type = QrySubTrendWeek3c.Type);

This doesn't come out exactly as my example is laid out but i tried to
simplify the example some. There are actually 4 weeks instead of 3 and there
is a category column and a type column. Category is color and type is the
various colors.



:

It sounds like your table structure is not normalized but is a spreadsheet
layout. Post your table structure and sample data.

I think a crosstab query will do what you want with the correct table design.

--
KARL DEWEY
Build a little - Test a little


:

I have a query that I would like to rename the columns depending on the
contents of a control on a form. For example my query [QryAll] contains
headers titled week1, week2, and week3 and the form [MainForm] has textboxes
[Start1], [Start2] and [Start3] which correspond to a date that has been
entered into the form. I would like the date that has been entered into the
form to show as my column header for week1 week2 and week3 in the query.
Thanks for any help in advance.

Here's what I hope to see -

MainForm
Start1 = 1/25/08
Start2 = 2/1/08
Sart3 = 2/8/08

Qry All
Type 1/25/08 2/1/08 2/8/08
Blue 15 25 20
Red 18 23 21
Gray 19 26 24