Queries :: CountIf Between Dates In Crosstab Query
Nov 4, 2013
I have generated a table using a crosstab query and it gives the information as below
12/10 12/11 12/12 13/01
Part 1 1 2
Part 2 4 4 4
Part 3 5
So basically part, qty used, year/month used - it works fine up to this point.
Now I want to either add more columns to the crosstab query to count number of times Part 1 was used in last 12 months & 24-13 months
Or
Create another query to do this.
But I want the count function to start counting from current month to last 12 months and from month 13 to month 24 (in another column).
The problem I am having is that this query will run every month and I don't want to update the column headers (in query property sheet) instead I want query to pick this up automatically.
I want to create a cosstab query with dates for the next 12 months accross the top as columns and employee names down the side as rows. I then want to populate with data showing what each person is programed to do under the dates. For instance trainer 1 is delivering training from the 23/06-30/06 then designing a course from 02/07-10/07. that type of thing.
I could represent an activity using a colour i.e. yellow for delivery, green for design etc.
Also do I need to create a table with all the dates? Is there a quick way of doing this?
I have a crosstab query with the following column which calculates the number of days an employee took to perform a task. Problem is that it is also counting weekends and holidays.
Field: LogDate Table: tblWorkLog Total: Count Corsstab: Value
Is there anyway to exclude weekends and holidays from that value?
I have a table, tblHolidays, with the field, HolidayDate. Is there a function that I can use like the WeekDay and use that with the HolidayDate field so that I can only get the Number of Work days?
***Addition***
Sorry I would also like to clarify something I have only one date field. What's happening is the user logs in and inputs what he did for the day, and the date is logged.
So the crosstab query sums up those days, but I would like to exclude weekends and holidays.
I print a report based on Query1, with the selection criteria for dates being taken from 2 fields on a form. The selection criteria reads:-
>=[Forms]![DTdates]![StartDate] And <=[Forms]![DTdates]![EndDate]
Simple and it works fine.
I now need to base a second report on a Crosstab query based on Query1. Follow me?
Now, I have tried using the selection criteria in both Query1 and in Crosstab_Query1 and I get the error message:- "The Microsoft Jet database does not recognise 'Forms!DTdates!StartDate' as a valid field name or expression."
How do I get round this or can I?
The bottom like is I want to fire off printing the report from dates input to a form.
TRANSFORM Count(tblclientcontacts.ENTERED_BY) AS CountOfENTEREDBY SELECT "Count" AS [ENTERED_BY+OFFICER1+OFFICER2+OFFICER3] FROM tblclientcontacts GROUP BY "Count" PIVOT tblclientcontacts.ENTERED_BY;
However I need this to show data that is for a certain date. I have a field named CONTACT_DATE which has the date in this format: xx/xx/xxxx.
I have been messing with the above and havent had any luck trying to get the crosstab query to only query say from months 10,11,12 or even for month 10.
Anyone have any pointers for me or a direction I can look at.
I keep getting not part of a aggregate function error, or syntax error in my attempts.
I am trying to count how many plans a retail chain has over its stores.
So WeekNo | StoreNo | Product |PlanNo 1 | 10001 | Dog | A 1 | 10001 | Cat | B 1 | 20008 | Dog | B 1 | 30005 | Dog | B
Query/report to show Product | PlanNo A | PlanNo B Dog | 1 | 2 Cat | 0 | 1
Numbers in Query/report are a count of Stores selling the product
One of the issues is that the data is duplicated over the weeks, I'm thinking to use max on week number so It only looks at the last available data. And how to write the query.
Currently I'm doing this PlanA: Sum(IIf([PlanNo]="A",1,0) PlanB: Sum(IIf([PlanNo]="B",1,0) . . .
But it looks at the whole of the table for all the weeks...
I have a crosstab query to summarise the counted string values from another query: E.g.;
TRANSFORM Count(Table1.Viable) AS CountOfViable SELECT QryTable01.productName FROM QryTable01 GROUP BY QryTable01.productName PIVOT Table1.Viable;
As there are some null values returned (ie blank cell in the pivot table)
I used the Nz function to make this zero but when the query results are used in a report I want to add the rows to get row totals... but the result is as if they were string values;
So if I add a text box in the report with = [viable] + [Not viable] t Then the report row with the values:
Below is the SQL view of a Crosstab Query I am running. However I am lost at how to keep it from creating a serpate row for the same data on the column. For instance in the row data if I have an ATM batch come in for 1111 on 9/16 and then one come in for 1111 on 9/17 instead of keeping it on the same row it drops 9/17 down to the next row. Is there a way to keep the data on the same row?
Code: PARAMETERS [forms]![frmReporting]![txtDatefrom] DateTime, [forms]![frmReporting]![txtDateTo] DateTime; TRANSFORM Sum(Query1.CountOfATM) AS SumOfCountOfATM SELECT Query1.ATM, Query1.Store, Query1.Type, Sum(Query1.CountOfATM) AS [Total Of CountOfATM] FROM Query1 WHERE (((Query1.[Date Stamp])>=[forms]![frmReporting]![txtDateFrom] And (Query1.[Date Stamp])<=[forms]![frmReporting]![txtDateTo])) OR (((Query1.[Date Stamp]) Is Null)) GROUP BY Query1.ATM, Query1.Store, Query1.Type, Query1.[Date Stamp], Query1.[Date Stamp] PIVOT Format([Date Stamp],"Short Date");
I want to show this in a report, similar to an spreadsheet, with:
ProductionForecastProductId = Rows ProductionForecastMonth = Columns ProductionForecastQuantity = Data
I thought of using a crosstab query like this:
Code: TRANSFORM avg(tblProductionForecast.[ProductionForecastQuantity]) AS AvgOfProductionForecastQuantity SELECT tblProductionForecast.[ProductionForecastProductId] FROM tblProductionForecast GROUP BY tblProductionForecast.[ProductionForecastProductId] PIVOT tblProductionForecast.[ProductionForecastMonth];
but I don't want the average of ProductionForecastQuantity. I want the actual value!
Is a crosstab query the wrong method to use?
If no, how do I show the actual value of ProductionForecastQuantity
I have a crosstab query that I would like to sum data across. Each row is a seperate experimental product, and each column is a test phase that each product goes through. The data is the number of days each product was in each test phase. What I need is the sum total of days that all products were in all phases. Think of this table as a single item, and each row is a slightly different version of the item to test. So what I need is essentially how many days this item has been in testing across all variations.
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.
I have a created a crosstab query which gives me the results I need, but I want to sort the row headings differently. These are not numbers, but machine sizes which range from 4 Metre to 20 Metre. Currently, the crosstab gives me:
10 Metre 12 Metre 14 Metre 17 Metre 20 Metre 3 Metre 4 Metre 5 Metre etc
But I need to show it as:
3 Metre 4 Metre 5 Metre 10 Metre 12 Metre 14 Metre 17 Metre etc.
The field is short text data type and data comes from an ODBC linked table to SQL server table, and I am using Access 2013.
I've setup some crosstab queries with good success and set column headings that appear with blank spaces when there's no data as expected, but I have 1 report that is formatted with set row headings (its a specifically formatted report that i need to produce, that i can't just change).So I need to basically do the same but with the row headings set (I have got the crosstab working, just not showing the rows with no data as yet).
I have created a cross tab query that contains a row heading for Entity and Total Cases. I would like to have a percentage of the Case Total for Each Category as well as the count for each category. It works fine for just the counts and here is the sql behind it:
TRANSFORM Count(CASEDATA.CASESTATUS) AS CountOfCASESTATUS SELECT IUIDCODES.CODEDESCRIPTION, Count(CASEDATA.CASETYPECODE) AS CountOfCASETYPECODE FROM (CASEDATA INNER JOIN CASESTATUSCODES ON CASEDATA.CASESTATUS = CASESTATUSCODES.CODEID) INNER JOIN IUIDCODES ON CASEDATA.IUID = IUIDCODES.CODEID WHERE (((CASEDATA.CASETYPECODE)=63) AND ((CASEDATA.FYSTATUS)=6 Or (CASEDATA.FYSTATUS)=7 Or (CASEDATA.FYSTATUS)=8)) GROUP BY IUIDCODES.CODEDESCRIPTION PIVOT CASESTATUSCODES.CODEDESCRIPTION;
I built a query with the wizard to group by Reason for testing
The SQl was
TRANSFORM Count(Cultureworkload.IRLNumber) AS CountOfIRLNumber SELECT Cultureworkload.Reasonfortesting, Count(Cultureworkload.IRLNumber) AS [Total Of IRLNumber] FROM Cultureworkload GROUP BY Cultureworkload.Reasonfortesting PIVOT Format([Reception],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");
Although I filtered reception field by month to 1st quarter it shows empty months like Apr,May Jun etc.
I have created a crosstab query that I will use in a subform and a subreport. However, Access won't let me add the fields to the query that I need to link the subform to the form. Is it possible to add fields to a crosstab query?
This crosstab query has the employee's name as a row heading, each calendar day as a column heading and the sum of Hours worked as the value.
TRANSFORM Sum(tbl_Tracker.Hours) AS SumOfHours SELECT tbl_Employee_Master.Full_Name FROM tbl_Employee_Master INNER JOIN tbl_Tracker ON tbl_Employee_Master.user_no = tbl_Tracker.User_Id GROUP BY tbl_Employee_Master.Full_Name ORDER BY tbl_Employee_Master.Full_Name, tbl_Tracker.TheDate PIVOT tbl_Tracker.TheDate;
It returns data that looks like this: FullName 2/3/2014 2/4/2013 2/5/2014 John Smith 8 0 8 Joe Jones 0 8 0 Cathy Wise 8 8 8
I want the data returned to look like this: 2/3/2014 2/4/2014 2/5/2014 John Smith Joe Jones John Smith Cathy Wise Cathy Wise Cathy Wise
The hours worked are not important - only the names associated with the dates.
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"..
TRANSFORM Sum([rpt LEAD TYPE SUCCESS RATE2].rec_cnt) AS SumOfrec_cnt SELECT [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date FROM [rpt LEAD TYPE SUCCESS RATE2] GROUP BY [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date PIVOT [rpt LEAD TYPE SUCCESS RATE2].Results;
I get valid results.
When I run the form it prompts me 3 times for each start date and end date. Then I get the following error :The Microsoft Access database engine does not recognize '' as a valid field name or expression.
I have been tasked to create a DB for my unit. I have created a few DB, but I am a novice at best. I need a crosstab Query to count the number of records for each FY. The Army's FY is from Oct -Sep. I only need to show the the total number of record for the previous FY in a Report and on a form.
I went into the properties if that field inside the query design and changed it to Standard with 0 decimal places and it worked fine.
But when I based a crosstab query off the query that contained the above calculated field, I cannot seem to get the numbers to format correctly. 1231313.424 is shown instead 1,231,313 and I don't have a line in the Properties window to even change the decimal places. It doesn't recognize when I change the format to Standard. I have tried using Round([ProjRevNRC]) which gets rid of the decimal places as desired but does not show commas.
I'm sure it's a simple part of the Round expression that I am missing but nothing has worked.
I have a cross tab query that takes input from the user. The code is as under:
PARAMETERS [START_MONTH] DateTime, [END_MONTH] DateTime; TRANSFORM Round(Sum(([A_GAS_m3]+[NA_GAS_m3])/1000000),3) AS GAS_MMSCM SELECT maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block FROM maindata RIGHT JOIN PRODUCTION ON maindata.field_block=PRODUCTION.FIELD_BLOCK WHERE ((PRODUCTION.MONTH) Between Format([START_MONTH],"dd-mm-yyyy") And Format([END_MONTH],"dd-mm-yyyy"))) GROUP BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block ORDER BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block PIVOT PRODUCTION.ACTIVITY;
I am facing 3 problems with the above code:
1. Access prompts to enter the start month and end month even when I click the save button. 2. When I run the query it prompts to enter the start month and end month twice. 3. I have generated a report based on the above query. When I run the report it keeps on prompting for start month and end month and does not give the final result.
problem when I am trying to use Form to filter data from a cross tab query though I have already defined the parameters. The SQL is as under :
//Code start PARAMETERS [FORM]![START_MONTH] DateTime, [FORM]![END_MONTH] DateTime; TRANSFORM Round(Sum(CLng([A_GAS_m3]+[NA_GAS_m3])/1000000),3) AS GAS_MMSCM SELECT maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block FROM maindata RIGHT JOIN PRODUCTION ON maindata.field_block = PRODUCTION.FIELD_BLOCK WHERE (((maindata.field_block)<>"Panna" And (maindata.field_block)<>"Mukta" And (maindata.field_block)<>"CB-ONN-2000/2(NSA)" And (maindata.field_block)<>"CB-ONN-2000/2(BHEEMA)") AND ((PRODUCTION.MONTH) Between Format([FORM]![START_MONTH],"dd-mm-yyyy") And Format([FORM]![END_MONTH],"dd-mm-yyyy"))) GROUP BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block ORDER BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block PIVOT PRODUCTION.ACTIVITY; //Code End
When I save it prompts for the Start and End Month and When I run the query it pops up the form twice...
