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?
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");
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.
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.
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: 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")
err_handler: If Err.Number = 2522 Then 'if user exits out of openfile dialog box
Exit Sub Else 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!! : Code: xlSheet.Visible = True 'xlSheet.Cells.Select 'xlSheet.Selection.ClearContents xlSheet.Range("C1").CopyFromRecordset rs xlSheet.Columns.AutoFit
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.
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.
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;
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.
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] 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!
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.
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')?
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?
: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;
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.
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)”
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?
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
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?