How To Transform Data To Crosstab-like Format?
Apr 1, 2008
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
View Replies
ADVERTISEMENT
Mar 24, 2015
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");
View 2 Replies
View Related
Oct 24, 2012
1. how to transform excel data to access
2. how to create run time application : i tried it to make accde but no luck T_T "
View 4 Replies
View Related
Oct 10, 2006
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):
Expr1:Format([OPEN_TIME]),"mm/dd/yyyy")
I am stumped! Any help would be appreciated.
Thanks,
John
View 3 Replies
View Related
Jul 1, 2014
This is my expression to format crosstab query by month
Expr1: Format([Recharge_Date];Month([Recharge_Date]))
but i can't make it with month and year....
View 2 Replies
View Related
Jun 4, 2007
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.
Any ideas how to solve this?
Thanks!
Steve
View 1 Replies
View Related
Dec 10, 2012
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">
[code]...
View 2 Replies
View Related
Dec 22, 2014
I have a table with multiple rows per member that I would like to convert to 1 row per member. I have a table that looks a bit like this:
Member_IDDiagnosis
10000Hypothermia
10000Frost Bite
10001Fatigue
10001Dehydration
10001Exhaustion
99999Exhaustion
99999Hypothermia
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.
View 3 Replies
View Related
Mar 28, 2014
I have a table with acct numbers of various lengths, ie:
246
18
36895
352119
472
I need to run a query so that all of the acct numbers are 8 characters long with zeros as placeholders in front of the actual acct numbers, ie:
00000246
00000018
00036895
00352119
00000472
I tried using the expression NewAcctNo: 00000000+[AcctNo], but that did not fix the problem.
View 2 Replies
View Related
Dec 9, 2012
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"
View 3 Replies
View Related
May 22, 2006
Hi
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
View 2 Replies
View Related
May 22, 2006
Hi
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
View 1 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
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
Oct 27, 2007
Hi 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
View 2 Replies
View Related
Mar 24, 2014
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?
View 1 Replies
View Related
Dec 16, 2013
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
Code:
StrConv(Left(Tbl_Telephony_Temp.Agent, InStr(Tbl_Telephony_Temp.Agent, ",")-1), 3)&", "&StrConv(Mid(Tbl_Telephony_Temp.Agent, InStr(Tbl_Telephony_Temp.Agent, ",") + 1), 3) AS CompleteName
Which as you can see is quite a complex statement, how can I simplify it?
Example of a name: VITOR, FONSECA, preferable output, Vitor, Fonseca
View 14 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
Dec 27, 2014
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
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.
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
Sep 21, 2007
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
Please help!!!
View 2 Replies
View Related
Aug 8, 2005
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!!!!!!!!!!
View 4 Replies
View Related
Jun 28, 2006
hello, Not sure if this is right placeto post.
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).
Can anybody help me with it?
Thank you.
View 2 Replies
View Related
