Crosstab To Report/Word
Sep 21, 2004is 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.
ADVERTISEMENT
Report From Crosstab
Aug 18, 2004I 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
~
Crosstab Report
Apr 4, 2007Hi,
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.
Dynamic Crosstab Report
Nov 30, 2004I 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
Complex Report Query (CrossTab)
Jul 14, 2005Complex 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!
Reports :: Dynamic Crosstab Report
Nov 13, 2014I 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.
Help Needed With Displaying Crosstab Data In A Report
Oct 27, 2007Hi 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
Reports :: Crosstab Report With Conditional Formatting
Feb 22, 2015I 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 RelatedReports :: How To Make Report Autoupdate With Crosstab Query
Mar 26, 2013I 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)?
Reports :: Updating Report Based On Crosstab Query
Apr 19, 2015I'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?
Reports :: Crosstab Report With Dynamic Number Of Columns?
Mar 21, 2015I'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;Â
Exporting Report Into MS Word?
Apr 10, 2014I have a Report that contains, among other things, Pictures & Check Boxes. When I try to export into a Word (.rtf) document,the Data exports just fine, but the pictures & check boxes are lost. Is there a way to export the Report into Word keeping all formatting intact?
View 3 Replies View RelatedQueries :: Sort Crosstab Query Columns And Generate Report
Jul 28, 2015This 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.
Modules & VBA :: Type Mismatch Report On-Open For Dynamic Crosstab
Nov 22, 2014This 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]....
Skip Report, Publish With MS Word
Apr 6, 2005Currently, I have a button on my form that users can click on to generate a report based on the info from the form. Once the report has opened, they publish the form with MS Word for further edit if needed.
How do I set up the button so that it opens the report then automatically publishes the report with MS Word?
General :: Monthly Cleaning Program - Crosstab Query To Generate A Report
Jun 12, 2012I 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.
General :: Word Report Controlled By Form
Jun 17, 2013I have a word 2010 reports that is linked to an access 2010 form. SQL is used to populate the report with some of the fields from the form. Is it possible to create a combo-box with two selections (Mike, Paul) in it. And then have two other fields populate based on the selection in that combo-box? For example, if Mike is selected then an Address: and Phone Number: fields are filled in with his information. And if Paul is selected his information fills in.
View 1 Replies View RelatedGeneral :: Logo On Report With 2 Colours One Word
Apr 20, 2014I have a company logo that has 2 colours to its name
I want to put that logo onto me report. I have tried but I am unable to do this
company name APICAPACITORS, API IN RED CAPACITORS BLUE ,is it possible without making 2 words
Reports :: Export Access Report To Word?
Feb 27, 2014Is there a way to export a report from Access into Word so that it keeps all its formatting etc. The only option shown is in RTF format.
View 2 Replies View RelatedExport Access Report To Word Document
Jan 11, 2014I want to insert access report in a word document, for this, I use this code:
DoCmd.OutputTo acReport, "rptName", acFormatRTF, "C:ReportFileName.rtf"
But, when that output word file is empty, don't have any chart that was in access report.
Queries :: Crosstab Query By Month - Report On Claims On Paid And Incurred Basis
Apr 7, 2013I 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] ....
Loose Form Features When Publish Report To MS Word
Nov 5, 2004I have created a report in MS Access. When I choose the option to Publish to MS Word, I loose all of the background colors. Is this correct or am I doing something wrong? Is there a way to maintain all of the Format features when I publish?
View 1 Replies View RelatedReports :: Create Report To Cut-n-paste Into Word Table
Apr 10, 2015I have a report that groups by a project name and then a repeating field to list all associated detail having to do with the project. Then the projects loop to the next.Each project row is the same height as the combined detail rows. And to be able to print guidelines just as you would with a word table.
+-------+-----------------+---------+
| Project |.-detail row 1......| Open.....|
|...........|.-detail row 2......|.............|
|...........|.-detail row 3......|.............|
+-------+-----------------+---------+
This is what currently happens.
+-------+-----------------+---------+
| Project.|.-detail row 1......| Open.....|
+-------+-----------------+---------+
............|.-detail row 2......|
............+-----------------+
............|.- detail row 3......|
............+-----------------+
I'm thinking I need to do it with vba putting each into a variable then set the field to be the value of the variable. but i'm also not sure how to do that as it repeats like that.
General :: Export A Query Of 14 Items To A Word Report?
Jun 17, 2013Is there a way to export a query of 14 items to a word report. I have a table of 14 items in that I use as a query but it only exports 1 item to the report.
View 2 Replies View RelatedReports :: Open Report As A Microsoft Word File
Jun 26, 2014I want to open a simple report (restricted to the current record) but I need it as a editable MS Word file, (not printed or pdf, etc.)
Quote:
Private Sub MyReport_Click()
Dim stDocName As String
Dim strWhere As String
[code]....
I've tried several combinations of vba..I'm using Microsoft Access 2007
General :: How To Export Report Or Table To Word Document
Jul 10, 2014How do I export report or table to word document with check box so people can check them and send to me back. I can change my records.
For example
Delete ITEM
5540 (Chick box goes here, they can tick)