I have 12 months' worth of data that the user wants displayed on a crosstab report. I have no problem creating the crosstab query or subsequent report. However, the user wants to be able to select their own 12 month time period. So, my crosstab is based upon a make table query, that will allow the user to enter parameters.
Now, my problem is that these 12 month column headers/data will change as the user enters different date ranges. How do I write the code that will allow me to pass these variables to the report? I'm using 97.
Thanks!
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.
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?
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
I’m trying to create a report with dynamics columns generated by a crosstab query. I have set 4 unbound text boxes for header and detail sections on the report. The column heading from the crosstab query could have 1(Column), 2 or 3 different values, which will be my heading in the report. I want to be able to show 1(Column) if it has a valued and hide the rest 2, and 3 heading. Now column 4 headers will be my Total heading as well for values, which will be coming from the sum of 1,2 and 3 if they happened to have values.
Can anyone help on this please, and let me know if I should bring some more information to you guys.
Hi, I wish to use the results of a crosstab query in another query which will make table. The trouble is the field names are not always the same in the crosstab. The results are coming from a normalised table so if there are no records for a particular field then that field will no appear in the crosstab.
I need to make my 'make table query' (qryTabletImport_CollarMakeStaging) account for the missing fields names when it tries to make the new table. Since I have made the make table query with all availble field names I get this error when some are not present.
<the jet engine doesnot recognise 'qryTabletImport_CollarConvert.Hours' as a field name or expression>
(because the field Hours doesn't have any data in the normalised table)
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
I'm trying to run a query which fetches only the last 2 years of data for a given region from a table with several years worth of data (there are year, region, sector and rank columns among others). The region is passed into the query from a combobox from Form1.
The first problem was that some regions have up-to-date data and some not so much e.g. for Europe the "last 2 years" mean 2012,2013 for Asia its 2011,2012. In order to deal with this I've created a crosstab query which works well except for one thing - because the columns are dynamic (dependant on the region) the column headings change as well.
And here comes my question, how can i fix the column names to be e.g."Current Year" and "Prior Year" independent of the query fetching 2012,2013 or 2009,2010? I've tied different things with PIVOT... IN ... but with no luck.
Here's the sql for the query:
Code:
PARAMETERS [Forms]![Form1]![cmbRegion] Text ( 255 ); TRANSFORM min(DataTable.Rank) SELECT DataTable.Region, DataTable.RegionalSector, FROM DataTable WHERE (((DataTable.Region)=Forms!Form1!cmbRegion))
I am building a database which tracks tasks assigned to employees. I want to have one of my forms displaying a view which shows at a glance which tasks are assigned to whom at what times of the day, with a click opening a task for editing. My current attempt is to create a crosstab query with the employees as the column headings ("dynamic crosstab" query) and a table of time values for the hours of the day providing the row headings, with the task as the value.
I then created a form based on the query with Datasheet view as the default view (apparently necessary to set properties/event handlers to query values. I got the idea from the Northwind sample database) However, the form fields do not update to reflect changes to the employee list and therefore the onClick handlers are not applied to newly added employees. What to do, besides for programmatically setting the event handlers?
hi, i'm having a problem making my report dynamic, i have a query which returns the payments due in the next month, my question is, if i have a form based on this query, how can i make it so that, if i need to print an invoice for one of the customers, lets say the currently displayed record, how can i limit the customers displayed in that report (invoice) to just the current record that i want the invoice for?
I have a form with payment information and need a list, what date the payments are due. These can be only 2 or 3 payments up to maybe 24 payments. The result I want is something like this:
Date Amount Total paid 1.7. 500.00 500.00 1.8. 500.00 1,000.00
The list itself is not a problem, but I need a list who only shows the number of payments as agreed, 2 lines (with paydates) if 2, 12 if 12 payments. I have the information of the total amount, the number of installments and the first payment date.
I created a dynamic crosstab report with 4 unbound fields in the details section and 4 unbound fields in the header section, which all work perfectly well. The crosstab query contains 17 columns. The last 4 columns contain the values I need to take the sum of. I have put some code in the open event procedure of the report.
Private Sub Report_Open(Cancel As Integer) Dim rs As DAO.Recordset Dim db As DAO.Database Set db = CurrentDb Set rs = db.OpenRecordset("Select * from Que_ProjectUren_Sel_Dept_Test")
I'm currently creating an invoicing report for a company, and for record purposes they need a dynamic field on the report for the specific Invoice Number of each client they invoice. I have the field set up currently as a text box =[Invoice#] so they have to enter an invoice number when they generate the report. However, when I generate the report and put in a number, I'm getting some whacky outputs.
For example, I've tested it with a test client, and when I give the client an invoice number of 1, the report somehow changes it to 49.
I've created an Access Report of a letter to be printed and mailed to customers. The majority of the letter is text, however there is a subform in the middle that will have a wide range of records displayed in it. This causes the subform's height to adjust from 1/2" to several inches, depending on the number of records. I need the text below the subform to start just below the last record in the subform, no matter how many records are displayed in the subform.
I have made a cross-tab query which works fine. I also have made a report which is based on this cross-tab query. Due to nature of the cross-tab query the Value column(s) is dynamic. The report I made is based on all available data types (columns). The report works if all data type is available but the report fails if some of the data types are not available (i.e columns are missing due to not having any value or data). I hope I am explaining this correctly,
Is there a way I can use expressions in the report to place a conditional clause that if the Control Source doesn't exist place a Null or 0 in the report or in its place.
I have searched and searched for a thread before posting this, there are some discussions on related issues, but I could not find a solution. This is my problem:
I have a table with Incident_Type (a total of 6 selections from a combo-box), Incident_date, Incident_SubArea(25 selections from a combo-box), a check box for IsStaff_reported and all these are recorded by Incident_ID.
I have created a monthly report based on a parameter query that prompts for start and end dates using a custom form for incidents.
Now I need parameter (again start and end dates) cross-tab queries for number of incidents (i.e. count of Incident_ID) that are 1. IsStaff_reported, and 2. Not IsStaff_reported. to show up in a single report.
I dont know if that is too much to ask for or is there a simple way to accomplish this. Any help is appreciated.
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.
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)?
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?
I have a form that shows a list of all of my records in my database. I want to be able to click a button called "Report" and have that print a report that has all the records I have filtered on my form. I have a report in the format that i want it in, however, currently it prints every record and not just what is shown on my form. (The form is dynamic and I want the Report to be dynamically based on the form) HELP PLEASE!
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
Hey, I made a table Products: order number (key), name, supplier, price,... I copied it into a query, and made a form of it, so I can look up products by order number with a combo-box. Now I'm into my next step (and it gives me a headache): In the form I added a new text box, where I should put the incoming amount of a product in, and another text box to put in the products that are empty. Now the question: how can I make a query that counts the amounts of incoming and empty products, as well as the difference between those two, in a way I can do multiple additions? I hope you know what I mean by dynamic sum: start stock=0 first delivery: +3==> stock 3 +next delivery: +6 ==> stock 9,...
Hi guys, I am trying to create a form or subform that should be created after a button is pressed and some complex filetering is occured and records are saved in a multiple arrays so I need to generate a form and controls according to those array values. Please help.
Actually I can not get data directly out of the query its just too complex bla bla bla. Finally I have three arrays each array has same number of values and I want to generate a form on the basis of the values of the array. Please Help me. I have tried creating form but Could someone please show me the right path.
