Dynamic Query Based On Anthoer Crosstab
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 Replies
ADVERTISEMENT
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
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
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 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 2, 2005
Hello All,
How can I create reports in Access based on dynamic queries? I did a lot of search on this but couldn't find anything reasonable.
Any help to get me started will be extremely appreciated.
Thanks
View 1 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
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
May 31, 2006
:confused:
I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;
-----------
qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
Thanks for you time! :)
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
Feb 10, 2012
I am writing a module to send information to Excel, and the information is based on a Crosstab query. But I keep getting a "syntax error in the TRANSFORM statement".
Function Edt_Royalties() As Integer
Dim AplicExcel As Excel.Application
Dim Planilha As Excel.WorkSheet
Dim stArq As String
Dim DB As Database
[Code] .....
View 1 Replies
View Related
Feb 13, 2008
Hi,
I have a table that looks something like this:
Company Product Sales
1 A 5
1 B 2
1 C 7
2 A 8
2 B 1
2 C 2
I want a query to produce a table showing Products A though D which is
the sum of Products A and B. Result:
Company Product Sales
1 A 5
1 B 2
1 C 7
1 D 7
2 A 8
2 B 1
2 C 2
2 D 9
I have many rows and many summations and the current union query and
supporting queries are pretty messy and not easily changeable. What
I'd like to do is have a third table which would hold the summed
product names and the formula used to get to it (Product D, A + B) and
have a query that can be dynamically based off of that table. Do I
need to use VBA? Does anyone have a better idea? Any help or even a
good search term would be appreciated!
Thanks!
Nathan
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
Oct 14, 2014
I'm currently working on a project that calls for a graph that is based on an equation which the users can change while in the database.
the equation is basically an annual compound interest formula: 65*(1+x)^([YearNumber]-1), where x is the rate of change as a decimal, and what the users can change.
I've tried to use a simple query with this equation as one of the expressions, where the x is displayed as [Rate as decimal], but when I do this, I get an error on the table: the database engine does not recognize [Rate as Decimal] as a valid field name or expression.
I've also tried tying the equation to an unbound text box on a form called "RateasDecimal", with a button that opens the form with the graph on it, but I get the same error - except [Rate as Decimal] is replaced with "forms!form1!RateasDecimal".
is it possible to create a graph like this in access.
View 2 Replies
View Related
Jul 26, 2012
What I am wanting is to be able to build dynamic form content/elements based on entries on a table. This is for a gym membership system. What this form is going to be used for is to allow the front desk to scan a membership card which then performs a search on the database. Part of this search is going to be on a table that contains various add-on classes, tanning sessions, etc that a member can add beyond their base membership.
On the left side of the form, I will display a picture of the member and their name. What I am wanting to do on the right side of the form is to build a dynamic list of the add-on perks they are enrolled in.
I know I could hard program elements on the form to be visible or invisible, but that would be static and leave gaps when I have to turn things off b/c that member isn't enrolled in that class, etc.
Is this even possible with Access and VBA? I know I could do this sort of thing with a webpage using PHP, PERL or whatever. I don't know the limits of Access Forms and VBA.
View 5 Replies
View Related
Oct 3, 2005
Hi folk
I hope someone can come up with a suggestion for this one.
I have a Crosstab query in my database which looks for the age of accounts on each status. The query itself runs fine but the problem is if there are no accounts on that status. For instance if there are no accounts on an open status then the Open Status isn't shown.
This means that when my Excel Query looks for the Open value from the query I get a Microsoft Jet query error.
Is there any way to force the crosstab to display all the statuses regardless of whether there is any accounts on it.
As I say any suggestions are appreciated.
JC
View 1 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
