Modifying Data In A CrossTab Query
Mar 17, 2006Is it possible to modify data values in a cross tab query?
I tried and it won't let me delete or type anything.
Thanks.
ADVERTISEMENT
Queries :: Data On 1 Row Crosstab Query
Sep 17, 2014Below 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");
Queries :: Sum Data Across Crosstab Query?
Nov 12, 2013I 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 RelatedQueries :: Query Error When Data Changes In Crosstab Source Query
Aug 12, 2014I 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.
Tables :: Modifying Data Using Another Table
Aug 19, 2014I have a table that has a field that holds a total. Through historic reasons the total in a (relatively) small number of cases is wrong. I have analysed and worked out what the difference should be and have created a table with the value that is missing. I basically want to add the 2 together.
So I have
Table1 Field1 Value1
Table2 Field2 Value2
I want table 1 to be
Table1 Field1 (Value1+Value2)
Programming wise I would just have
Value1 = Value1 + Value2
How do I go about doing this in a query or some other whizzy way?
Forms :: Updating Date When Modifying Data
Nov 20, 2013I want to update my date field in my database everytime when the record is changed. I do not want to do this by using a macro and I cannot make it word by using the before update function. In the before update I have tried the following without success (nothing happens):
Private Sub Modified_Date_BeforeUpdate(Cancel As Integer)
Me![Modified Date] = Now()
End Sub
Multiple Users Modifying And Adding Data
Sep 11, 2013I have created a database for 2/3 users (will progress to more once the system has been smoothed over) who will have the db.file open from 9-5 and will be consistently adding and changing data within 2/3 forms. Most of the time but not always a message comes up saying,
"You do not have exclusive access to this database at this time. Any changes you make may not be allowed to be saved later"
I can understand that it can create data corruption if multiple users are allowed to edit and modify the same data at the same time but surely a database can't be that limited. I thought that splitting the database into a front end on a local server and then a back end on my computer would solve the problem but everyone who is using the front-end still experiences the same problem.
How to be able to overcome issues with multiple users access the same forms and tables? Even when another user is editing one form and another is editing a different form the message will still appear.
Queries :: Using Form To Filter Data In CrossTab Query?
Jul 27, 2013problem 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...
Data Analysis Options DCount - Crosstab Query
Dec 27, 2014I have a table [Control Table] with the fields [Date signed] and [outcome] date signed is formatted as dd/mm/yyyy and the outcome field is a drop down with the options granted, not granted ect
I am looking for a way to present the data using specific date ranges.
I have found 2 possible avenues;
Dcount in a select query:
w/c 01/04/2014 GRANTED: DCount("[Date signed]","[control table]","[Date signed]>=#04/01/2014# And [Date signed] <=#04/06/2014# And [Outcome]='Granted'")
w/c 01/04/2014 Not GRANTED: DCount("[Date signed]","[control table]","[Date signed]>=#04/01/2014# And [Date signed] <=#04/06/2014# And [Outcome]='Not Granted' And [Reason not granted]='Assessed'")
w/c 01/04/2014 Discharged: DCount("[Date signed]","[control table]","[Date signed]>=#04/01/2014# And [Date signed] <=#04/06/2014# And [Reason not granted]='Discharged'")
etc...
But I would need to create the multiple queries 52 times each for the different count value per week
My 2nd option
I have looked at crosstab query, but I cant find a way for it to list the specific dates I need it to query e.g from
01/04/2014 - 06/04/2014
07/04/2014 - 13/04/2014
14/04/2014 - 20/04/2014
etc...
Any tips on Data analysis? I have been able to perform the task previously in excel using If statements but we are now moving to access.
Queries :: Crosstab Query - Showing Rows Without Records / Data
May 7, 2013I 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.
Queries :: Crosstab Query And Cache List Data (sharepoint)
May 20, 2014My 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;
Modifying A Query
Jan 10, 2007I'm far from being an Access expert, so forgive me if I don't make perfect sense. I have a database where questions and pages are entered on a daily basis. I have a table with three columns: data, pages, and questions. I have a query where I can sumarize the data by Month. (I created a query by using the wizard and I chose to summarize by Month). This returns all of the data, summarized by Month. I want to limit it further by year, so that it would return 12 months of data. I am at a loss! I've tried typing [Type Year] but I get no responses. I was able to create a limiting one where I type in the month and year [Type Month and Year] that works great, but it doesn't translate into the year only.
Thanks for any help you can lend!
Michele
Queries :: Average Calculated Field From A Previous Query - Crosstab Data Type Mismatch
Jun 3, 2014I 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],""))
Need Assistance Modifying Date Query
Nov 19, 2006I have a query that is based off of a union query
SELECT clvpertech.clvtech1 AS Technician, Sum(clvpertech.CLVCode) AS CLVs
FROM clvpertech
GROUP BY clvpertech.clvtech1;
This is returning a sum of all the procedures a technician does for the entire database.
This is working.
I need to break this down by day, week , month, and year base off of the datecomp1 or datecomp2 or datecomp3 or datecomp4 fields having a date in them.
I made this query to get the daily results and it works
SELECT clvpertech2.clvtech1 AS Technician, Sum(clvpertech2.CLVCode) AS CLVs
FROM clvpertech2
WHERE (((clvpertech2.datecomp1)=Date$())) OR (((clvpertech2.datecomp2)=Date$())) OR (((clvpertech2.datecomp3)=Date$())) OR (((clvpertech2.datecomp4)=Date$()))
GROUP BY clvpertech2.clvtech1;
How can I add different queries to get the results by breaking down the date field for year, week, and month?
I tried this for the month but I get Invalid Procedure Call when I try to run it???
SELECT clvpertech2.clvtech1 AS Technician, Sum(clvpertech2.CLVCode) AS CLVs
FROM clvpertech2
WHERE (((clvpertech2.datecomp1)=DatePart("mmm",[datecomp1]))) OR (((clvpertech2.datecomp2)=DatePart("mmm",[datecomp2]))) OR (((clvpertech2.datecomp3)=DatePart("mmm",[datecomp3]))) OR (((clvpertech2.datecomp4)=DatePart("mmm",[datecomp4])))
GROUP BY clvpertech2.clvtech1;
Queries :: Modifying Query Output
Feb 13, 2014I am trying to modify the output to only show discontinued items. While the query is in datasheet view, the text is read as "YES" or "NO" under discounted items. So, I tried entering "YES" in criteria while in design view, but keep getting an error message stating, "Data type mismatch in criteria expression".
View 5 Replies View RelatedQuery Execution And Record Modifying
Dec 8, 2014whenever I run/execute a query in Access it is modifying the first record on the table in which it is calling the data from.For example if the first record might contain the following:
Record A: John Doe, Oct, 2014, Account is Active
And lets say I am running a query to pulling records from Nov 2014.The Month and Year Fields in the above example for Record A gets modified to the query search parameters for Nov.Is there some of of record lock or controls that I need to adjust to prevent this from occurring?
Crosstab Query Based On Crosstab??
Sep 21, 2007Hi 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!!!
Modifying Query Fieldname & Criteria Thru Code
Nov 30, 2004Hi,
Self learning trying to modify a query fieldname and criteria thru code.
Have a small form with a button making a copy of a query/s (eventually making about 50 copies).
Once these have been made, would like to open the query up, which I can do, then modify both
the fieldname and the field criteria to suit my needs from parameters set in the form.
How do I do this if it can be done ?
Thanks in advance
Ian T
Modifying Access Pass Through Query Using Querydefs
Feb 8, 2012I have a pass through query which I want to add a parameter to the end.
The pass through query is very long and has to crate temporary tables on SQL Server in order to generate the required output.
I do not want to retype or try and paste all of this code into a vba module.
Can I use querydefs or something in the querydefs collection to extract the code into a new query and append my parameter along the way?
A simple way of looking at this could be
querytemplate:-
"set nocount off
Select UWREF
from policy
where UWREF like '"
parameter:- (picked up from a text box on a form)
"ABC123"
additional text to follow:-
"%' set nocount on"
In a nutshell that's all I want to do, but as stated beforehand the real world query has a shed load of other code.
Queries :: Modifying A Query To Return Only Instances Where There Is More Than 3 Records
Sep 26, 2014I have an existing query, created using the query wizard which works just fine - however, I would like to modify it to return only instances where there are 3 or more records appearing.
Essentially, its an employee history report for a particular action done by those employees, which returns all records between two dates as specified by the user. What I would like to do is only show those employees who have had more than three instances of this action in the given date period.
I am thinking along the lines of DCount? but how it would be phrased in the query?
Filtering CrossTab Data
May 22, 2006Hi
I have set up a query and a Crosstab Query based on that Query. This works fine, but I am trying to filter the data in the crosstab query to only include the account info for the account the user is viewing.
My crosstab looks something like this :
Account # Result 1 Result 2 Result 3 Result 4
I can static a criteria in the base query (Criteria Account = "1") and it works, but I need the criteria in the query to change based on the record the user is viewing on a specific form. I tried to reference the form ; Forms![formname].[account] but the crosstab query will not run that way. Is there a way through code to change the criteria in a query for a specific field ?? Or do I need to create a recordset and use SQL statements ?
Any thoughts ??
Thanks
Filtering CrossTab Data
May 22, 2006Hi
I have set up a query and a Crosstab Query based on that Query. This works fine, but I am trying to filter the data in the crosstab query to only include the account info for the account the user is viewing.
My crosstab looks something like this :
Account # Result 1 Result 2 Result 3 Result 4
I can static a criteria in the base query (Criteria Account = "1") and it works, but I need the criteria in the query to change based on the record the user is viewing on a specific form. I tried to reference the form ; Forms![formname].[account] but the crosstab query will not run that way. Is there a way through code to change the criteria in a query for a specific field ?? Or do I need to create a recordset and use SQL statements ?
Any thoughts ??
Thanks
How To Transform Data To Crosstab-like Format?
Apr 1, 2008May i reach following function by Grid Query design or by SQL
Original:
==============================================
Factory 1-----------Product A
Factory 1-----------Product B
Factory 1-----------Product C
Factory 1-----------Product D
Factory 2-----------Product D
Factory 2-----------Product E
Factory 2-----------Product F
Factory 3-----------Product D
Factory 3-----------Product E
Factory 3-----------Product F
Result:
==============================================
Factory 1-----------Product A,Product B,Product C,Product D
Factory 2-----------Product D,Product E,Product F
Factory 3-----------Product D,Product E,Product F
Help Needed With Displaying Crosstab Data In A Report
Oct 27, 2007Hi all, help with this would be appreciated.
I have a crosstab query which returns values in a range of categories. I have 5 categories. My problem is that sometimes a category may not return a value in 1 or more of the categories. This means that when I am displaying this data in my report I haven't got the data in the correct columns.
In the underlying queries for this I tried using a LEFT JOIN forcing the category, but when I executed the crosstab I got errors in all fields.
Thanks in advance
Ginny
Tables :: How To Get Data From Crosstab When Click On Number
Mar 24, 2014I have a query of people and their position, I did a crosstab query and now I have numerical data. How so I get it to show me the data when I click on the number?
View 1 Replies View RelatedBlank Fields In Crosstab Query Based On Previous Query
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! :)