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?
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#))
GROUP BY LaborDB.ProjID
PIVOT Format([Date],"Short Date");
Thanks for your help
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
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.
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
I have a question,
Company, Start Month, End Month, Fees, Calc
I used crosstab to make this format
Company, Start Month, Fees, Mar-05 , Apr-05
I want to change column position as following order:
Company, Start Month, Mar-05 , Apr-05, Fees
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.
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?
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!
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
CurrentDb.Execute "DELETE * FROM temp_qryTotals;", dbFailOnError
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
Set rs = Nothing
Set xlSheet = Nothing
Set xlApp = Nothing
MsgBox "Data successfully exported!", vbOKOnly, "Success!"
If Err.Number = 2522 Then 'if user exits out of openfile dialog box
MsgBox "There is an error!" & vbCrLf & vbCrLf & _
Err.Number & " - " & Err.Description, vbOKOnly, "Error!"
Set rs = Nothing
Set xlSheet = Nothing
Set xlApp = Nothing
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
Anyone know how to reference the name or header of a particular column in a listbox control?
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.
How do I return the Column Header Text on Right Click of listbox?
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?
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
GROUP BY Forecast.HECI
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?
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.
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:
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.
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.
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.
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.
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]
GROUP BY [EmployeeNum] & " " & [Name], [JobNum] & " " & [JobName], qryWeeklyTimeCard.DriveTime
ORDER BY 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!
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
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')?
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
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?
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
GROUP BY qryTest2.CID
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! :)
Can i have a query in a report header section?
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?
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?
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.
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)”
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:
I want to use a crosstab so I can view the data as follows:
Name Branch1 Branch2
Bob 111 222
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!
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?
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
er44 $100 £102
tt66 £103 £106
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
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:
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?