Crosstab Report
Apr 4, 2007
Hi,
I have questions about crosstab report.
I am using Ms Access and trying to get report from the crosstab query.
Below is the code which I store in the report.
Private Sub Report_Open(Cancel As Integer)
Dim intColCount As Integer
Dim intControlCount As Integer
Dim i As Integer
Dim strName As String
On Error Resume Next
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("select * from qrytest")
intColCount = rst.Fields.Count
intControlCount = Me.Detail.Controls.Count
If intControlCount < intColCount Then
intColCount = intControlCount
End If
' Fill in information for the necessary controls.
For i = 1 To intColCount
strName = rst.Fields(i - 1).Name
Me.Controls("lblHeader" & i).Caption = strName
Me.Controls("txtData" & i).ControlSource = strName
Me.Controls("txtSum" & i).ControlSource = _
"=Sum([" & strName & "])"
Next i
' Hide the extra controls.
For i = intColCount + 1 To intControlCount
Me.Controls("txtData" & i).Visible = False
Me.Controls("lblHeader" & i).Visible = False
Me.Controls("txtSum" & i).Visible = False
Next i
' Close the recordset.
rst.Close
End Sub
The bold text should give me the details, but it does not show on the report. I can only see the header.
Please help.
View Replies
ADVERTISEMENT
Aug 18, 2004
I have a report from a cross tab query.
it is selecting "EN-02", "EN-04", "EN-05" as a value.
The problem occurs because there is no control source for a value in the crosstab.
The problem is that if the query returns and there is no value for "EN-02" then the report fails and say that it can't recognize "EN-02" as a valid field name or expression in the report because there is no value in the query for it.
How can I get the report to load the returned values no matter what is returned from the query?
ie if there is no control source, set the value to say "No flow"
Please Help
~
View 1 Replies
View Related
Sep 21, 2004
is there a way to output a crosstab query to some kind of report, either a built in report or a Word doc.
i have searched for ages but just want a simple tool to do this.
View 1 Replies
View Related
Nov 30, 2004
I am using the following cde to generate a Dynamic Crosstab Report. I can get the column labels to work but Ican't get the values to appear in the detail section all I get is #Name?
I am using the following code in the report
Private Sub Report_Open(Cancel As Integer)
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("select * from test2")
rst.MoveFirst
j = 0
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).NAME Like "*test" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.Field0.ControlSource = rst.Fields(i).NAME
Case 1
Me.Field1.ControlSource = rst.Fields(i).NAME
Case 2
Me.Field2.ControlSource = rst.Fields(i).NAME
Case 3
Me.Field3.ControlSource = rst.Fields(i).NAME
Case 4
Me.Field4.ControlSource = rst.Fields(i).NAME
Case 5
Me.Field5.ControlSource = rst.Fields(i).NAME
Case 6
Me.Field6.ControlSource = rst.Fields(i).NAME
Case 7
Me.Field7.ControlSource = rst.Fields(i).NAME
Case 8
Me.Field8.ControlSource = rst.Fields(i).NAME
Case 9
Me.Field9.ControlSource = rst.Fields(i).NAME
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
End Sub
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("select * from test2")
rst.MoveFirst
j = 0
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).NAME Like "*test" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.Label0.Caption = rst.Fields(1).NAME
Case 1
Me.Label1.Caption = rst.Fields(i).NAME
Case 2
Me.Label2.Caption = rst.Fields(i).NAME
Case 3
Me.Label3.Caption = rst.Fields(i).NAME
Case 4
Me.Label4.Caption = rst.Fields(i).NAME
Case 5
Me.Label5.Caption = rst.Fields(i).NAME
Case 6
Me.Label6.Caption = rst.Fields(i).NAME
Case 7
Me.Label7.Caption = rst.Fields(i).NAME
Case 8
Me.Label8.Caption = rst.Fields(i).NAME
Case 9
Me.Label9.Caption = rst.Fields(i).NAME
End Select
skip_it:
Next
rst.Clone
Set rst = Nothing
End Sub
What else do I need to do to get data in the detail section
In anticipation
errolf
View 5 Replies
View Related
Jul 14, 2005
Complex Report Query (CrossTab)
Hey everyone!
I need some help here, I am in the final design stages of my database… I am working on creating some reports and I need some help getting the info I need in the right place. Let me summarize the problem, then I will give you a rundown of my table structure and what I need to do, finally explain how far I am at this point.
I need to create, a complex query for use of a report I am creating. Here is a summary of what info the final query needs to include:
CurrentRank / LastYrRank / BusType / BusName / BusPhone / etc. / Cat1 / Cat2 / Cat3 / etc.
*******************
Here is my table Structure
*******************
|tblBusiness
|--------------
| &BusinessID
| BusinessTypeID-----1---1--1
| BusinessName | | |
| ….(etc.) | | |
| | |
|tblBusinessType | | |
|-------------------- | | |
| &BusinessTypeID--%/ | |
| BusinessType | |
| ….(etc.) | |
| |
|tblCategory | |
|------------- | |
| &CategoryID---------1 | |
| CategoryName | | |
| | |
|tblBusinessCategory | | |
|------------------- | | |
| &BusinessID----------- |--%/ |
| &CategoryID-------- %/ |
| &CopyYear |
| Value |
|
|tblRank |
|--------- |
| &RankID |
| &BusinessID------------------%/
| BusinessTypeID
| &Year
| Rank
1= one
%=many
&=PrimaryKey
Now, each BusinessType has several associated BusinessCategories (3-8)… all of the businesses use that set of categories to create a unique set of categories along with a value and a year. So, all businesses with a common businessType will have a common set of categories and values, and will have a set for each year
Additionally each business within a businessType is ranked by those categories for each year. (I am currently setting the business rank by year manually, though I will eventually create a module to do it for me.)
With me so far?
*************************
Here is The Report I need to make
*************************
BusinessType
|----------------------------------------------------------------------|
|Rank | |Telephone| | | | |
| 2006| Business | Fax | $ sold lt. yr| Type Meal | Owner | |
| 2005| Address |e-mail/web|(Category*)| (Category) | Category | etc.|
|-----|----------|-----------|------------|-----------|----------|-----|
| | Bus. 1 | phone | 23,405 | dinner/ | John Doe | |
| 1@ | Street | fax | | dessert | | |
| 1^ | State/Zip | email/web | | | | |
|-----|----------|-----------|------------|-----------|----------|-----|
| | Bus. 2 | phone | 20,185 | breakfast |Jane Smith| |
| 2 | Street | fax | | lunch | | |
| 4 | State/Zip | email/web | | | | |
|-----|----------|-----------|------------|-----------|----------|-----|
| | Bus. 3 | phone | 18,958 | lunch | Ron Man | |
| 3 | Street | fax | | | | |
| 2 | State/Zip | email/web | | | | |
|-----|----------|-----------|------------|-----------|----------|-----|
| | Bus. 4 | phone | 17,432 | lunch / | Bob Neff | |
| 4 | Street | fax | | dessert | | |
| 3 | State/Zip | email/web | | | | |
|-----|----------|-----------|------------|-----------|----------|-----|
| Footer |
|----------------------------------------------------------------------|
*=Ranking Category
@=currentYearRank
^=PreviousYearRank
Hope this is helpful… almost done…
*****************
Here is where I am at…
*****************
I have a query named QallBusinesses which contains all tblBusiness linked to tblBusinessCategory.
Then I have a Cross Tab Query named CTQGolfCourses (for instance) that limits the year and business type which gives me the below results
BusType / BusName / BusPhone / etc. / Cat1 / Cat2 / Cat3 / etc
This is great… it is almost finished… Now, I need the list to include the current year and previous year rank (lets say 2006, 2005 respectively) and sort the list of businesses according to the current year rank… (in case of rank ties sorting by business name) to end of with this:
CurrentRank / LastYrRank / BusType / BusName / BusPhone / etc. / Cat1 / Cat2 / Cat3 / etc.
That is where I get stuck! Can I create a nested Crosstab?
I have tried to create a query using CTOGolfCourses and tblRank linking BusinessID then crosstab that query to get the results I want, however it just ends up creating a records for each business type to have every available rank with every available year, so when I crosstab it, it ranks all the businesses as #1 for both years.
If I can lay this out manually, I know that I can code this using VBA to generate the exact reports I need, using the varying criteria, however I cannot quite get the results I need manually.
Wow, this is a long post! Thanks for hanging in there! I really appreciate any help you can give on this complex problem!
View 3 Replies
View Related
Nov 13, 2014
I have a crosstab Query as the source for my report, of course the issue is the column headings on the report. I have Purchasers as a row heading, Year as a row heading, Month/Year as a row heading, Meter as a column heading, PaidMCF as Value and, a total as a Row heading. My issue is feeding the column headings on my report with the meter names.
Purchaser 1 has 23 meters attached
Purchaser 2 has 1 meter attached
Purchaser 3 has 6 meters attached
Purchaser 4 has 2 meters attached
Purchaser 5 has 16 meters attached
Purchaser 6 has 11 meters attached.
View 4 Replies
View Related
Oct 27, 2007
Hi all, help with this would be appreciated.
I have a crosstab query which returns values in a range of categories. I have 5 categories. My problem is that sometimes a category may not return a value in 1 or more of the categories. This means that when I am displaying this data in my report I haven't got the data in the correct columns.
In the underlying queries for this I tried using a LEFT JOIN forcing the category, but when I executed the crosstab I got errors in all fields.
Thanks in advance
Ginny
View 2 Replies
View Related
Feb 22, 2015
I am playing with a report. Crosstab report works, but I would like to print check mark if value is "X". Other values are "A" and "E" and they need to remain as they are.
View 4 Replies
View Related
Mar 26, 2013
I made a report with following crosstab query.
TRANSFORM Workersdetail.workername AS CountOfedate
SELECT Workersdetail.[attendance], Count(Workersdetail.[edate]) AS [Total Of edate]
FROM Workersdetail
GROUP BY Workersdetail.[Workername], Workersdetail.[attendance], Workersdetail.[workerhourenter]
PIVOT site+Cstr([workerhourenter])
I wanted to know that, is there any option through which my report gets autoupdate or refresh incase of addition in SITE field (as mentioned with PIVOT)?
View 1 Replies
View Related
Apr 19, 2015
I've got a self updating crosstab query, its essentially a monthly summary and every month a new column is added (one corresponding to the current month, i.e., next month the new column will be may, following that the new one will be june, etc)
I've designed a report to be based on this query and i tested it out by manually adding data for next month into a table, the query auto updated however the report remained the same (ended in april instead of adding a new column for may).
Just curious if there is a way to automatically add these new columns to the report every month or will i have to do so manually?
View 1 Replies
View Related
Mar 21, 2015
I've made a crosstab query and would like to use it to create a subreport. In the column headers I have names of courses. Courses can be added or removed. How can I make a crosstab report with dynamic columns?
PHP Code:
TRANSFORMÂ Count(tblCourses.CourseName)Â ASÂ CountOfCourseName
SELECT tblNmscStaff.NmscStaffFirstName, tblNmscStaff.NmscStaffLastName,Â
tblNmscStaff.PtOrFtNtl, tblNmscStaff.Ntl, tblNmscStaff.NmscID
FROM tblNmscStaff LEFT JOIN (tblCourses RIGHT JOIN [tblNmscStaff/CoursesPointer] ONÂ
tblCourses.CourseIDÂ =Â [tblNmscStaff/CoursesPointer].CourseID)Â ONÂ
tblNmscStaff.NmscStaffIDÂ =Â [tblNmscStaff/CoursesPointer].NmscStaffID
GROUP BY tblNmscStaff.NmscStaffFirstName, tblNmscStaff.NmscStaffLastName,Â
tblNmscStaff.PtOrFtNtl, tblNmscStaff.Ntl, tblNmscStaff.NmscID
PIVOTÂ tblCourses.CourseName;Â
View 1 Replies
View Related
Jul 28, 2015
This is a query, report and vba question. I'm using Ms Access 2007.
TABLE 1: projectname, activityname, totalhoursworked, employeename
TABLE 2: employeename, employeelevel
TABLE 3: employeelevel, rate
I created a select query to join the info that I need.
SELECT QUERY 1: projectname, activityname, employeename, totalhoursworked, rate, cost (calculated field (totalhoursworked*rate))
I have 2 crosstab queries.
CROSSTAB QRY 1: ROW (projectname, activityname) COLUMN (employeename) VALUE (totalhoursworked (summed))
CROSSTAB QRY 2: ROW (projectname, activityname) COLUMN (employeename) VALUE (cost (summed))
I then created a 2nd select query with inner joins to join both crosstab queries on similar fields (activity & projectname).
SELECT QUERY 2: projectname, activityname, employeename (totalhoursworked as value), employeename (calculatedcost as value)
It gives me this:
However, I want it like this:
Those employeename... refers to more employees being added after a period of time. Hence I want to know if I could use vba to generate a report every time a button is pressed on a form? I know how to link the form to the query.
View 8 Replies
View Related
Nov 22, 2014
This code runs to the set frm part then i get a type mismatch? ive tried a few different things and still nothing?
Code:
Private Sub Report_Open(Cancel As Integer)
' Create underlying recordset for report using criteria entered in
' EmployeeSalesDialogBox form.
Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form
' Set database variable to current database.
Set dbsReport = CurrentDb
[code]....
View 2 Replies
View Related
Jun 12, 2012
I want to create a report for the Monthly cleaning plan of a hotel. For each day, how many rooms need new sheets, how many need new towels etc.
At this point I can generate a report for any given day.
This could be an example of what I want to achieve
Code:
------------ Date | Date+1 | Date+2 | Date+3
New Sheets 2 1 0 2
New Towels 1 3 0 1
Full Clean 0 1 2 0
"Date" is a date tat you can set, after which you'll get the following 30 days("Date+1","Date+2" etc)
I thought that a CrossTab query would give me what I want,but using the wizzard I can't get the result that I want.
Haven't worked with crosstab queries before so maybe I'm doing something wrong, or maybe this isn't even possible with a crosstab query.
View 4 Replies
View Related
Apr 7, 2013
I am creating a crosstab query in VBA to report on claims on a paid and incurred basis. I would like the query to have 13 columns - one for each month of the current year and one for all claims paid prior to January of the current year.
Is there a way to lump all data with a date less than Jan 1 into a single field while retaining the monthly detail for the current year?
All of the data is coming from a single table. Sample code below functions, but provides a column for every month a claim was incurred.
Sub Triangle()
'Triangle Reports
'Check Registers
On Error GoTo Error_Handler:
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim QRY As DAO.QueryDef
[Code] ....
View 4 Replies
View Related
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
5-RewCOCredit
Else 'WrapCOCredit>RewCOCredit
5-WrapCOCredit
Please help!!!
View 2 Replies
View Related
Mar 23, 2006
I have a table that holds review information on staff:
tblReviews:
StaffID (Number)
ReviewDate (Date/Time)
Completed (Yes/No)
Everytime a review is carried out it is added as a new record, however, I need the information to be presented in a crosstab so it looks something like this:
StaffID Review1 Review2 Review3
17 01/01/2006 04/04/2006 05/05/06
20 01/01/2006 04/04/2006 05/05/06
25 01/01/2006 04/04/2006 05/05/06
Other then creating a table and playing with a bit of vba does anyone have a solution?
Thanks.
View 4 Replies
View Related
Sep 16, 2007
Im trying to use crosstab to help me out count how many pages are within a document...
for example
in my "break" field. there are records that contain D's(d=document), and blank records(blanks=pages)...im trying to use crosstab to help me count how many are within each document....but for some reason, its not coming out right...can anyone help me?
i keep getting this error when i try running my crosstab query...
"Too many crosstab column headers(21521)"
is it possible since i have so many records...i cant perform this task??
heres a cpy of my db
View 4 Replies
View Related
Nov 26, 2007
I want to combine the data from 2 tables in a report. I think I need to do this through a crosstab query, but I've no idea how as it needs to calculate it's data.
The row heading needs to be [DateWorked] from the "Hours" table.
Each Column heading needs to be every [StaffPIN] from the "StaffData" table.
I want it to display the number of hours each staff member worked for a perticular day. The "Hours" table holds each staff member's start and finish time. I have a function called workedHours that, given the start and finish time it will return hours worked (a shift can overlap 2 days so DateDiff wouldn't work).
Can anyone help?
View 9 Replies
View Related
Dec 19, 2007
Hello,
I have a table with the following info:
ID, CSR, Option 1, Option 2, Option 3, all the way to option 12.In the Option fields, you can enter a number from 1 - 12. What I'm looking for is a summary report that will give the number of times each number appears for each field. Something like this:
Option 1: 1 = 20 times, 2 = 5 times, 3 = 15 times
Option 2: 2 = 5 times, 2 = 10 times, 3 = 0 times
So on and so on. Would this be done with a crosstab query, or am I way off?
Thanks in advance for your help.
Alex
View 4 Replies
View Related
Aug 17, 2005
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)”
Thanx
ami
View 2 Replies
View Related
Oct 6, 2005
Does anyone know what the maximum rows & columns are for a crosstab query as I cannot find them anywhere?
When I create one that is too big, it just gives the message of how many columns I have rather then how many I'm allowed.
Any help would be greatly recieved.
Regards
Carly
View 3 Replies
View Related
Dec 15, 2006
Hmz,
I have never tried this, it seems easy enough but in fact... I simply cannot find the answer.
I want to display a crosstab query as a (sub)form. The basis of this is that a form requires the columns to be fixed. The crosstab will offcourse grow and grow in the number of columns.
I would like to show the form as a datasheet to support this purpose.
I was thinking maybe I can dynamicaly add the (new) column(s) before opening the form, but how do I do that?
Greets & Thanks
Yours
View 4 Replies
View Related
Sep 23, 2005
I am am wondering if it is possible to create a crosstab query that displays alphanumeric values and not numeric (computational) values.
Ex:
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.
O.
View 5 Replies
View Related
Oct 2, 2005
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?
Cheers
Rhys
View 4 Replies
View Related
Oct 4, 2005
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 3 Replies
View Related
