Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Column Header In CrossTab Query


Hi,

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

ProjIDT-Hours13/06/200514/06/200515/06/2005
22130 8 5
243257810

Code

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

Thanks for your help

Joolz


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
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:

Month....102
1..........$500
2..........$300
3..........$450
etc.

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
Hello to everyone!!!i need to ask somethingI CREATE I DYNAMIC QUERY .....HOW CAN I CHANGE THE NAME OF THE COLUMN HEADINGS?THANX IN ADVANCE

View Replies !   View Related
Change Column Position Of Crosstab Query
Hi :

I have a question,

Table1:

Company, Start Month, End Month, Fees, Calc
ABC_________Mar-05___Mar-05____7 ____1
ABC_________Mar-05___Apr-05____7_____2
ABC_________Apr-05___Apr-05____5_____4


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_________Mar-05____1_______2_____7
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
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,

Me.lstBox.Column(1).Name

Scott

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?


Thanks

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:

TRANSFORM Sum(Forecast.QTY) AS SumOfQTY
SELECT Forecast.HECI
FROM Forecast
GROUP BY Forecast.HECI
PIVOT Forecast.QTY;

Robert

View Replies !   View Related
Crosstab Queries?...Is There A Way To Create More Than One Column?
Hey..

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?...help anyone?

View Replies !   View Related
Getting The Actual Date From A Crosstab Column Heading
Hi,

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.

Penwood

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.

André:confused:

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
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
5-RewCOCredit
Else 'WrapCOCredit>RewCOCredit
5-WrapCOCredit

Please help!!!

View Replies !   View Related
Blank Fields In Crosstab Query Based On Previous Query
:confused:
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
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;

-----------
qryTest2 SQL: (Grouping by to remove dups)

SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
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"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;

Thanks for you time! :)

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved