May 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
I'm trying to construct an SQL crosstab query that will output data like the picture I've attached in the .zip file.
The four variables from the data table would be [Client Accounting].[Marketer] (the left vertical column), [Client Accounting].[Closing Date] (the higher level horizontal column grouped by month), [Client Accounting].[Write Off] and [Client Accounting].[Refund] (the lower level horizontal columns as sums)
The totals column at the bottom and the two vertical columns at the right would be made in the report and wouldn't need to be in the query.
This is what I have so far but I don't know how to add a second TRANSFORM statement to be included and grouped by month!
Code: TRANSFORM Sum([Client Accounting].[Refund]) AS SumOfRefund SELECT [Client Accounting].[Marketer] FROM [Client Accounting] GROUP BY [Client Accounting].[Marketer] PIVOT Format([Closing Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
I am trying to create a crosstab query that counts the number of events that have been logged for each day of the month. The event date is stored in an ODBC compliant database in date/time format. Unfortunately, the crosstab query counts the number of events for each date/time occurrence.
I have tried entering the following expression in the Field: definition with no success (no records are returned when the query finishes executing):
Hi there, I got a little data transformation problem:
I have got a source table:
ID 1 2 3 1 x y z 2 xx yy zz
That I wanna transform to the following format:
ID ID2 Comment 1 1 x 1 2 y 1 3 z 2 1 xx etc...
The problem is, that I need a routine that takes the column names of table 1 and uses them as values in table 2. So basically, every column of table 1 (1,2,3, etc.) should become a new data record in table 2.
I'm trying to import an XML file into my access database using an XSL Transform (I only need certain bits of data), however when I import the tables and field names come through fine now but the only values that come through are the last values listed in the XML file... for instance when i import high there would be 10 high values but only the last one comes through.I would obviously like the "Day" field to be a date/time field in access, and the high and low to be a number field in access... but this is back-burner to solving the real issue listed up top.This is my XSL file:
Code:
<?xml version="1.0" encoding="ISO-8859-1"?> <xsl:stylesheet version="1.0" xmlns:xsl="Transform Link (I don't have 10 posts yet and can't post links)"> <xsl:template match="/"> <Weather> <xsl:for-each select="response/forecast/simpleforecast/forecastdays/forecastday">
And I'd like to convert it to this: Member_IDDiagnosis - 1Diagnosis - 2Diagnosis - 3Diagnosis - 4 10000 HypothermiaFrost Bite 10001 Fatigue DehydrationExhaustion 99999 Exhaustion Hypothermia
The columns don't line up well but I am looking for each diagnosis to move into one of the 4 columns, depending on whether it is the first, second, third or fourth diagnosis associated with the member.
I'm having difficulty getting my import to work... when i call on the original XML file I get too many tables... when i call on the XML file using the transform function of Access with the XSL file it gives me only two tables "body" and "tr". Body contains the value "Weather" and tr contains the value "Day".
Import the day as month/day/year into a field "Day" in access table "WeatherSFCAL"
Import the Fahrenheit temp from the high section into the field "High" in access table "WeatherSFCAL"
Import the Fahrenheit temp from the low section into the field "Low" in access table "WeatherSFCAL"
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 ?
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 ?
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 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 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.
I 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?
I have a small issue where I do have names written in UPPER CASE, I would like to transform them in Capitalized (each word), I understand that I cannot use the same function as in excel (PROPER) but something like StrConv, but as I do have a ‘comma’ in between the names I have something like that
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...
I 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
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;
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],""))
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 string "HICX106-StandardTop-Venderedstest", I only want the first 30 letters or numbers ie "HICX106-StandardTop-Vendereds". Can anyone help, guess this is really easy!!!!!!!!!!
In the subreport, I calculated a difference between budget and actual. I want to present this difference in a way that if it is positive, then just show it the way it is, but if it is a negative number, I want to show it in a (). FOr example, if it is -123, I want to show it as (123).
