 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / February 2008Rename Column Headers
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 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 Ron, I'm also trying to test this method. > 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. [quoted text clipped - 12 lines] > > Ron 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.  SignatureKARL 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 [quoted text clipped - 16 lines] > Red 18 23 21 > Gray 19 26 24 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. [quoted text clipped - 21 lines] > > Red 18 23 21 > > Gray 19 26 24 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. SignatureKARL DEWEY Build a little - Test a little > SQL for QryAll > SELECT QryCategoryTypes.Category, QryCategoryTypes.Type, [quoted text clipped - 40 lines] > > > Red 18 23 21 > > > Gray 19 26 24 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? [quoted text clipped - 44 lines] > > > > Red 18 23 21 > > > > Gray 19 26 24 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"); SignatureKARL DEWEY Build a little - Test a little > Local Issue Table > Created Date/Time Format Short Date [quoted text clipped - 67 lines] > > > > > Red 18 23 21 > > > > > Gray 19 26 24 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.