Queries :: Query Error When Data Changes In Crosstab Source Query
Aug 12, 2014
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.
View Replies
ADVERTISEMENT
Feb 4, 2015
I have simple crosstab query that I use to pull data about how many products is selled in one day/week/month and so on. On entry form I have two critical date fields, Starting/Ending date. It all works perfect except when I put indetical values in both those fields.
For example I have sold over 100 items on 4th of february 2015. year. So, in Starting/Ending date field of entry form, I put value from 04/02/2015 to 04/02/2015 in order to see how much items was selled that day.
This is the error I receive:
Quote:
An error was encountered
Description: The Microsoft Office Access database engine does not recognize '' as a valid field name or expression.
Error Number: 3070
What am I doing wrong?
View 5 Replies
View Related
Apr 21, 2005
I’m trying to create a budgeting tool for my company. We operate in many countries and budget at the country level so I’ve set up a one-to-many relationship between my tblCOUNTRY and my tblEXPENSE. My tlbEXPENSE has fields ExpenseID, CountryID, Month, and Value.
In order to create a form where each country manager is able to fill out, and make changes to their budget for the next year I would like to create a crosstab query so they can have a horizontal view of the year broken out by month. The problem I’m running into is that crosstab queries are aggregates so they do not allow data entry.
The only solution I’ve thought of is to create a make table query from the crosstab query, but I still run into several problems. First, I need all twelve months to show on my crosstab even if there is not an expense yet for that month, and once I have it broken out by month how can I transpose it back into a form to be able to append it to my tblEXPENSE?
I don’t know if this is even possible with the way I have set up my tables. I guess first I’m wondering if I should change the setup of my tblEXPENSES so it has a field for each month although this would make it harder to do an annual budget rollup based on country? Or should I make a form in Excel for all the country managers to fill out, compile all the data, and append it to my database? Thanks in advance for any advice.
View 4 Replies
View Related
Sep 17, 2014
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");
View 4 Replies
View Related
Nov 12, 2013
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.
View 1 Replies
View Related
Jul 27, 2013
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...
View 1 Replies
View Related
May 7, 2013
I have attached part of my database.
I need to show the complete rows for all the status in each year with or without any record/data.
View 5 Replies
View Related
May 20, 2014
My data is on Sharepoint. I have a simple Crosstab query that works well unless I choose the option "Cache List Data". In that case I get a Type Mismatch error.
Code:
TRANSFORM Sum(PivotData.QuoteTotal) AS SumOfQuoteTotal
SELECT PivotData.FullName
FROM PivotData
GROUP BY PivotData.FullName
PIVOT PivotData.StatusText;
View 1 Replies
View Related
Jun 3, 2014
I am trying to construct a crosstab that averages a calculated field from a previous query. It is returning a "Data Type Mismatch" message.
The field I am trying to average is a subtraction of dates to find total days. I assume my field is not a number so I have tried to wrap it in CDbl() to change the type.
The formula is
Code:
CASE_DAYS: CDbl(IIf([Actual Close Date]-[Creation Date]>=0,[Actual Close Date]-[Creation Date],""))
View 5 Replies
View Related
Mar 2, 2006
I got an error (The Microsoft Jet database engine does not recognize <name> as a valid field name or expression. (Error 3070)
) when I tried to use the ranking system described by meloncolly and sbaxter in previous threads, created a query using the rank, and then tried to make a crosstab query with it.
Is there some way to get around this?
I attached the test database to this.
View 4 Replies
View Related
Mar 4, 2015
In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
View 1 Replies
View Related
Aug 12, 2015
I have a query where these are the fields:
ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
View 4 Replies
View Related
Jul 16, 2015
is it possible to use a sum select query in the control source field of a text box. I have a query that works fine in a combo text box but I keep getting a 'syntax error with subquery parenthesis' when I try and build on in a normal text box.
I am building the query using query design view and the query works and then I'm copying the SQL code behind the query into the text box field and access reformats it s a bit so not sure it's that's the reason.
R_P_Data_P = Table
approvalNosys = Field in the table
status = Field in the table
score = Field in the table
cmrOverview = Form
[approvalNoSys] = field in form
1 is the criteria
Code:
SELECT R_P_Data_P.approvalNosys, R_P_Data_P.status, Sum(R_P_Data_P.score) AS SumOfscore
FROM R_P_Data_P
GROUP BY R_P_Data_P.approvalNosys, R_P_Data_P.status
HAVING (((R_P_Data_P.approvalNosys)=[forms]![cmrOverview]![approvalNoSys]) AND ((R_P_Data_P.status)="1"));
View 7 Replies
View Related
Mar 17, 2006
Is it possible to modify data values in a cross tab query?
I tried and it won't let me delete or type anything.
Thanks.
View 1 Replies
View Related
Dec 17, 2012
I have a table and a simple query that pulls results from the table. Nothing too crazy. But, if I were to go in and change some of the data/values in the query results it will change the respective data in the table. I know that this cannot be right. What do I have to do to either prevent the ability to change query results and/or prevent any changes in the query from altering the original data in the table.
View 2 Replies
View Related
Jun 19, 2014
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:
Viable Not viable
14 12
displays as 1412
So how do I add the values in the rows???
View 1 Replies
View Related
Apr 16, 2013
I have the table "tblProductionForecast" with the following fields:
ProductionForecastProductId
ProductionForecastMonth
ProductionForecastQuantity
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
If yes, how should I create the report?
View 5 Replies
View Related
Feb 26, 2015
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.
View 4 Replies
View Related
Apr 23, 2014
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).
View 3 Replies
View Related
Jun 23, 2014
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?
View 7 Replies
View Related
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 use the design view to generate queries.
View 1 Replies
View Related
Apr 12, 2013
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;
View 2 Replies
View Related
Apr 17, 2014
I have a table like this
Cultureworkload
ReasonfortestingIRLNumberDiagnostic sputum specimens processedFollow up specimens processedReception
Follow up7/20130102-Jan-13
Diagnosis8/20131002-Jan-13
Follow up9/20131002-Jan-13
Diagnosis10/20131002-feb-13
Follow up11/20131003-feb-13
Diagnosis14/20131003-Mar-13
Diagnosis15/20131003-Apr-13
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.
View 3 Replies
View Related
Oct 9, 2014
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?
View 6 Replies
View Related
Feb 12, 2014
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.
View 2 Replies
View Related
Oct 5, 2013
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"..
View 3 Replies
View Related
