Queries :: Using Dynamic Crosstab Queries In Forms
Jan 21, 2014
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?
View Replies
ADVERTISEMENT
Jul 3, 2014
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))
[Code] ....
View 7 Replies
View Related
Nov 23, 2005
Hi,
I have a form and its source is a query. There are only 5 things on the form that can be modified, the rest is just displayed.
the 5 things are within a table b. In my design view i link up table b with the already made crosstab query. (everything within the crosstab query are just for display on the form). Within the design view I put * from the crosstab query (everything) and then the fields from table b (all 5 of them).Everything on the form is working and is linked up properly, but when i go to type in them it is locked. (well they arn't within the property), but i cant type in them. This is for all 5 fields on the form.
When i then go back into the source and remove the crosstab query, i can then type into the fields.
Any ideas?
** p.s When i create the same query normal it doesn't allow me to type in the datasheet view either **
Thanks
k0r54
View 12 Replies
View Related
Dec 2, 2014
I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT
[code]...
View 2 Replies
View Related
Feb 24, 2015
I have a crosstab queries which uses the date query parameters. However, when I go to my Export command (code is below), it ask me to enter the date parameters (start date and end date) twice. What do I have to do so that the system will ask me to enter once only?
Code:
On Error GoTo Err_cmdTest_Click
'Must 1st set a Reference to the Microsoft Office XX.X Object Library
Dim dlgOpen As FileDialog
Dim strExportPath As String
Const conOBJECT_TO_EXPORT As String = "qryEXPORT"
[Code] .....
View 9 Replies
View Related
Feb 23, 2015
How can you export cross tab queries by using date parameters (for example: Jan 1, 2014 to December 31, 2014)...
View 3 Replies
View Related
Apr 30, 2015
I was wondering how to do a crosstab query and have to column headings
I need the Organization Number and the Org name..so something like this
4005 4010
Office of HQ Office of Accounting
Is this possible?
View 2 Replies
View Related
Dec 10, 2014
I have a list of 20 hospital patients that I am trying to use to try and establish a list of supplies or services billed on Day of Surgery, Post Op Day 1, Post Op Day 2, Post Op day Three and Post Op Day 4. My problem is when I ran Crosstab Summaries on their accounts the column headings were the actual dates of services. The dates range from January 2014 to July 2014. Is there a way I can change the dates object to the text objects listed above and then merge the accounts so the items and quantities are tabulated. If I can't do this with Access I'll have to tabulate them manually by either creating a new table and data form or by setting up an Excel Spreadsheet.
Current Crosstab
Item # Description Total Qty Jan 1 Jan 2 Jan 3 Jan 4 Jan 5
201 Gauze 3 2 1
203 Misc 1 1
What I want is:
Item # Description Total Qty DOS POD1 POD2 POD 3 POD 4
201 Gauze 3 2 1
203 Misc 1 1
Keep in mind that there are 20 accounts with varying dates that have to combined into one table or query.
View 1 Replies
View Related
Jun 7, 2004
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!
View 14 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
Dec 15, 2004
Hi all,
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.
Thanks so much.
View 2 Replies
View Related
Mar 3, 2006
i have a database that imports files with dynamic amounts of fields, runs querys on the data and outputs it to a speadsheet. The The problem is instead of making it run querys on each column, i have it running on them all collectively. Now i want to change it so a query is run on each column then output to its own spreadsheet. Does anyone know how to do this?
View 1 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
May 8, 2006
I've got a database that I need to import a vacation days table from an excel sheet. The biggest deal with it all is that the excel file & range it needs to search for the data is dynamic, actually I also need to be able to scan different sheets within the excel file.
Now, im not new to all this stuff, ive done all sorts of complicated excel manipulation in VBA as well as queries and custom processing, but I've been away from it all for roughly 8 mo. so im rusty.
If anyone has a suggestion about how i could go about loading the excel file (dynamically) and pulling information from it (i will be doing aggrigates like SUM & Count, however i can program these manually if need be.)
Thanks
-ReAn
View 2 Replies
View Related
Jun 19, 2006
I have several fields that have a drop down list. Say the Field is called "Age" and I have choices in the drop down menu as 1, 2, 3, and 4. How do I create a query that would accept input from the user to show 2 of the 4 or 1 of the 4 or all of the choices using one query that has checkboxes, and possibly using a form to do so. I have a form that has a list of all entries. Now I just need to make it able to filter based on user input.
Say I have checkboxes for 1, 2, 3, 4, and all choices. Can a list filter only what I check? Can I create a form that would have all the choices on it and just click a button that says "submit" and have it filter, or print the form. How do you get the checkboxes to filter when checked, but not when unchecked? Where does Access store the true/false, on/off, yes/no responces that come from the checkbox? Is there a variable that gets set to a 1 or a 0 based on the status of the box?
View 1 Replies
View Related
Aug 29, 2005
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)
make table query - qryTabletImport_CollarMakeStaging
Crosstab query - qryTabletImport_CollarConvert
normalised table - tblTablet_HOLEDETAILS
Hope this makes some sense to someone.
I am ok with vb so am willing to go that way but getting my head around which way start is another issue
Cheers
View 4 Replies
View Related
Jan 21, 2008
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
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
Jan 20, 2015
I have a relatively small data base.
There are a number of what I call reference tables. These are fairly static and in the main will not change.
There are a further half a dozen tables all linked together.
The system basically takes an enquiry which may lead to a repair taking place. The repair will have a number of jobs and each job may have parts replaced.
What I want to do is have an identical database which to begin with will have exact copies of all the reference tables. The remaining half a dozen tables initially will be empty but grow with time.
The user wants to be able to copy the completed repairs he selects over to the 2nd database (which is initially empty). As weeks go by he wants to select further completed jobs that have not already been copied over to the 2nd database. The 2nd database will be updated so I can't just copy over all completed repairs each time.
I had envisage him doing this by using a front end to the 2 databases and firstly selecting which of the completed jobs which haven't been copied over. This is a fairly straight forward query that will update a field on the repair table.
Once he has done this I was thinking that I could run a query that would pull all the rows from the 6 tables from database 1 and update the corresponding tables/rows on the 2nd database.
I don't actually know how to approach that. The process needs to be simple for the user. ie 1. select repairs to copy over 2. run something to copy them over.
View 11 Replies
View Related
Dec 17, 2013
Is it possible to have the user enter the number of top records to choose in a query?
I know that you can enter a number or percentage in the Top Values property of the query but I would like the user to be able to enter this number each time they run the query since it may be different each time and not always, say, the top 10 records.
View 12 Replies
View Related
Jul 10, 2013
I need to do a DSum with criteria that picks up a previous month based on todays date, I have got the following but I don't know how to format the last part of the criteria.
If I take out the Month(DateAdd("m",-13,(Date()))) and replace it with 6 it works fine.
Code:
DSum("[invquan]","[qrySalesByStockCode]","[stcode] = '" & [stockcode] & "' and [Month] = Month(DateAdd("m",-13,(Date())))'")
View 1 Replies
View Related
Oct 18, 2013
Sometimes i have a problem with dynamical queries. For some of these i need to put in the same parameter value two times, before it works. Why this occurs?
View 2 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
Jan 22, 2014
We have a ComboBox on a form with the months: Jan, Feb, Mar, Apr, etc.
We have fields in a budget table named: Jan, Feb, Mar, Apr, etc.
We want to create a query that pulls the correct field based on the value in the combo box.
This is what I have that doesn't work:
MyField: [Query1].[SumOf] & Forms![Main Menu]![test]
How to do this?
View 4 Replies
View Related
Apr 23, 2015
I have a dynamic cross tab query - thus the column headings will change each time it is run.
At present the column headings are displayed in alphabetical order - how can I change this so they are based on a different order - eg by the descriptions corresponding ID
View 3 Replies
View Related
Aug 30, 2006
I have a crosstab query where I can compile the totals per day that I want. What I need to do is then group these days into weeks by the following Sunday for a week ending total. How can I link each day for its corresponding week ending date. I attatched an image if it helps.
View 4 Replies
View Related
