Dynamic Crosstab Query
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 Replies
ADVERTISEMENT
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
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
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
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
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
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
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 2 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
Jun 30, 2006
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!
View 6 Replies
View Related
Feb 7, 2007
I have a form where a user chooses different criteria from listboxes and then I run out and format the SQL statement.
This is the query that I am trying to replicate using VBA code. Note: the Location_ID, Ship_Day, and Final_Dest come from the values in the listboxes.
I am getting a syntax error and I cannot find it anywhere. Also, is there an easier way to do this?
SELECT [(Table) Denton Routing].LOCATION_ID
, [(Table) Location].NAME
, [(Table) Location].CITY
, [(Table) Location].STATE
, [(Table) Location].REGION
, [(Table) Denton Routing].UNIQUE_LANE_ID
, [(Table) Denton Routing].CARRIER_ID
, [(Table) Denton Routing].[SHIP DAY]
, [(Table) Denton Routing].[DELIVERY DAY]
, [(Table) Denton Routing].[TIME AT LOCATION]
, [(Table) Denton Routing].STOP_NUM
, [(Table) Denton Routing].NO_OFF_STOPS
FROM [(Table) Location] INNER JOIN [(Table) Denton Routing] ON [(Table) Location].[LOCATION ID] = [(Table) Denton Routing].LOCATION_ID
WHERE ((([(Table) Denton Routing].UNIQUE_LANE_ID) In (SELECT UNIQUE_LANE_ID
FROM [(Table) Denton Routing]
Where [(Table) Denton Routing].Location_ID = "13176AA"))
AND (([(Table) Denton Routing].[SHIP DAY])="MONDAY"
AND [(Table) Denton Routing].Final_Dest = "DENTON"));
Here's the code:
Private Sub cmdRunQuery_Click()
Dim Db As DAO.Database
Dim QD As QueryDef
Dim where As Variant
Set Db = CurrentDb()
' Delete the existing dynamic query; trap the error if the query does
' not exist.
'On Error Resume Next
'Db.QueryDefs.Delete ("Dynamic_Query")
'On Error GoTo 0
where = Null
where = "WHERE ((([(Table) Denton Routing].UNIQUE_LANE_ID) In (SELECT UNIQUE_LANE_ID FROM [(Table) Denton Routing] where"
where = where & " [(Table) Denton Routing].[Location_ID]= '" + Me![Text35] + "'"
where = where & " AND [(Table) Denton Routing].[Final_Dest]= '" + Me![List29] + "'"
where = where & " AND [(Table) Denton Routing].[Ship Day]= '" + Me![Combo46] + "'))"
MsgBox (where)
Set QD = Db.CreateQueryDef("Dynamic_Query", _
"Select SELECT [(Table) Denton Routing].LOCATION_ID, [(Table) Location].NAME, [(Table) Location].CITY, [(Table) Location].STATE, " & _
" [(Table) Location].REGION, [(Table) Denton Routing].UNIQUE_LANE_ID, [(Table) Denton Routing].CARRIER_ID, [(Table) Denton Routing].[SHIP DAY], " & _
" [(Table) Denton Routing].[DELIVERY DAY], [(Table) Denton Routing].[TIME AT LOCATION], [(Table) Denton Routing].STOP_NUM, " & _
" [(Table) Denton Routing].NO_OFF_STOPS FROM [(Table) Location] INNER JOIN [(Table) Denton Routing] ON [(Table) Location].[LOCATION ID] = " & _
" [(Table) Denton Routing].LOCATION_ID " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query"
End Sub
View 5 Replies
View Related
Dec 16, 2005
As much as I would like to tell my boss he's insane I'd also like to keep my job. I know this is possible, but it sounds very complicated. Basically, I need a way to generate queries on the fly. This is geared towards someone who doesn't know SQL of course. I know basically what he's looking for, comparing sales over some period of time. However, he might want annual numbers, quarterly numbers, percentages, overages/shortages, and any kind of sales related query you can think of. The only way I figure I can do that is to have a form build the SQL statement, save the SQL statement as a query, then have him open the query. Is there another way I can do this without necessarily saving a query first?
View 6 Replies
View Related
Jun 14, 2006
Dear all,
I want to generate a different drop down box dependent upon the criteria of another selection on a form.
For example, when the user selects "fish" from the drop down, the query criteria lists only those animals whose animal type is fish. If the user selects "reptile" from the first drop down, the query criteria changes, so the next drop down box lists those animals whose animal type is reptile.
To summarise:
Choices in drop down 1:
Fish
Reptile
Choices in drop down 2:
If Drop down 1 = Fish
Cod
Haddock
Shark
If Drop down 1 = reptile
Snake
Lizard
Is this possible, and how do I go about structuring it.
Thanks,
Steve
View 1 Replies
View Related
Jun 14, 2006
Dear all,
I want to generate a different drop down box dependent upon the criteria of another selection on a form.
For example, when the user selects "fish" from the drop down, the query criteria lists only those animals whose animal type is fish. If the user selects "reptile" from the first drop down, the query criteria changes, so the next drop down box lists those animals whose animal type is reptile.
To summarise:
Choices in drop down 1:
Fish
Reptile
Choices in drop down 2:
If Drop down 1 = Fish
Cod
Haddock
Shark
If Drop down 1 = reptile
Snake
Lizard
Is this possible, and how do I go about structuring it.
Thanks,
Steve
View 1 Replies
View Related
Jun 14, 2006
Dear all,
I want to generate a different drop down box dependent upon the criteria of another selection on a form.
For example, when the user selects "fish" from the drop down, the query criteria lists only those animals whose animal type is fish. If the user selects "reptile" from the first drop down, the query criteria changes, so the next drop down box lists those animals whose animal type is reptile.
To summarise:
Choices in drop down 1:
Fish
Reptile
Choices in drop down 2:
If Drop down 1 = Fish
Cod
Haddock
Shark
If Drop down 1 = reptile
Snake
Lizard
Is this possible, and how do I go about structuring it.
Thanks,
Steve
View 1 Replies
View Related
Jun 14, 2006
Hi,
I have the following query:
SELECT *
FROM qryForCreateReportSold
WHERE (((qryForCreateReportSold.Fund) Like '*STP*') AND ((qryForCreateReportSold.[Sale Date])>=#7/1/2005# And (qryForCreateReportSold.[Sale Date])<=#6/14/2006#));
The user fills out a form and hits run query and the value STP gets passed into the query criteria.
My situation is that there are claaifications such as S/STP, E/STP etc and the problem is all these projects also show up whereas I want projects ONLY
under the STP classification.
I guess the problem is due to the fact of the *STP* which means pick all records that have an STP in them...
If I manually get into the query and remove the * * then the projects ONLY with STP show up.( which is exactly what I need)
Can some one suggest something to remedy the situation.
Thanks
View 4 Replies
View Related
Jul 19, 2005
After reading some of what people already had in this forum, I was still left a little confused. Say I have a form with three combo boxes and I want the user to be able to select something from all 3 or just one at a time. Which kind of query should I be using. Right now I have a select query, but it doesn't work right due to the Where Clause, which will work 1 way or the other, but not both(that I know of). Any advice would be appreciated. Thanks.
Bobby
View 2 Replies
View Related
Dec 7, 2005
My boss asked me to do something today that stumped me. He wanted me to make a query, where when you're selecting the parameters you could chose whether or not you wanted certain fields to be visible.
On top of that I need to be able to chose whether I want it to be shown on screen in a query, printed as a report, or exported to excel. This is what makes it tricky. I could easily do a bunch of If...then statements to generate the SQL query and only show the fields necessary, however the report and the query view both have the fields on them in design view. If I cut the fields out of the query at runtime, then I get the nasty "Input parameter" popup box.
So I've been bashing my head off the keyboard trying to figure out how I'm going to do this. One other theory I tried to put into use was having them left on the form/report, but if the checkboxes were not selected then it would set them to visible = false at runtime. Sounds good in theory, however Access decided to ignore this block of code and just show the fields regardless of what I told it to do.
So if anyone could provide me with some insight or suggestions, I would certainly appreciate it.
Thanks in advance!
View 1 Replies
View Related
Aug 24, 2006
Not sure if this is possible but I figured I'd ask.
I currently have a form/update query that allows me to change a persons last name in a record depending on the value I enter in an unbound text box on the form.
Is it possible to make this query dynamic so I change field names on the fly instead of it programmed for lastname. Example, I want to change firstname instead of lastname.
Would I need another query for firstnames?
View 3 Replies
View Related
Sep 13, 2007
Hello Access Expert
Is it possible to call a function as the criteria for a field in a query.
I have used a function that returns a boolean into the criteria field and it worked but when I create a string expression for the criteria field it doesn't seem to work.
For example I have created this simple function to generate a criteria
Public Function AcceptedDays() As Variant
Dim Days As String
Days = ""
If Forms![DrillDown]![Sunday] Then
Days = "1"
End If
If Forms![DrillDown]![Monday] Then
If Days = "" Then
Days = "2"
Else
Days = Days + " OR 2"
End If
End If
If Forms![DrillDown]![Tuesday] Then
If Days = "" Then
Days = "3"
Else
Days = Days + " OR 3"
End If
End If
If Forms![DrillDown]![Wednesday] Then
If Days = "" Then
Days = "4"
Else
Days = Days + " OR 4"
End If
End If
If Forms![DrillDown]![Thursday] Then
If Days = "" Then
Days = "5"
Else
Days = Days + " OR 5"
End If
End If
If Forms![DrillDown]![Friday] Then
If Days = "" Then
Days = "6"
Else
Days = Days + " OR 6"
End If
End If
If Forms![DrillDown]![Saturday] Then
If Days = "" Then
Days = "7"
Else
Days = Days + " OR 7"
End If
End If
AcceptedDays = Days
End Function
If however, I literarly type what the function produces into the query grid field the query generates the correct result. On the other hand if I send the result of the above function the query doesn't work. Any explanations ?
Thanks so much.
View 3 Replies
View Related
Feb 11, 2007
Hi, I'm building a form where the user can choose various options to build up the query they want. It's going to be easier this way because the different combinations they might want would mean me writing well over 100 different queries. So I think I can build the sql statement in vb and then get a recordset back with the results, but the question is how to display it. I can't just assign the recordset to an existing form because the fields to be displayed will be different for different options. Ideally I'd like it to open just as if I had run:
DoCmd.OpenQuery <name of the query>
But it doesn't look like this is going to work for a query I've generated in the code, even if I use a querydef object (correct me if I'm wrong?). So does anyone know how I would go about doing this? (It would also be fine if the results appeared in a subform on the form I'm calling the query from, but I can't see how to do this either).
Any help gratefully appreciated.
Sim Bamford
View 2 Replies
View Related
Feb 20, 2005
I have the following query, which I know works.
The query is called "products_due_in_query"
SELECT Sum([Supplier_order_line].[quantity]) AS [Due_in]
FROM Supplier_order_line
WHERE (((Supplier_order_line.order_line_status)="Awaiting delivery") AND ((Supplier_order_line.product_number)=[product_number_combo]));
product_number_combo is a combobox on a form called "product_enquiry".
I have a text box on "product_enquiry", which I want to show the value of "Due In" (from the query), when the user selects a product_number from the product_number_combo combobox.
How can I do this? I have tried setting the text box control source to products_due_in_query.Due_in but all I get is "#name".
I thought about doing it with VB code, but dont know how.
I have used .recordSource before, to assign a value to a combobox, but I dont know how to do it for a text box.
Can anyone help?
Thanks very much.
View 1 Replies
View Related
Apr 29, 2006
Hi, hope this is a reasonable thing for semi-beginner to intermediate person to do.
Our MS Access 2000 db shared on a server stores projects, categories of projects, clients, contractors doing the projects. Contractors are offered projects within their chosen categories based on their order on the list (last company who was offered a contract goes to bottom of list, like that).
Currently we print out a phone list of the contractors (in desc. date order of the most recent offer accepted or refused), then we phone down the list until someone accepts. Later we enter all those offers in order on frmOffers to store the date & time stamp of that offer.
Problem is, several contracts can be on offer on a given day, so when a company accepts/declines Project A, that company is still showing as high on the (paper) list for Project B. So when we have 3 people phoning they don't know they've offered multiple contracts to the same company this morning.
Is it possible for a form "Offers" to be based on a query that keeps updating like that? For example, you open frmProject to display details of a certain Project, cmd button to open frmOffers (continuous form, showing all offers so far with company, phone #, "accept"/"decline" & date/time stamp). Then, when you tab to the next record, can you have it show which company is next in line (even as the person beside you has updated their frmOffers in the meantime)?
Would you need to have a separate table storing only the most recent offer for each company? If so, how would you get that updated all the time?
Anything you could offer to point me in the right direction would be very much appreciated!!
Thanks,
View 1 Replies
View Related
Mar 31, 2008
Can anyone help?
want to establish the criteria for a report to include 6 months (into the future) from todays date. Have tried dateadd function but this doesn't seem to allow dynamic (automatic) start of period date
Cheers
View 3 Replies
View Related
