Im trying to create a crosstab query with parameter entry from a form.
I wish to be able to specify a date (using Microsoft DTPicker control) and a shift (using a combo box to the relevant table) to make the selections.
These two selections on the form are the criteria for the cross tab query which returns the downtime totals for each category for each line.
I have defined the parameters for the query by putting which seems to work for most people from what I've read, and has worked for me on previous forms in the same database (although not in parameter crosstabs....this is my 1st parameter crosstab)
However, I still get no result from the query. Without the parameters, the crosstab query works fine.
Help please...I'm really stuck. Here is the sql for the query, can anyone see anything that is obviously wrong with it?
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 have a table that has three columns: Nationality, Gender, and Date. First I had the problem of grouping the Nationality column by Gender. That i resolved by creating a crosstab query. Now, I want to put a criteria on Date field so that when the query is run it will ask for the date input and will only show the Nationality and Gender result for specific dates. But when I write something in Criteria field it shows an error saying: "The Microsoft Access database engine does not recognize "[Registration Date]" as a valid field name or expression"..
I have the following database, in my query I am trying to do a calculation as the value parameter within the corsstab.
Ultimately I an trying to get month as the Row Header (from date) and Category as the column header. The Value inside the table I am trying to calculate is A/(B-C-D)...
I have a report based on a crosstab query with a subreport based on a second crosstab query both the crosstab queries have the same 3 parameters (2 as string, 1 as boolean) which I pass through a dialog form that closes after the report is opened (in preview).looks like everything works fine at least for the first page of the report but when I try change page, it prompts me to re-enter the parameters. Of course, if a do not close the parameters form I will not have this problem but that is a popup dialog which I do not want to close manually after the operation is done
So I tried to pass the form input first to a Public variable then to a Public Function but I was not able to reference neither of them in the PARAMETER statement of the 2 Xtab queries..As a function, each of the following declarations prompts for input at runtime::
MyFunction, [MyFunction], [MyFunction()], [=MyFunction()] while the following are not accepted (syntax error) MyFunction(), =MyFunction()
I have a query which uses several IIF statements in different fields the end field being a simple compare, if one value is greater than another+5% then no="OK" yes="Check" This is the final IIF
I only want to show those records requiring the "Check" but if I use "Check" in my criteria I am asked for "EstPrice" or "BaseRate" as these are based on other IIf statements. I have tried using a form to feed the parameter but still the same result. I think I should be joining together the different IIFs but am finding this a difficult option so ...
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
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
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"
I have a form whose data source is a select query, q3, that is built from 2 other select queries. I'll call them q1, q2, and q3. q1 is a parameter query where I enter a "Cutoff Date" that the 3 queries manipulte and generate the desired results that appear in the form. The problem is that I don't know how to capture the parameter "Cutoff Date" from q1 to display on the form.
: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;
I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.
I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .
My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.
Hello All, Your Help Required. I have send you a Database, in which I have used Crosstab query, I just want when I select the report from switchboard, and enter the datefrom / dateto (fields names) than click the preview report. Required report is open.
I have faced following problems 1-When I have selected the report and click the preview button. Report is not opened. 2-I have used cross tab query and link with the switchboard. But when I have run the query this msg is appeared “Microsoft Jet engine Does not recognized these field(name)”
I am am wondering if it is possible to create a crosstab query that displays alphanumeric values and not numeric (computational) values.
Ex: Table contains the following details:
Name Branch Bob 111 Bob 222 Joe 333 Pam 444
I want to use a crosstab so I can view the data as follows:
Name Branch1 Branch2 Bob 111 222 Joe 333 Pam 444
Is this possible? I've been playing around with it and it doesnt look doable. Perhaps there is a better way of getting the data into this format? Any suggestions would be greatly appreciated!
I'm trying to create a crosstab query with criteria that refers to a combo box on a form to allow me to filter data before the query is created. However when I refer to the combo box (e.g. = forms!frmSite!CmbSiteName) I am told that access does not recognise this as a 'valid field name or expression'. With a normal select query I dont have the same problem. Is there a way around this?
Admittedly this is my first time doing a crosstab query, but I have both my column and rowe headers set to group by, I kinda assumed this would group them in much the same way a pivot table does in excel
Any help on this would be great
Hmm, doesnt want to keep the spacing in it :( hopefully you can get the idea
I have a report based on a crosstab query and the data changes every time the report is run even though the data is not updated. Can anyone shed some light?
I need help with a crosstab query ( :eek: ). I would like the column headings to be the last 6 months, the row headings to be billers, and the data in the middle to be both the date that a payment was made (falling within the month headings) and also the amount paid in that payment. Sometimes there might be more than one payment to a biller in a month or there might be no payments to that biller in a month. All of the raw data needed is in one table. I have read about crosstab queries, tried to use the wizard, and looked at examples but I can't get it to work:confused: . Could someone show me how this should be written? :)
I need a query which displays data as a crosstab query would but gives me the capability to edit data in the query. Basicaly, I have a Resource column, a Month column, and an Allocation column (among others). I need the months to appear in columns and the allocations to be summed by month. (Months are never repeated though, so it's not really a sum...) Can anyone help me out on this?
My crosstab query doesn't take the textbox value from the form. It says it doesnt recognize [forms]![frmMain]![txtEndDate] as a valid field name or expression. How can I solve this?
TRANSFORM Sum(tblDowntimeHrs.downtimeHrs) AS downtimeHrsOfSum SELECT tblEquipmentType.Equipment_Type FROM tblEquipmentType INNER JOIN (tblCategory INNER JOIN tblDowntimeHrs ON tblCategory.category = tblDowntimeHrs.category) ON tblEquipmentType.type = tblDowntimeHrs.type WHERE (((tblDowntimeHrs.date)>=[Forms]![frmMain]![txtStartDate] And (tblDowntimeHrs.date)<=[forms]![frmMain]![txtEndDate])) GROUP BY tblEquipmentType.Equipment_Type PIVOT tblCategory.category_description;
in my select query with the same table, it works fine:
SELECT tblEquipmentType.Equipment_Type, tblCategory.category_description, Sum(tblDowntimeHrs.downtimeHrs) AS downtimeHrsOfSum FROM tblEquipmentType INNER JOIN (tblCategory INNER JOIN tblDowntimeHrs ON tblCategory.category = tblDowntimeHrs.category) ON tblEquipmentType.type = tblDowntimeHrs.type WHERE (((tblDowntimeHrs.date)>=[Forms]![frmMain]![txtStartDate] And (tblDowntimeHrs.date)<=[forms]![frmMain]![txtEndDate])) GROUP BY tblEquipmentType.Equipment_Type, tblCategory.category_description;
TRANSFORM Sum(VoosAeronTbl.ATE) AS SomaDeATE SELECT AeronaveTbl.Registo FROM MissaoTbl INNER JOIN (AeronaveTbl INNER JOIN VoosAeronTbl ON AeronaveTbl.MatriculaID = VoosAeronTbl.MatriculaID) ON MissaoTbl.MissaoID = VoosAeronTbl.MissaoID GROUP BY AeronaveTbl.Registo PIVOT MissaoTbl.MISSAO;
when I try to select between dates set on a form field like this...
TRANSFORM Sum(VoosAeronTbl.ATE) AS SomaDeATE SELECT AeronaveTbl.Registo FROM MissaoTbl INNER JOIN (AeronaveTbl INNER JOIN VoosAeronTbl ON AeronaveTbl.MatriculaID = VoosAeronTbl.MatriculaID) ON MissaoTbl.MissaoID = VoosAeronTbl.MissaoID WHERE (((VoosAeronTbl.Data) Between [forms]![ContAnFrm]![DataInicio] And [forms]![ContAnFrm]![DataFim])) GROUP BY AeronaveTbl.Registo PIVOT MissaoTbl.MISSAO;
...I get the follow error
microsoft jet database motor does not recognize [forms]![ContAnFrm]![DataInicio] as a field name or valid expression
I am trying to simulate the data as it is presented in Excel, but with Access. This might sound stupid, but I have to draw a chart in Excel by Automation from the data I retrieve in Access. So far, I have made a query, which returns the following (see SourceData.jpg (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=10335&stc=1))
What I have to do next is present the data as follows (see ResultData.jpg (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=10336&stc=1))
How do I do this in SQL? I have a book at home which I am sure explains this in the form of a Count function, or a crosstab query, but will only be able to check it on Thursday. Could somebody maybe suggest something in the meantime?
What I do know, is that the bottom “Summe” row cannot be done by one query. But the column “Total” must be able to be done with a Count function. (Or am I wrong?) :confused:
Its so hot and humid here and no air-con that I cannot think, so please help!!!
