Column Header In CrossTab Query

Jan 18, 2006


Is it possible to choose a runnig number as a column header for a crosstab query header as at the moment I am using the date as below. I would like the column headers to be for example col1 ,col2 ,col3 ,col4 etc is it possible to give access an array or varible to use?

Example output

22130 8 5


TRANSFORM Sum(LaborDB.Hours) AS SumOfHours
SELECT LaborDB.ProjID, Sum(LaborDB.Hours) AS T-Hours
WHERE (((LaborDB.LogID)=166) AND ((LaborDB.Date) Between #13/06/2005# And #15/06/2005#))
PIVOT Format([Date],"Short Date");

Thanks for your help


View Replies


Crosstab Query - Custom Header Label

Jun 15, 2005

Is it possible to create a custom label for a column header in a crosstab query?

My crosstab query displays sales totals for a particular sales rep number (rep # 102 in this case), by month. For example:


I would like the column to be labeled "Sales" instead of "102" - is that possible?

Thanks for any help you can provide!!! :o

View 7 Replies View Related

Crosstab Query Contains <> Column

Nov 29, 2007

I have constructed a crosstab query in order to populate a stacked column chart.

Everything is fine except the crosstab query seems to contain an extra blank column with the header <>. This is therefore appearing in the chart when I don't want it to.

As the crosstab query is itself based on a select query I think the <> column is a reference to a blank row in the select query.

The blank row in the select query is itself the "new record" row from the underlying table.

Whether significant or not one of the data types is an autonumber.

To solve my problem I think I need to find a criteria to use in the select query that would exclude the new record row. I've tried to use the autonumber field but various "is null" "is not null" <>"" statements don't seem to work.

I basically want to include all populated rows but exclude the blank "new record" row.

View 1 Replies View Related

Column Headings In DYNAMIC Crosstab Query

Jan 21, 2008


View 1 Replies View Related

Change Column Position Of Crosstab Query

Jun 18, 2006

Hi :

I have a question,


Company, Start Month, End Month, Fees, Calc
ABC_________Mar-05___Mar-05____7 ____1

I used crosstab to make this format

Company, Start Month, Fees, Mar-05 , Apr-05
ABC_______Mar-05 _____7_____1_______2
ABC_______Apr-05______5 ____________4

I want to change column position as following order:
Company, Start Month, Mar-05 , Apr-05, Fees
ABC_________Apr-05____________4 _____5

I tried to use this code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qd As QueryDef

Set db = CurrentDb
Set qd = db.QueryDefs("report_crosstab")

qd.fields("Fees").OrdinalPosition = 4

It doesn't work for crosstab query.

Can anybody tell me how to change the column position of the query?

Please let me know, thanks.

View 2 Replies View Related

Queries :: Add Percentage Column To Crosstab Query

Apr 12, 2013

I have created a cross tab query that contains a row heading for Entity and Total Cases. I would like to have a percentage of the Case Total for Each Category as well as the count for each category. It works fine for just the counts and here is the sql behind it:


View 2 Replies View Related

Queries :: Counting Occurrences Of P - Summary Column In Crosstab Query

Feb 18, 2014

I need to add a column to a crosstab query that counts all of the occurrences of "P" in a particular row in the crosstab query. Basically "P" stands for "Present" and I need to know how many days each employee was present for his/her shift. The SQL for the crosstab query is as follows:

PARAMETERS [Forms]![frmDashboardReports]![txtStartDateAndTime] DateTime, [Forms]![frmDashboardReports]![txtEndDateAndTime] DateTime;
TRANSFORM First(tblAttendance.AttendanceCode) AS FirstOfAttendanceCode
SELECT tblAttendance.EmployeeName
FROM tblAttendance
WHERE (((tblAttendance.AttendanceDate)>=[Forms]![frmDashboardReports]![txtStartDateAndTime] And (tblAttendance.AttendanceDate)<=[Forms]![frmDashboardReports]![txtEndDateAndTime]))
GROUP BY tblAttendance.EmployeeName
PIVOT Format([AttendanceDate],"Short Date");

This returns an "AttendanceCode" against each employee against each day in the specified time period. I just need to be able to "sum" those codes in a column.

View 3 Replies View Related

Queries :: Conditional Formatting On Column Not Listed In Crosstab Query

Mar 26, 2013

I have a simple crosstab query:

FirstName and Surname as Row Headings.
Date as Column Heading.
OnShift as a Value (Count).

These are from the table DailyActivityLogs. Also in that table is a choice field called Weather that lets you choose the conditions that day (Weather, Work, Part Weather, Subbed).

This is the report the crosstab query generates:

What I would like to do is colour the Count fields depending on what the weather was that day. It seems possible, as Weather sits in the same table, but the field Weather isn't in the crosstab query.

Can I do this?

View 3 Replies View Related

Extract A Listbox Header Name Of A Particular Column?

Jul 18, 2006

Anyone know how to reference the name or header of a particular column in a listbox control?

i've tried,



View 2 Replies View Related

Sort Subform By Click Column Header

Jul 21, 2005

Does anyone know how to sort subform records by clicking column headers. I know there is one way by select column and click A->Z button from toolbar. Is there other way to do so? I know there are same kind of posting, but they couldn't solve my problem.

Any help would be very appreciated.

View 1 Replies View Related

Queries :: Reporting When Column Header Keeps Changing

Dec 18, 2013

I have a crosstab query that reports KPI numbers for the last 5 weeks, using the week no as the column header. Then I am generating a report from this query. It works 100%

However, when I go to the next week, the report fails as the first week from the previous query no longer exists

So, this is happening...

The first report generated columns called 39,40,41,42,43 because the crosstab query uses week number as the header.

The second report (1 week later) cant find column 39 so it crashes!

Can't see how to change the column header to a generic as it is created by the crosstab.....

View 3 Replies View Related

Return Column Header Text On Right Click Of Listbox

Apr 13, 2006

How do I return the Column Header Text on Right Click of listbox?


View 5 Replies View Related

Queries :: Single Row Data Required As Column Header

Nov 4, 2013

Is there any way I can use data in the row as a column header ? I only have 1 row of data which i want to use as a header to I can link it to other tables/queries.

(Access 2007 query)

View 1 Replies View Related

Too Many Crosstab Column Headers (454)

Mar 16, 2007

I am attempting to create my first crosstab query in design view. Adding a simple row and column header and one value, I always get the error message 'Too many crosstab column headers (454)'. What am I doing wrong? Here is the SQL:

FROM Forecast
PIVOT Forecast.QTY;


View 4 Replies View Related

General :: Can't Change Listbox Column Header Name When Data Source Is A Share-point List

Jul 30, 2013

The following code works to change the column header name for a listbox in form view when the data source is a local table, but not when the data comes from a sharepoint list.

sqlstatement = "SELECT ID, PONum as [PO Number], ActDate as [Date], VendorName as [Vendor Name], Service, BuildingNumber as [Building Number], ReservationDescription as [Description], POAmount as [Amount], QuoteType as [Type of Quote], Comments" & _
" FROM ActivityLog" & _
" WHERE (Activity = 'AcceptReservation') AND (PSCName = '" & Me.PSCCombo4.Column(0) & "')" & _
" ORDER BY ActDate;"
'MsgBox sqlstatement
Me.EditPOListBox.RowSource = sqlstatement
sqlstatement = ""

Also it appears that there is no such thing as a caption property for a sharepoint list column.

View 3 Replies View Related

Crosstab Queries?...Is There A Way To Create More Than One Column?

Nov 12, 2004


If there was a way I could get the crosstab query in Access to allow more than one column I would solve a major problem. Is that possible? anyone?

View 1 Replies View Related

Renaming Crosstab Column Heading?

Mar 23, 2013

I have a cross tab query which give result like this

Date No. BoxItem1 BoxItem2 Item1 Item2
1-2-12 1 15 15 10 15
1-2-12 2 10 10
1-2-12 1 15 15
1-2-12 1 10 5 5 1

I need to BoxItem1 come after item1 and also col heading only box.Is it possible or not?

View 1 Replies View Related

Getting The Actual Date From A Crosstab Column Heading

Aug 23, 2005


I have a crosstab query that groups by week to obtain columns for monday to sunday. Is there a way to obtain the actual dates that these columns represent?

Any help would be much appreciated.

View 1 Replies View Related

Queries :: Change Column Headings Crosstab

Aug 17, 2015

I have a Graph that uses a crosstab query to generate the data. Its only a simple query. One of the fields is project type, but in the query this changes to a number instead of text? I would like to have this as text but don't know how to go about it. I have searched forums and tried Allen Browne's suggestion on specifying column headings but no luck.

I have attached the initial query and the crosstab query in the screen shot as well as the graph. My aim is to get the project names in the legend, instead of 1, 2 as shown.

View 6 Replies View Related

Crosstab Queries, Column Headings And Lookup Table

Aug 22, 2006

Not sure if there is a quick answer but I am trying to complete a crosstab query that references a lookup table. I cannot remove the lookup tables because the database was designed by a consultant. The lookup table is referenced as the column heading. The query works fine until I change the column headings in the properties box - it returns the column headings but there are no values. Am I doing something simple wrong or is it having troubles because it is a lookup table for column headings.

View 3 Replies View Related

Crosstab Query Based On Crosstab??

Sep 21, 2007

Hi all, I am utterly unsure if what I want to do is even possible:

I have two crosstab queries, qryRewCOCredit and qryWrapCOCredit which show the changeover (CO) times for the specified machine when they are NOT zero. (all zero entries don't show up).

There are many cases when there is a CO for the Rewinder on a specific day, but not for the Wrapper, and vice versa.

I want to make another crosstab query which performs a calculation. To keep it simple:

If (RewCOCredit>WrapCOCredit) Then
Else 'WrapCOCredit>RewCOCredit

Please help!!!

View 2 Replies View Related

Queries :: Run A Simple Update Query To Copy Data From One Column To Another Column

Sep 24, 2013

I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.

View 7 Replies View Related

Queries :: Change In Column Based On Base Query Column

Mar 24, 2014

I have created a cross tab to extract pipeline and sales for Q1 2014, Q2 2014, Q3 2014 & Q4 2014... the user can select the quater from a multivalued text box...

Now for the final output, have created another query which pull the above four quarter in each column from the cross the problem arises when i change the quarter to Q2 2014, Q3 2014, Q4 2014 & Q1 gives an error "Microsoft office Access database does not recognizes "Query name" as a valid field name or expression".

The error is because the second layer of query does not identifies Q1 2014.

How do i make access change the column automatically when the Q1 changes to Q2...

View 1 Replies View Related

Queries :: Add A Column In A Query That Will Give Y Or No To Previous Column

May 21, 2015

I am looking to add a column in a query that will give a Y or No to previous column data if it contains TEXT or NUMBER (It could read "TEXT" or "NUMBER" or even Y for text or N for number).

View 3 Replies View Related

Queries :: Use Value From Parameter Query In Report Header

Jul 12, 2014

I have a query that I use to populate a report.

The query has a parameter box that opens and asks the user to enter a date.

I would like to display this date in the Report heading.

Is there any way that I can grab the value entered into the parameter query and display it on my report heading?

View 1 Replies View Related

Reports :: Displaying Query Criteria In A Report Header

Apr 22, 2014

I can't get my reports' unbound field's to display the criteria parameter I enter when report is opened. I have done this before but not in Access 2010.

In my parameter, I do have a long string inside the brackets (e.g. =[Enter START Date "1/1/14" or Leave Ranges Blank to show ALL]). I am wondering if this or the quotes inside the brackets are causing it not to work.

View 10 Replies View Related

Copyrights 2005-15, All rights reserved