Column Header In CrossTab Query


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

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 Replies View Related

Crosstab Query Contains <> Column

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 Replies View Related

Column Headings In DYNAMIC Crosstab Query


View Replies View Related

Change Column Position Of Crosstab Query

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 Replies View Related

Column Count Inconsistency Between Recordset And Crosstab Query - Version: 2003 (11.0)

I have run into a problem that I've never encountered before. I am trying to open an ADO recordset of a crosstab query with a SQL string similar to the following:

SELECT * FROM Qry_Crosstab

If I open the Qry_Crosstab by itself, it returns 17 columns worth of data. However, when I open the recordset, it only shows a field count of 9. The other 8 columns simply are not showing up in the recordset.

Has anyone run into anything like this before?

View Replies View Related

Reports Based On A Crosstab Query And Column Headings - Version: 2003 (11.0)

I have a bunch of reports based on Crosstab Queries. The Column Headers in the Crosstab query are company names and more and more will be added in the future. The Client has no idea what future company might be added, so I cannot go to the Query Properties and add in the Column Headings area all the company names. Therefore, everytime a new company is added I have to manually add the company name to the report. I don't think there is anyway around this, I am just afraid that when a new company is added and the report is printed that I or the User will forget that we have to add the new company or companies. Any suggestions???? Thanks!


View Replies View Related

Exporting Crosstab Query - Show Column Headings? - Version: 2000 (9.0)

Ok, last one and then I'm definitely going to bed!! (its 3.45am here )

Hope you can help. I'm trying to export a crosstab query to a sheet in excel - it works, but the column headings don't appear - how can I get it to work like when you export a crosstab manually...? Here is my code:
Private Sub cmdExportResponse_Click()

On Error GoTo err_handler

Dim sFile As String
sFile = OpenFile()

Dim xlApp As Excel.Application
Dim xlSheet As Object
Dim rs As DAO.Recordset
Dim sQry As String

'clear and populate temp tables
DoCmd.SetWarnings False
CurrentDb.Execute "DELETE * FROM temp_qryTotals;", dbFailOnError
DoCmd.OpenQuery "qryTotals"
DoCmd.SetWarnings True

sQry = "qryTotals_Crosstab"
Set xlApp = CreateObject("Excel.Application")

Set rs = Application.CurrentDb.OpenRecordset(sQry)
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets("actuals")

xlSheet.Visible = True
xlSheet.Range("C1").CopyFromRecordset rs


Set rs = Nothing
Set xlSheet = Nothing
Set xlApp = Nothing

MsgBox "Data successfully exported!", vbOKOnly, "Success!"

Exit Sub

If Err.Number = 2522 Then 'if user exits out of openfile dialog box

Exit Sub
MsgBox "There is an error!" & vbCrLf & vbCrLf & _
Err.Number & " - " & Err.Description, vbOKOnly, "Error!"
Set rs = Nothing
Set xlSheet = Nothing
Set xlApp = Nothing
' xlApp.Quit
DoCmd.SetWarnings True

End If
End Sub

Many thanks in advance from a v v tired gal!

p.s. on a side note, if anyone knows how to get this working, that would be a total bonus!! :
xlSheet.Visible = True
xlSheet.Range("C1").CopyFromRecordset rs

View Replies View Related

Extract A Listbox Header Name Of A Particular Column?

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

i've tried,



View Replies View Related

Sort Subform By Click Column Header

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 Replies View Related

Return Column Header Text On Right Click Of Listbox

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


View Replies View Related

How Can I Lookup What The Header Of A Column Is In Code? - Version: 2000 (9.0)

I would like to be able to 'see' what the header names (ie the column title bits) of a table are in code, and act accordingly. I don't know how to 'look' at a table in code, whether it's even possible or a good idea.

Does anyone have any pointers for me?


View Replies View Related

Too Many Crosstab Column Headers (454)

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 Replies View Related

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


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 Replies View Related

Getting The Actual Date From A Crosstab Column Heading


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 Replies View Related

Using A Date Field Column Heading In Crosstab Qry

Problem #1 - getting a crosstab to show more than 12 months of data across. Is this a limitation?

Problem #2 - is related in that i prefer not to use in the Qry2 field name of: Expr1: Format([CalEndDate],"mmm")
The above seems to limit me to just 12 month names like Jan, Feb, Mar etc etc
I was prefer to see: 1/31/2007, 2/28/2007, 3/31/2007. So I want to move away from mmm.
If i leave CalEndDate "as is" in Qry2, i get an error message: :This expression is typed wrong or too complex to be evaluated."

I even tried in Qry1 to make 3 extra field where i strip out 2007 and strip out the month number and then combine the 2 stripped out numbers to look like 2007-1, 2007-2, etc
but i got no where on that either. I was thinking that maybe text would work better but no go.

Here is what I have. Two Qry's. Qry2 is a crosstab using Qry1 data. Qry1 has a Date field in it. I want this Date field called CalEndDate to become the column heading in my Qry2 crosstab.

In Qry1, I have several fields but notably:
Field: CalEndDate
Criteria: Between [Start Date] And [End Date]

Then in Qry2, the crosstab, I have:
Field: Expr1: Format([CalEndDate],"mmm")
Total: Group by
Crosstab: Column Heading

In summary: i am looking to have Qry2 ask me my start and end dates and then then give me 18 months of crosstab data based on either a concatenated field like 2006-11, 2006-12, 2007-1, 2007-2 etc in proper order. or worst case, have the field show the CalEndDate for the column headings in the crosstab.
thanks for ideas.


View Replies View Related

Displaying Column Totals In Crosstab Queries In Forms

Is there a "magic" (easy) way to present column totals in crosstab queries i forms.

I would like to have a result like this:

Totals January February ....etc....
Area 1 100 10 12
Area 2 42 8 9
Area 3 10 1 3
Sum 153 19 24

I suppose it pretty straightforward to have this in a report-footer, but I'm working with a form with different check- and option-boxes that re-calcultes the results from the crosstabs.


View Replies View Related

Crosstab Queries, Column Headings And Lookup Table

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 Replies View Related

Multi-column - Possible W/o Crosstab? W/o Sub-Report? - Version: 2000 (9.0)

Greetings (there have been times today where an air strike on my PC would have been welcomed!).

As the title says, I have a two column report - FY_XX and FY_XY (FY = "Fiscal Year". The other two columns, Variance and Percent are calculated, but I thought I'd mention them just in case it might matter).

I have a query that has all the necessary info in it that I need for the report. The field heading for where FY_XX and FY_XY reside is labeled "FY". I've got the report format just the way I want it. If only I can get the summations for FY_XX and FY_XY to be distinct and line up under the respective columns. From there I'd be happy to call this a day, go home, have a good meal, get a good night's rest, enjoy a sunrise tomorrow AM and pull my hair out over something else : ). But in the mean time, if someone can let me know if I can do it this way or if I have to use a sub-report (the format is pretty specific, so a Crosstab is probably not a viable option).

To all of you wonderful and helpful people, "Thank You" and have a very Merry Holiday Season.



View Replies View Related

Crosstab Report - Need A Column Total Too - Version: 2002 (10.0) XP

Hi -- I could really use some help. I've got a weekly timecard report based on a crosstab query. It creates a separate page for each employee, lists the jobs they've worked on by date (formatted as "ddd" for Mon, Tue, etc) with the hours. The hours total across the row for each job, but I also need them to total down for each day. So that for Monday thru Sunday, if employee John Doe worked on 5 different jobs, I would see how many hours per job at the end of each row, but then at the bottom display the total number of hours per day. Hope that makes sense. It looked simple at first, just the =Sum([Mon]), except I forgot that "Mon" actually represents a date, not the hours.

Here's the SQL for the crosstab query:

TRANSFORM Sum(qryWeeklyTimeCard.Hours) AS SumOfHours
SELECT [EmployeeNum] & " " & [Name] AS EmpInfo, [JobNum] & " " & [JobName] AS Job, qryWeeklyTimeCard.DriveTime, Sum(qryWeeklyTimeCard.Hours) AS [Total Of Hours]
FROM qryWeeklyTimeCard
GROUP BY [EmployeeNum] & " " & [Name], [JobNum] & " " & [JobName], qryWeeklyTimeCard.DriveTime
ORDER BY Format([WorkDate],"ddd")
PIVOT Format([WorkDate],"ddd");

If anyone's got any ideas, I'd really appreciate it. This has been a really complicated project overall and I wouldn't have been able to make it through some earlier rough spots without UA!


View Replies View Related

Suppress Field Label On Crosstab Report Except In 1st Column - Version: 2003 (11.0)

I am trying to print a crosstab type report with up to 5 records/columns going across. The crosstab and label wizards result in the headings for each field printing to the left of the value for each column. This of course takes up space on the report and is not necessary with this horizontal approach. I simply want the field description to print once at the left of the page and repeat if there is more than one page. An added twist is that if there are multiple pages I want the first column of the first page to be either retained or reprinted in the first column of pages 2 thru n. In affect this is a horizontal view of a "subject" and any number of comparison records.

I can create a file to hold up the subject and up to 4 of the comparison records in it. Lots more coding though. I was hoping someone's knowledge and/or trickery in this could help me along.

The attached PDF is and example of what i would like to accomplish.

Thanks to all

View Replies View Related

Problem With Having Numbers As Column Heading In Crosstab Queriy - Version: 2000 (9.0)


I'm having a problem with having numbers as the column heading in my crosstab query for a report. I'm trying to put 'Week' as the column. But the columns for (week) 1,2,3,4,5 is not in the field list and I can't bound the controls to it.

Does anyone know how to get around this problem (besides changing every data from '1' to 'w1')?

View Replies View Related

Crosstab Query Based On Crosstab??

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 Replies View Related

Put Report Header After Page Header - Version: 97 (8.0)

How do can i make the page header appear on the top of the pages, even when the report header is on the page. Right now, my report header is coming before the page header. then when the report header ends, which is several pages, the page header starts part way down the page. Thoughts?

View Replies View Related

Blank Fields In Crosstab Query Based On Previous Query

I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....

First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.

When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)

The SQL was written by Access not by me. :)

Here is an example of the Crosstab SQL (which is using a previous query):

TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
FROM qryTest2
PIVOT qryTest2.TYPE;

qryTest2 SQL: (Grouping by to remove dups)

FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))

Thanks for you time! :)

View Replies View Related

Query On The Report Header Section - Version: 2002 (10.0) XP


Can i have a query in a report header section?

For example,

Can i have a qry1 for report header 1 (records of query 1) and a qry2 for report header 2 (records of query 2) within the SAME report?

Pls guide

View Replies View Related

Header Need To Include Parameter Query Dates - Version: 2003 (11.0)


I have a report created based on a query. The query asks for the first date and the last date of the quarter, so when the report is ran that info is gathered. What I would like to know is how do I have the header show those dates that are entered into the parameter query please?

Thank you.

View Replies View Related

Crosstab Query And Query In VBA - Version: 2000 (9.0)

Is it possible to do a crosstab query of a query all in VBA? I would lke to run a SQL statement to get the right records then I need to do a crosstab SQL statment of those records to get the records I want. Is this possible and if so, can someone provide the general gist of how to go about doing it?

I have both in the QBE and so have the SQL statements, but I'd like to know how to do it in VBA without the QBE.

Thanks in advance ofr any help.

View Replies View Related

Crosstab Query

Hello All,
Your Help Required. I have send you a Database, in which I have used Crosstab query, I just want when I select the report from switchboard, and enter the datefrom / dateto (fields names) than click the preview report. Required report is open.

I have faced following problems
1-When I have selected the report and click the preview button. Report is not opened.
2-I have used cross tab query and link with the switchboard. But when I have run the query this msg is appeared “Microsoft Jet engine Does not recognized these field(name)”


View Replies View Related

Crosstab Query

I am am wondering if it is possible to create a crosstab query that displays alphanumeric values and not numeric (computational) values.

Table contains the following details:

Name Branch
Bob 111
Bob 222
Joe 333
Pam 444

I want to use a crosstab so I can view the data as follows:

Name Branch1 Branch2
Bob 111 222
Joe 333
Pam 444

Is this possible? I've been playing around with it and it doesnt look doable. Perhaps there is a better way of getting the data into this format? Any suggestions would be greatly appreciated!

Super Thanx.


View Replies View Related

Crosstab Query

Hi all

I'm trying to create a crosstab query with criteria that refers to a combo box on a form to allow me to filter data before the query is created. However when I refer to the combo box (e.g. = forms!frmSite!CmbSiteName) I am told that access does not recognise this as a 'valid field name or expression'. With a normal select query I dont have the same problem. Is there a way around this?



View Replies View Related

Crosstab Query

Hi, I have a crosstab query I have set up, calculating the cost of a product per month

it should look like
1 2 3 4
gek22 £55 £66 £77 £88
er44 £99 $100 £101 £102
tt66 £103 £104 £105 £106

but instead it looks like

1 2 3 4
gek22 £55 £88
gek22 £66
gek22 £77
er44 £99
er44 $100 £102
er44 £101
tt66 £103 £106
tt66 £103
tt66 £104 £105

Admittedly this is my first time doing a crosstab query, but I have both my column and rowe headers set to group by, I kinda assumed this would group them in much the same way a pivot table does in excel

Any help on this would be great

Hmm, doesnt want to keep the spacing in it :( hopefully you can get the idea

View Replies View Related

Can This Crosstab Query Be Done?

I have 2 tables:


students holds information about students (surprisingly lol) and Periods has when periods end etc.



What I would like to be able to do is create a crosstab query has:
RowHeading: StartYear
ColumnHeading: PeriodNumber
Value: If the student was in training at the end of the period then count them.

So it would look something along the lines of....
1 2 3 4 5 6
2005 No. of students in learning
2006 at the end of each period

I have managed to do this but I have had to create 12 seperate queries calculating which students were in learning (thats the most periods there will be (12 months)) and then making a normal select query. I am just wondering if there is a different (ethical?) way?

View Replies View Related

Copyrights 2005-15, All rights reserved