The Access SQL TRANSFORM statement is a non-standard extension to SQL-92 implemented by Microsoft's JET database engine to create crosstab queries. Crosstab queries calculate a sum, average, count, or some other aggregate on rows, and then group the results in two dimensions: one down the left side (row headings) and the other across the top (column headings). In Excel these are called PivotTables. In SQL Server you're on your own.
Crosstab queries provide a powerful data analysis tool, but can be tricky to use in Access. It's not that they're hard to create. Access provides a wizard for creating new ones, and the syntax is pretty straightforward, if under-documented (that TRANSFORM link above is to the Access 97 documentation). It's using them, like as the basis for a report, that can be tricky.
The problem is that a crosstab query's columns aren't generally known until the query is run. After all, a crosstab query's purpose is to turn row aggregations into columns, and so the number of columns (and their names) will generally vary depending on the data selected. Access reports, on the other hand, really want to know at design time what columns their base queries will return. Without this information, they can't know what data columns are available for layout and presentation.
Luckily, there are lots of ways to get around this in Access. For instance, you can assemble, parse, and distribute the query's results as delimited values, or use a multi-column subreport, or use a parallel non-TRANSFORM GROUP BY query and fill the report's controls programmatically. The folks at Corporate Technologies have prepared a great demonstration of the many ways to do this, so I won't repeat them here. What I am going to do is ramble some about creating a parameterized crosstab query with a fixed set of columns in order to highlight a couple of gotchas.
I prepared a sample database, in case you want to follow along.
Let's start with a single table named mytable having the following columns and rows:
column type key long integer project long integer year integer amount currency key project year amount 1 100 2003 $0.50 2 100 2004 $1.00 3 100 2004 $1.50 4 100 2005 $2.00 5 200 2004 $3.00 6 200 2005 $4.00 7 200 2005 $4.50 8 200 2006 $5.00
Using standard GROUP BY SQL, it's easy to get a total of the amounts by project (see q0a):
SELECT mytable.project, Sum(mytable.amount) AS total FROM mytable GROUP BY mytable.project; project total 100 $5.00 200 $16.50
Or a total of the amounts by year (see q0b):
SELECT mytable.year, Sum(mytable.amount) AS total FROM mytable GROUP BY mytable.year; year total 2003 $0.50 2004 $5.50 2005 $10.50 2006 $5.00
However, to get a total of the amounts by project for each year, we'll need a crosstab query.
To turn the former GROUP BY query into a crosstab, wrap it in a TRANSFORM statement, using the Sum() of mytable.amount for the TRANSFORM and mytable.year as the PIVOT (see q1a):
TRANSFORM Sum(mytable.amount) AS total SELECT mytable.project FROM mytable GROUP BY mytable.project PIVOT mytable.year; project 2003 2004 2005 2006 100 $0.50 $2.50 $2.00 200 $3.00 $8.50 $5.00
Alternatively, we can GROUP BY year and PIVOT on the project (see q1b):
TRANSFORM Sum(mytable.amount) AS total SELECT mytable.year FROM mytable GROUP BY mytable.year PIVOT mytable.project; year 100 200 2003 $0.50 2004 $2.50 $3.00 2005 $2.00 $8.50 2006 $5.00
Now suppose we need to create an Access report based on the first crosstab query above (i.e., total amounts by project for each year), but we want to restrict the data to a user-specified year and the year after. To do this, let's add a WHERE clause that uses a numeric parameter named "foryear" (see q2):
PARAMETERS foryear Short; TRANSFORM Sum(mytable.amount) AS total SELECT mytable.project FROM mytable WHERE mytable.year In ([foryear],[foryear]+1) GROUP BY mytable.project PIVOT mytable.year;
When we run this query, we'll be prompted to enter a value for the "foryear" parameter. If we specify 2004, we get this:
project 2004 2005 100 $2.50 $2.00 200 $3.00 $8.50
Perfect. Unfortunately, we can't base a report on it. Well, we could, but we would have to bind the columns to a particular set of years, or programmatically establish the column bindings at runtime. Not what we want.
Luckily, in situations like this, where the number and/or type of columns desired are known in advance, there's a simple solution. We can coerce the PIVOT data to a set of fixed values using an expression. In other words, instead of relying on the varying values of a column to determine our column headings, we'll calculate fixed column headings from the column data using an expression.
In our query, we know we'll only ever have two columns: one for the specified "foryear" parameter, and one for the next year. So, we can PIVOT on an expression that converts mytable.year values to the appropriate fixed values of "thisyear" or "nextyear" (see q3):
PARAMETERS foryear Short; TRANSFORM Sum(mytable.amount) AS total SELECT mytable.project FROM mytable WHERE mytable.year In ([foryear],[foryear]+1) GROUP BY mytable.project PIVOT IIf(mytable.year=[foryear],"thisyear","nextyear");
Now when we run this query and specify 2004 for the "foryear" parameter, we get this:
project nextyear thisyear 100 $2.00 $2.50 200 $8.50 $3.00
And if we specify 2005 for the "foryear" parameter, we get this:
project nextyear thisyear 100 $2.00 200 $5.00 $8.50
Now we have a crosstab query with fixed columns to which we can bind a report.
I should point out that if you are going to create a report based on this query, you might want to avoid specifying the report's RecordSource at design-time. In other words, create an unbound report, and bind the report to the query at run-time by setting the report's RecordSource property in its Open event (see rep-q3a). If you don't do this you'll be prompted for the "foryear" parameter a million times while designing the report. Alternatively, you can bind the query's parameter to a control on an open form, or wait until you're done designing the unbound report and assign its RecordSource just before you save it (see rep-q3b). Note that you'll have to assign the report a RecordSource at design-time if you intend to use it as a subreport.
So, are we done? Not quite, we've still got a problem. The problem is that our crosstab query has columns consisting of fixed values, but doesn't have a fixed numberof columns. In other words, we know what the possible column values are (and so can bind form/report controls to them), but we don't know until the query is run how many of those columns it will actually have.
To illustrate this problem, run our last query again and specify 2002 for the "foryear" parameter:
project nextyear 100 $0.50
Yikes, we lost a column.
This presents an obvious problem for any forms or reports we've bound to the query. If those forms or reports always assume the existence of both a "thisyear" and "nextyear" column then we'll be seeing a 3070 error: "The Microsoft Jet database engine does not recognize as a valid field name or expression."
The other problem is that we can't use this query as the basis for a subreport. If we do (see rep-q3bsub), we'll get an error 2172: "You can't use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subreport." And, contrary to KB 209218, clearing the child report's LinkChildFields and LinkMasterFields properties will not allow the report to run "showing every row in the subreport." I also noticed that Access swallows this error if the parent report is opened programmatically using DoCmd.OpenReport, even though the report still doesn't work (see frm-rep-q3bsub).
The solution is simple: explicitly declare the columns our crosstab query will always have. We do this by adding an IN clause to our PIVOT (see q4):
PARAMETERS foryear Short; TRANSFORM Sum(mytable.amount) AS total SELECT mytable.project FROM mytable WHERE mytable.year In ([foryear],[foryear]+1) GROUP BY mytable.project PIVOT IIf(mytable.year=[foryear],"thisyear","nextyear") IN ("thisyear", "nextyear");
Now if we specify 2002 for the "foryear" parameter we get:
project thisyear nextyear 100 $0.50
Great. We've now got a fixed column crosstab query that we can bind to the RecordSource of a report at design-time without causing endless parameter value prompts (see rep-q4), and that we can successfully use as the RecordSource of a linked subreport (see rep-q4sub).
By the way, if you're wondering why earlier we didn't do something like this:
PARAMETERS foryear Short; TRANSFORM Sum(mytable.amount) AS total SELECT mytable.project FROM mytable WHERE mytable.year In ([foryear],[foryear]+1) GROUP BY mytable.project PIVOT mytable.year IN ([foryear], [foryear]+1);
It's because Access doesn't support the parameterization of IN clauses in a TRANSFORM statement's PIVOT clause. This will get you an error 3071: "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
Now, let's talk about another TRANSFORM gotcha. It seems that Access can't see through TRANSFORM SQL the same way it can see through all other SQL statements. Specifically, Access can't resolve implicit query parameters through a chain of separate nested queries that contain, somewhere in the chain, a TRANSFORM query, unless the TRANSFORM query also uses or declares the same downstream parameters. More simply, TRANSFORM queries terminate implicit parameter bubbling in Access.
An example will probably explain this better. If we have a query named q5 like this:
SELECT mytable.project, mytable.year, mytable.amount FROM mytable WHERE mytable.year In ([foryear],[foryear]+1);
And another query named q6 that relies on q5 like this:
SELECT mytable.project, Sum(mytable.amount) FROM q5 GROUP BY mytable.project;
Running query q6 will cause Access to prompt us for a value for the "foryear" parameter, which is used, but not explicitly declared, in q5 . Likewise, if we programmatically evaluate the Parameters collection of q6's QueryDef object, we'll find that it contains a single "foryear" Parameter:
?CurrentDb.QueryDefs("q6").Parameters(0).Name
foryear
However, if we add another query named q7, which also relies on query q5, but uses a TRANSFORM statement, like this:
TRANSFORM Sum(mytable.amount) AS total SELECT mytable.project FROM q5 GROUP BY mytable.project PIVOT mytable.year;
Running the query will not get us a prompt for the "fordate" parameter. Instead, it will will get us an error 3070: "The Microsoft Jet database engine does not recognize 'foryear' as a valid field name or expression." We'll also see this error if we try to programmatically evaluate the Parameters collection of q7's QueryDef object.
The solution is simple: explicitly declare query parameters, either in the queries that use them, or in the TRANSFORM queries that use the queries that use them.
Finally, just to be complete, I should point out that there's a way to get the same crosstab-style fixed column results illustrated above without using a TRANSFORM statement (see q8):
PARAMETERS foryear Short; SELECT myvalues.project, Sum(myvalues.thisyear) AS thisyear, Sum(myvalues.nextyear) AS nextyear FROM (SELECT mytable.project, IIf(mytable.year=foryear, mytable.amount, Null) as thisyear, IIf(mytable.year=foryear + 1, mytable.amount, Null) as nextyear FROM mytable WHERE mytable.year In (foryear,foryear+1)) AS myvalues GROUP BY myvalues.project;
Of course, this type of sub-SELECT SQL is always an option when you're dealing with fixed column presentations. However, I find the TRANSFORM-based approach cleaner and easier to read.
COMMENTS
Good article!
When I saw this, I was thinking - "this could probably also be done using XSLT" (that is, if the original data was in XML rather than Access).
So, how would this be done? I've heard that XSLT 2.0 has a "group-by" function, but I'm not sure how you would do a "pivot" transformation as described in this article.
- Andy
When I saw this, I was thinking - "this could probably also be done using XSLT" (that is, if the original data was in XML rather than Access).
So, how would this be done? I've heard that XSLT 2.0 has a "group-by" function, but I'm not sure how you would do a "pivot" transformation as described in this article.
- Andy
Andy Elvey | 2005.01.28 10:59 PM
I am glad I found this. BTW, could you add a TOTAL line off all Projects for thisyear and nextyear?
Doran George | 2005.02.08 11:51 AM
Hi Andy,
Thanks for the comment and question. Not enough room to answer here, so I wrote a new post:
Let me know if you have any more questions.
ewbi.develops | 2005.02.08 05:47 PM
Hi Doran,
Yes, but we'll have to do a couple of things.
First, we'll have to pick an arbitrary value to represent the total line. Preferably, the value should sort to the bottom of all the values and should be of the same data type. Given the sample data above, let's pick 9999.
Second, we'll have to replace the SELECT given to the TRANSFORM so that it includes all of the previously selected rows *plus* one row for each column, thisyear and nextyear, containing as the amounts in those rows the respective amount sums of the matching rows.
This can be more easily done using a couple of saved queries, but I'll combine it all here using sub-SELECTs to save room:
PARAMETERS foryear Short;
TRANSFORM Sum(mytablewithtotal.amount) AS total
SELECT mytablewithtotal.project
FROM (
SELECT mytable.project, mytable.year, mytable.amount
FROM mytable
WHERE mytable.year In ([foryear],[foryear]+1)
UNION
SELECT 9999 as project, foryear as year, Sum(mytable.amount)
FROM mytable
WHERE mytable.year = [foryear]
GROUP BY mytable.project, mytable.year
UNION
SELECT 9999 as project, (foryear + 1) as year, Sum(mytable.amount)
FROM mytable
WHERE mytable.year = [foryear] + 1
GROUP BY mytable.project, mytable.year
) mytablewithtotal
GROUP BY mytablewithtotal.project
PIVOT IIf(mytablewithtotal.year=[foryear],"thisyear","nextyear") In ("thisyear","nextyear");
TRANSFORM Sum(mytablewithtotal.amount) AS total
SELECT mytablewithtotal.project
FROM (
SELECT mytable.project, mytable.year, mytable.amount
FROM mytable
WHERE mytable.year In ([foryear],[foryear]+1)
UNION
SELECT 9999 as project, foryear as year, Sum(mytable.amount)
FROM mytable
WHERE mytable.year = [foryear]
GROUP BY mytable.project, mytable.year
UNION
SELECT 9999 as project, (foryear + 1) as year, Sum(mytable.amount)
FROM mytable
WHERE mytable.year = [foryear] + 1
GROUP BY mytable.project, mytable.year
) mytablewithtotal
GROUP BY mytablewithtotal.project
PIVOT IIf(mytablewithtotal.year=[foryear],"thisyear","nextyear") In ("thisyear","nextyear");
Run this query in the sample database I provided and you'll get a resultset having the same rows as before, with a new row at the bottom for project 9999 showing the total of each column.
Note that this can also be done with a UNION of the original TRANSFORM with a summing SELECT of the same TRANSFORM, but the TRANSFORM must first be saved in a separate query (no sub-SELECTing TRANSFORM queries in-line), and if there are any blank values in the result of the main TRANSFORM, Access may freak out. I need to write another post describing this issue, but in short when combining queries based on calculated values where the first query may contain null values in the first row Access will often display gibberish in some of the columns for some of the rows because it cannot discern the data type.
Hope this answers your question. Let me know if you'd like any additional info.
ewbi.develops | 2005.02.08 06:23 PM
I've got a Transform query which works on a workstation with the full Access 97 installed. However, on workstations where only Acess 97 run-time has been installed, the query isn't working and my form just shows #Name? instead of displaying data. All other queries, forms, reports work... but none of them use TRANSFORM. Please help.
TRANSFORM Count(Youth.ID) AS [The Value]
SELECT Youths.Location AS Branch, Count(Youths.ID) AS [Total Of ID]
FROM Youths
WHERE (((Format[IssueDate],"yy"))=Format(Now(),"yy")))
GROUP BY Youths.Location
PIVOT "M " & Format([IssueDate], "mm") In ("M 01", "M 02", "M 03", "M 04", "M 05", "M 06", "M 07", "M 08", "M 09", "M 10", "M 11", "M 12");
SELECT Youths.Location AS Branch, Count(Youths.ID) AS [Total Of ID]
FROM Youths
WHERE (((Format[IssueDate],"yy"))=Format(Now(),"yy")))
GROUP BY Youths.Location
PIVOT "M " & Format([IssueDate], "mm") In ("M 01", "M 02", "M 03", "M 04", "M 05", "M 06", "M 07", "M 08", "M 09", "M 10", "M 11", "M 12");
Cat | 2005.02.13 10:30 PM
Hi Cat,
I'm afraid I haven't got much time to look into this right now (maybe not even this week), but I wanted to point out that if you haven't already you may want to try a test where you run a full version of Access 97 in runtime mode using the /runtime command-line parameter to see if you can reproduce the behavior. (Note that to do this, the machine with the full version of Access 97 on it must also have a registered copy of mso97rt.dll on it.)
Is the trouble form bound to this query as its record source, or are you programmatically binding the form and/or running the query?
What's the control source property set to (at design time or programmatically at runtime) for the control reporting "#name"?
Are there multiple controls reporting "#name"? Are there any that aren't?
ewbi.develops | 2005.02.14 03:33 AM
Thanks. I'll try testing using the /runtime command-line parameter first.
I'm programmatically binding the RecordSource property, so the form can display a different set of data depending on what the user has selected.
If strLoc = "00" Then
Me.RecordSource = "qryStuSales_LocXweekday"
Else
Me.RecordSource = "qryStuSales_LocXweekdayBranchUse"
End If
If strLoc = "00" Then
Me.RecordSource = "qryStuSales_LocXweekday"
Else
Me.RecordSource = "qryStuSales_LocXweekdayBranchUse"
End If
In actual fact, ALL the controls on the form are reporting the #Name? error on the runtime machine. At first I thought I'd made a mistake and didn't match the field names correctly. But running the same .mde file on a machine with full Access 97 displayed the data just fine.
Cat | 2005.02.14 01:57 PM
Hi Cat,
I think the problem may be due to a failed VBA project reference:
Or perhaps a mismatched service release between the machine used to compile the MDE and the machines trying to use it:
Give these a look and let me know what you find.
ewbi.develops | 2005.02.14 03:18 PM
Hi again -
This time, I help *you* out! ... :-))
I found a great little website which shows that it is possible to create crosstabs using plain old "vanilla" SQL - no non-standard pivot statements or anything.
Here's the URL - (about 3/4 of the way down the page, you'll see an example of this ) -
http://www.advogato.org/person/peat/diary.html?start=15
This time, I help *you* out! ... :-))
I found a great little website which shows that it is possible to create crosstabs using plain old "vanilla" SQL - no non-standard pivot statements or anything.
Here's the URL - (about 3/4 of the way down the page, you'll see an example of this ) -
http://www.advogato.org/person/peat/diary.html?start=15
Not bad! ....... ;-)
- Andy
Andy Elvey | 2005.02.25 10:50 PM
Hi Andy,
Good to hear from you again. Unfortunately, Access/JET doesn't support the SQL keyword CASE, so one is left using sub-selects for the columns, like this:
SELECT
project,
(select SUM(amount) from mytable A where A.project = Z.project and A.year = 2003) as 2003,
(select SUM(amount) from mytable A where A.project = Z.project and A.year = 2004) as 2004
FROM mytable Z
WHERE Z.year IN (2003, 2004)
GROUP BY Z.project;
project,
(select SUM(amount) from mytable A where A.project = Z.project and A.year = 2003) as 2003,
(select SUM(amount) from mytable A where A.project = Z.project and A.year = 2004) as 2004
FROM mytable Z
WHERE Z.year IN (2003, 2004)
GROUP BY Z.project;
Which isn't bad in some cases. However, the drag is that these approaches don't produce dynamic crosstabs. It's necessary to know and explicitly define the columns at design time.
The SQL Server link I provide in the first paragraph describes a stored procedure that will build and execute a dynamic crosstab without having to first identify the possible columns.
Thanks for taking the time to write!
ewbi.develops | 2005.02.26 06:41 AM
Hi!
Arrrgh - amazing to hear that MS Access / Jet doesn't support "case"!
Just amazing (shakes head in wonderment ..... ;-)) ) I guess I shouldn't really be amazed, given that it's an MS product ... ;-P
( Gee, their SpreadsheetML is **ugly!** )
Anyway - at least the example on that page may come in handy for anyone using a less-proprietary SQL package (maybe SQLite, MySQL).
I haven't actually tried it myself yet - must do so. At my work, SAS has a "Proc SQL" which includes "case", so I'm sure that it'll work there!
- Andy
Just amazing (shakes head in wonderment ..... ;-)) ) I guess I shouldn't really be amazed, given that it's an MS product ... ;-P
( Gee, their SpreadsheetML is **ugly!** )
Anyway - at least the example on that page may come in handy for anyone using a less-proprietary SQL package (maybe SQLite, MySQL).
I haven't actually tried it myself yet - must do so. At my work, SAS has a "Proc SQL" which includes "case", so I'm sure that it'll work there!
- Andy
Andy Elvey | 2005.02.26 01:13 PM
Awesome article!
Scott B | 2005.03.16 09:13 AM
How do we use the transform statement if I want to pivot say for this condition:
1. Count8
2.Count between 8 and 25
3. Count25
1. Count8
2.Count between 8 and 25
3. Count25
The output I am looking for is like a value which is pivotted on all values from 1 to 7 and a pivot for a condition between 8 and 25 AND 1 FOR 25.
Rishi | 2005.05.12 12:36 PM
Hi Rishi,
The organization of TRANSFORM results into columns is controlled using the PIVOT clause. And, as described above, the PIVOT clause can be calculated. So, for instance, the following SQL can be used to transform the sample table above into rows of years with counts of rows having amounts in the categories of "$1", "$1-3", and "$3" organized into distinct columns:
TRANSFORM count(*) AS total
SELECT mytable.year
FROM mytable
GROUP BY mytable.year
PIVOT IIF(mytable.amount = 1, "$1", IIF(mytable.amount = 3, "$1-3", "$3")) IN ("$1", "$1-3", "$3");
SELECT mytable.year
FROM mytable
GROUP BY mytable.year
PIVOT IIF(mytable.amount = 1, "$1", IIF(mytable.amount = 3, "$1-3", "$3")) IN ("$1", "$1-3", "$3");
Note the calculated PIVOT clause and the use of the optional IN modifier to force the order of the columns.
It's a little hard to represent the results here, but they would look something like this:
year<$1$1-3>$3
20031
200412
20051 2
20061
20031
200412
20051 2
20061
Hope this helps.
ewbi.develops | 2005.05.12 01:37 PM
Thanks,
A lifesaver.
A lifesaver.
| 2005.06.09 04:36 PM
Be glorified your name for eternal times!
You've saved my life
You've saved my life
chorche | 2005.06.22 03:20 AM
Yea, verily! I'm so glad I could help.
ewbi.develops | 2005.06.22 06:26 AM
Can you put a function inside the IN part of the SQL statement? eg
In (returnInclause())
Doesn't seem to work for me unless I update the SQL statement programmatically
In (returnInclause())
Doesn't seem to work for me unless I update the SQL statement programmatically
Elaine | 2005.08.31 01:38 AM
Hi Elaine,
You are correct - no functions or parameters in the IN clause. However, there are often other ways to accomplish things thought to be the responsibility of the IN clause. In actuality, the IN clause only provides two services:
1) Restricting/filtering PIVOT'd column values
2) Ordering PIVOT'd column values
The first can (and should, for efficiency) be done using WHERE and HAVING criteria that reduces the selected rows to those having the desired PIVOT'd column values. For example, if the columns resulting from the PIVOT'ing of a Year column are supposed to be 2002, 2003, and 2004, then there's no point including rows having a Year 2004.
The second can often be done by using a function in the PIVOT clause to give each selected row's dynamic column value a fixed value corresponding with a static (ordered) value in the IN clause. That's what the sample above does. It uses a PIVOT function to convert dynamic year values (2003/2004, 2004/2005, etc.) into fixed values ("thisyear"/"nextyear") so they can be ordered using a static IN clause.
Hope that helps. Good luck!
ewbi.develops | 2005.08.31 08:19 AM
Thanks for a well explained article. Sorted out my 'problem' of getting overall totals on a 'GROUP BY'
statement (used UNION)
statement (used UNION)
John H | 2005.08.31 11:37 PM
Hello.
I loved the article, it was very comprehensive. I couldn't open the sample database, MS Access didn't recorgnize it (running 97), but I still found the examples handy. It helped out, thanks!
I have an automation question which could be discussed in another thread. The problem is opening a MDW protected MS Access db and returning a specific form or report's recordsource. I tried using automation but could not getting it to work. If you are intrested in experimenting with Access security and automation please drop me a line and I will send you my code. (I had success retrieving DAO objects.)
thx
Kris
Kris
Kris | 2005.09.30 12:50 PM
Hi Kris,
Sorry for the delay - I'm up to my ears moving.
When you refer to trying to get a form or report's recordsource, do you mean automation of Access itself, or automation of DAO and/or ADO to open an MDB to get these values?
ewbi.develops | 2005.10.04 07:51 PM
Simply perfect ;-)
Thanxs for help, from Africa, Ivory Cost
Have a nice day!
Thanxs for help, from Africa, Ivory Cost
Have a nice day!
Olivier | 2006.01.05 03:31 AM
Olivier,
Glad it helped. Thanks for the feedback!
ewbi.develops | 2006.01.05 06:54 AM
Thanks a lot for this great tip! I was struggling with this problem, and now I have the solution. Great resource!!!
Singh | 2006.02.11 03:09 PM
Great Post! Just found it. Is there a way to specify the sort of your series? I have an Access chart report and its record source is the following:
TRANSFORM Avg(qryCWT_Last_3_Months.doc_pcnt) AS AvgOfdoc_pcnt
SELECT (Format([d_iss_month],"mmm"" '""yy")) AS Expr1
FROM qryCWT_Last_3_Months
GROUP BY (Year([d_iss_month])*12+Month([d_iss_month])-1), (Format([d_iss_month],"mmm"" '""yy"))
PIVOT qryCWT_Last_3_Months.sof;
SELECT (Format([d_iss_month],"mmm"" '""yy")) AS Expr1
FROM qryCWT_Last_3_Months
GROUP BY (Year([d_iss_month])*12+Month([d_iss_month])-1), (Format([d_iss_month],"mmm"" '""yy"))
PIVOT qryCWT_Last_3_Months.sof;
The SOF is my horizontal series. The table has a Primary Key named SOF_ID. I don’t need or want the PK to show up on the chart but I would like the series to sort by PK rather than alphabetically. Any ideas?
Thanks,
Patrick
Patrick
Patrick | 2006.02.22 12:50 PM
Hi Patrick,
I didn't mention in the original post that if you use an unconstrained PIVOT (i.e., one without an IN clause) you can control the order of the columns with an ORDER BY on the PIVOTed column (just before the PIVOT clause). However, you can't ORDER BY on a column that doesn't appear in the query in an aggregate function or grouping (the GROUP BY or an unconstrained PIVOT). So, in your case, this means it is impossible to specify an ORDER BY on the SOF_ID while PIVOTing on SOF.
However, there is a way to get around this if you're willing to do a little coding. Unfortunately, there's not enough room here to write it up, so I'll try to get another post together later today describing the workaround.
ewbi.develops | 2006.02.22 04:34 PM
Patrick, I've written a post describing one way you can accomplish what you want:
Let me know if it works out for you, and/or if you come up with another solution. Good luck!
ewbi.develops | 2006.02.22 07:05 PM
I stumbled on this link looking for information about using CASE in a SQL construct in Access. While you provided the information about Access not supporting it, you didn't provide a simple work-around.
Declare a function xlate which does the substitution for you and then use it in your select. It works exactly the same as a CASE construct plus it keeps all the code in a nice, easy to maintain module. See the sample below:
function xlateAge(i)
select case i
case 0 to 17
xlateAge = "= 66
xlateAge = ">65"
end Select
end Function
select case i
case 0 to 17
xlateAge = "= 66
xlateAge = ">65"
end Select
end Function
select xlate(AgeInYears) as Age from Patient;
Hope it helps someone.
Marshall | 2006.02.24 02:49 PM
Ok, not sure why it ate my select statement, but the syntax of that is not correct. For those needing help with it, just reference the Access help file. Essentially, it works a good bit like the C select.
m
Marshall | 2006.02.24 02:51 PM
Hi Marshall,
Thanks for the feedback. Sorry about TypePad eating your comment, though. Your suggestion got me thinking about a general purpose routine for doing this in Access that I wrote a while back, so I wrote it up in a separate post:
Thanks again!
ewbi.develops | 2006.02.24 06:27 PM
Hi, This has been useful. I'm a relative beginner and have run into a stumbling block. I have a table with financial information. The years are records, the information is fields (cash, inventories, ppe, etc.)
I would like to create a query that looks something like this:
2001 2002 2003 2004
Cash
Inventories
PPE
I would like to create a query that looks something like this:
2001 2002 2003 2004
Cash
Inventories
PPE
I can't seem to create a union query with two crosstab SQL statements. Can you help... (I don't want to clutter the database with 100 crosstab queries, and one humongous union query).
Thanks,
Steve
Thanks,
Steve
Steve | 2006.02.26 12:25 PM
Hi Steve,
I'm not sure you'll need 100 crosstab queries, or even need to UNION any crosstab queries (which is possible with constrained PIVOTs or advance knowledge of the resulting columns), but you will need a UNION query in order to turn all of your columnar data into distinct rows before using this data in a crosstab.
The idea is to separately query each of the columns you want (e.g., Cash, Inventories, PPE, etc.), giving each resulting row a calculated column having the source field's name and separately the column's value using a single column name, and then UNION these values together. Something like this:
select "Cash" as InfoType, Year, Cash as Amount from MyTable where Cash is not null
UNION
select "Inventories" as InfoType, Year, Inventories as Amount from MyTable where Inventories is not null
UNION
select "PPE" as InfoType, Year, PPE as Amount from MyTable where PPE is not null
UNION
etc.
UNION
select "Inventories" as InfoType, Year, Inventories as Amount from MyTable where Inventories is not null
UNION
select "PPE" as InfoType, Year, PPE as Amount from MyTable where PPE is not null
UNION
etc.
You can then TRANSFORM this three column UNION query to get your crosstab:
TRANSFORM Sum(Amount) AS total
SELECT InfoType
FROM MyUnionQuery
GROUP BY InfoType
PIVOT Year
SELECT InfoType
FROM MyUnionQuery
GROUP BY InfoType
PIVOT Year
Let me know if that doesn't make sense or you need anything else. Good luck!
ewbi.develops | 2006.02.27 06:57 AM
I get extracts/reports from a corporate Sybase database. I have an all-inclusive query run, dropped into a folder each night, and then do my data
analysis/mining via Access.
analysis/mining via Access.
Now here's my problem
The DATA (stored as a Trait) is not stored in separate fields for each
trait .. here's an example
trait .. here's an example
EUID Trait_Name Trait_Value
12345 SeedNumber 147
12345 HarvestDate 4/15/2005
12345 Status Harvested
12345 SeedNumber 147
12345 HarvestDate 4/15/2005
12345 Status Harvested
etc
There may be many rows for each identifier... as many rows as there are unique trait and trait values assocaited with each identifier. Of course the Trait_Value field is character.
Now what I want to do is to build a pivot so that the data looks like this..each identifier a row, each Trait_Name gets a field name and the trait_values are converted to numeric, date or character to
conform to the type of data they are. I will have a separate field for each Trait I want to use.
conform to the type of data they are. I will have a separate field for each Trait I want to use.
Identifier SeedNumber HarvestDate Status
12345 147 4/15/2005 Harvested
12346
12347 23 5/20/2001 Harvested
12348 Killed
This works…
TRANSFORM First(tblGT.texttraitvalue) AS FirstofTextTraitValue
SELECT tblGT.EUID
FROM tblGT
GROUP BY tblGT.EUID
PIVOT tblGT.TraitName In ("Seednum_1","HarvDate_1","STATUSCALL_1","SendGHDate_1");
SELECT tblGT.EUID
FROM tblGT
GROUP BY tblGT.EUID
PIVOT tblGT.TraitName In ("Seednum_1","HarvDate_1","STATUSCALL_1","SendGHDate_1");
But the data remains formatted as text. So how might I get that text formatted to date, or numeric.
It would be preferred to do this in one statement, but if I could make a table from this TRANSFORM/PIVOT, and the use CInt() and CDate() to convert the strings to the proper format, I suppose that woudl work as well.
Jerry
Jerry Ranch | 2006.02.27 09:28 AM
Hi Jerry,
You could use your text-producing TRANSFORM query as the basis for another query that would repeat each of the explicitly PIVOTed crosstab columns with data type conversion expressions (CInt, CDate, etc.), but you would have to save the TRANSFORM query first, as they can't be used in-line as sub-SELECTs.
There's an alternative approach, though, that could get you the same thing in a single query. This approach doesn't use a TRANSFORM. Instead, it relies on the fact that, regardless of the approach you choose, you'll need to know in advance what traits you're going to receive and what data type their values are supposed to be. Knowing this, it uses a sub-SELECT with explicit column-assigning, data-type-coercing expressions, and then groups these to get your final results (using MAX to find the first non-null value):
SELECT
EUID,
Max(AllTraits.SeedNumber) as SeedNumber,
Max(AllTraits.HarvestDate) as HarvestDate,
Max(AllTraits.Status) as Status
FROM (
SELECT
EUID,
IIF(TraitName = "Seednumber", CInt(Nz(texttraitvalue, 0)), Null) as SeedNumber,
IIF(TraitName = "HarvestDate", IIF(IsNull(texttraitvalue), Null, CDate(texttraitvalue)), Null) as HarvestDate,
IIF(TraitName = "Status", texttraitvalue, Null) as Status
FROM tblGT
) as AllTraits
GROUP BY EUID
EUID,
Max(AllTraits.SeedNumber) as SeedNumber,
Max(AllTraits.HarvestDate) as HarvestDate,
Max(AllTraits.Status) as Status
FROM (
SELECT
EUID,
IIF(TraitName = "Seednumber", CInt(Nz(texttraitvalue, 0)), Null) as SeedNumber,
IIF(TraitName = "HarvestDate", IIF(IsNull(texttraitvalue), Null, CDate(texttraitvalue)), Null) as HarvestDate,
IIF(TraitName = "Status", texttraitvalue, Null) as Status
FROM tblGT
) as AllTraits
GROUP BY EUID
This same technique can be problematic if you are going to SUM or AVG any of the sub-SELECTed columns, as Access can get confused about the data type of the derived column when faced with nulls. But in this case, you're just trying to retrieve the first non-null value using MAX, so it shouldn't be an issue.
Hope this helps some. Good luck!
ewbi.develops | 2006.02.27 12:14 PM
Thanks a lot... I knew it would be simpler than I thought.
Steve | 2006.02.27 06:15 PM
Well you are correct ... I didn't need the TRANSFORM/PIVOT for my dataset after all.
I thank you for your outstanding advice..it worked. I've been working on this for a month now and just getting more and more frustrated.
I made a modification to suit my needs.
Instead of coding it in a sub, in the QBE of a query that hits the table with the data, I built a new field for each TraitName that I wanted data back for ..... like this (taking your example) using a
GROUP BY EUID
Example with a date field
HarDate: Max(IIf([traitName]="HarvDate_1",IIf(IsNull([texttraitvalue]),Null,CDate([texttraitvalue])),Null))
HarDate: Max(IIf([traitName]="HarvDate_1",IIf(IsNull([texttraitvalue]),Null,CDate([texttraitvalue])),Null))
total:expression
Example with a numeric field
SeedNumb: Max(IIf([traitName]="seednum_1",CDbl(NZ([texttraitvalue],0)),Null))
SeedNumb: Max(IIf([traitName]="seednum_1",CDbl(NZ([texttraitvalue],0)),Null))
total:expression
I had to use CDbl here..when I used CInt() I get a "function CInt() not defined" error.
The syntax you suggetsed was the key..I had tried this approach previously and it did not work as the syntax was improper.
I thank you on bended knee.
My day is complete..I think I'll go home..or play the lottery.
Jerry
Jerry Ranch | 2006.03.01 07:31 AM
Glad to hear it, Jerry.
Good point about the sub-SELECT being unnecessary. I chose that approach so that I could test it independent of the GROUP BY (also, if you can save the sub-SELECT in its own query, you can easily re-use it as the basis for other queries).
I'm surprised you're getting dinged by the CInt() function, though. It belongs to the same set of VBA conversion routines as CDate(), CDbl(), etc., and it worked in my query. Hmm. We'll just chalk that up to Access weirdness.
Anyhow, feel free to take the day off. Tell 'em I said it was okay! ;)
ewbi.develops | 2006.03.01 10:16 AM
hi
very useful this articles.
very useful this articles.
arun | 2006.04.15 05:37 AM
Hi all,
its vary nice and useful article ....i have one query:
How can we user transform & Pivot in Oracle database..if anybody done..pls explain...
Regards,
DRA..
DRA..
| 2006.08.14 06:26 AM
DRA,
Static pivots can be coded in Oracle using just SQL, as pointed out in the last sample in the original post above. However, generation/execution of dynamic crosstab SQL queries is tougher in Oracle than in Access and SQL Server. I'd say have a look at the analytic functions available in Oracle since at least v8.1.6:
Lots of good stuff there. Perhaps something in there can help. Good luck.
ewbi.develops | 2006.08.14 10:30 AM
Hi, can you help me? I am new to SQL. The thing is I have two tables, one with the deescrpition of the invoice, having three fields that hold the value of three items plus the invoice number(cost_1, cost_2, cost_3, inv_num), and the other with the payments made to the invoice (fields: inv_num, date_pay, amount_pay), I would like to have a query that can give me the total per invoice and the amount due for the invoices which aren completely paid.
I really appreciate your help.
I really appreciate your help.
Ezequiel | 2006.09.17 12:45 PM
Thanks for this post, saved us a few hours.
Chase | 2006.09.25 12:11 PM
Thanx for the coverage.
But is it possible to pass a corelated subquery in the TRANSFORM clause?
It helps.
Hamid | 2006.10.06 03:54 PM
Hamid,
Good question. Yes and no. In some cases it is possible to use a correlated subquery in the source query for a TRANSFORM. However, Access is a little touchy about the use of column qualifiers. Sometimes it complains about qualifiers in the subquery, requiring that they be left unqualified (this is true whether the source query is saved and named before being used in a separate TRANSFORM query, or is used directly). Also, I've got a couple of examples of Access getting the results (subtly) wrong when using a correlated subquery in a TRANSFORM, even though it gets the source query itself right. If I get time I'll try to write a separate post on this.
In short, you can, but be very careful.
ewbi.develops | 2006.10.11 01:56 PM
Hi,
I've been poking around your site and find it quite informative. Thanks.
I've got a problem which I haven't been able to solve using TRANSFORM or anything else in Access. I'm basically trying to pivot several fields into a single field. This can be done easily in excel and programatically in Access but I'd like to be able to do it in a query.
I'm basically trying to take a table with the fields:
Item
Cost_HAW
Retail_HAW
Cost_NAM
Retail_NAM
Item
Cost_HAW
Retail_HAW
Cost_NAM
Retail_NAM
and create a query which produces:
Item
Div
Cost
Retail
Item
Div
Cost
Retail
where Div is the last 3 characters in the original Cost and Retail fields.
I know I can do this programatically in Access, I can even do it in a round about way by creating separate Insert Queries for HAW and NAM and then Inserting the values of these two Queries into a new table.
I'm trying to stay away from using Access code as I'm ultimately using the data in Excel. I can query an Access query in excel and the data will refesh but it won't when I query a table.
I'm currently using a query in Excel to retrieve the data and then pivoting the data in Excel. This works great of course until I try to refresh the pivot table using code in Excel.........
If there's a way to do this or a better approach to sharing data between Access and Excel dynamically, I'm all ears.
Thanks.
jlowe | 2006.11.28 04:41 PM
Hi jlowe,
I'm not sure there's any good way to use a crosstab query to get what you want, at least not without code. The easiest way I can see to normalize the item table you described having divisional _NAM, _HAW, etc. cost/retail columns into one having a separate division column and simple cost/retail columns is to do the following:
- Create a table to contain the unique divisions. I assume the divisions are well known and finite, as they appear in the column headings. Let's call the table Divisions and give it a single string column named Div. You can make this column the key to prevent duplicates.
- Populate the Divisions table, which in this case means adding two rows: "HAW" and "NAM".
- Cruise over to the following post and get the code to add SQL Server-like CASE functionality to your Access system:
- Finally, use this query to normalize the original table, which is assumed to be named Items (please excuse the indentation - the commenting system here sucks):
select
Item,
Div,
CaseSimple(Div, "HAW", Cost_HAW, "NAM", Cost_NAM) as Cost,
CaseSimple(Div, "HAW", Retail_HAW, "NAM", Retail_NAM) as Retail
from (
select
Items.*,
Divisions.*
from
Items, Divisions
)
Item,
Div,
CaseSimple(Div, "HAW", Cost_HAW, "NAM", Cost_NAM) as Cost,
CaseSimple(Div, "HAW", Retail_HAW, "NAM", Retail_NAM) as Retail
from (
select
Items.*,
Divisions.*
from
Items, Divisions
)
This query will turn an Items table that looks like this:
Item,Cost_HAW,Retail_HAW,Cost_NAM,Retail_NAM
A,$1.00,$2.00,$3.00,$4.00
B,$10.00,$20.00,$30.00,$40.00
C,$100.00,$200.00,$300.00,$400.00
A,$1.00,$2.00,$3.00,$4.00
B,$10.00,$20.00,$30.00,$40.00
C,$100.00,$200.00,$300.00,$400.00
Into one that looks like this:
Item,Div,Cost,Retail
A,HAW,1,2
A,NAM,3,4
B,HAW,10,20
B,NAM,30,40
C,HAW,100,200
C,NAM,300,400
A,HAW,1,2
A,NAM,3,4
B,HAW,10,20
B,NAM,30,40
C,HAW,100,200
C,NAM,300,400
I just realized I forgot to add currency formatting to the calculated columns. Oh well. Hopefully you get the point.
Btw, you can use nested IIF()s instead of the CaseSimple() function, but I find the CASE syntax easier to grok.
Hope this makes sense. Let me know if it doesn't, or if I've missed the point entirely. Good luck.
ewbi.develops | 2006.11.29 12:38 AM
Hi,
Thanks for the quick reply. I do have a division table so I'm all set there. I'd looked at the case documentation earlier but wasn't sure I'd be able to get it to work. I'll give it a try with the code you suggest and let you know how it goes.
Thanks again.
jlowe | 2006.11.29 11:02 AM
Very good. Exactly what I needed. There is one extra thing though. Here is my query.
TRANSFORM Count([Customer].C_ID) AS CountOfC_ID
SELECT Format([C_Date],"mm") AS Expr1
FROM [Customer]
GROUP BY Format([C_Date],"mm")
PIVOT Format([C_Date],"yyyy");
SELECT Format([C_Date],"mm") AS Expr1
FROM [Customer]
GROUP BY Format([C_Date],"mm")
PIVOT Format([C_Date],"yyyy");
What I'm doing is count the number of customer per month, and compare different years. My only problem is if there is no customer for one month, it will not show this month in the query. So i will only have 11 months instead of 12. I'm using this table for a graph with the X-axis as months and years for the series. Is there a work around for this?
Thanks
jf0027 | 2006.12.07 07:02 AM
jf0027, sorry for the delay, I'm swamped. I'll try to take a closer look at your query and question tonight or tomorrow.
ewbi.develops | 2006.12.08 05:04 AM
Been swamped but will let you know how things go.....I've actually found an additional need for this so we'll see.
jlowe | 2006.12.15 02:43 PM
Great article, but I'm just not clear on how to do a simple conversion.
How would I convert the following into a SQL statement that access can understand?
TRANSFORM First(tblUNA_YTD.UNA_DAYS) AS FirstOfUNA_DAYS
SELECT tblUNA_YTD.Ledger
FROM tblUNA_YTD
GROUP BY tblUNA_YTD.Ledger
PIVOT tblUNA_YTD.Month;
SELECT tblUNA_YTD.Ledger
FROM tblUNA_YTD
GROUP BY tblUNA_YTD.Ledger
PIVOT tblUNA_YTD.Month;
Thanks.
Joseph | 2007.04.10 10:04 AM
Joseph, what part of that query is Access not understanding? Is it reporting an error?
ewbi.develops | 2007.04.13 04:38 PM
jf0027,
I guess it took more than a couple of days. Sorry about that. I lost track of your request until just recently. Then I discovered that it deserved more attention than I could give it in a comment, so I created a new post:
I assume you've already figured this out, but wanted to share it with you anyway and say thanks for the comment.
ewbi.develops | 2007.04.13 11:34 PM
perfect
thanks for the help!!!
smita | 2007.07.12 10:56 AM
Hi, I have a same problem as described .
But I use a criteria for my years and I take the 4 years next to it .
ME.TXTSubmitYear , ME.TXTSumityear +1 , ME.TXTSumityear +2 , ME.TXTSumityear +3 , ME.TXTSumityear +4
So for Criteria 2007 I get 2007-2008-2009-2010-2011
How can I past these variable into the pivot ?
Because now , when I don't have data for 2009 I get in the column of 2009 the data of 2010 en in 2010 the data of 2011
But I use a criteria for my years and I take the 4 years next to it .
ME.TXTSubmitYear , ME.TXTSumityear +1 , ME.TXTSumityear +2 , ME.TXTSumityear +3 , ME.TXTSumityear +4
So for Criteria 2007 I get 2007-2008-2009-2010-2011
How can I past these variable into the pivot ?
Because now , when I don't have data for 2009 I get in the column of 2009 the data of 2010 en in 2010 the data of 2011
Thanks , Hope you can help
searching already for weeks
searching already for weeks
Romain | 2007.10.22 03:41 AM
THis is the code I'm using
[code]
Me.TXTIngaveJaar = Year(Date)
Me.IstSelection.RowSource = ""
DoCmd.RunSQL "Drop Table TBlTempInvest;"
DoCmd.RunSQL "Create Table TBlTempInvest (CibSpendingId Long,CibId Number,Jaar Number,Kost Number);"
StrSQL = "Select TBLSpending.CibSpendingID, TblSpending.CibID, TblSpending.Jaar, TblSpending.Kost "
StrSQL = StrSQL & "From TblSpending "
StrSQL = StrSQL & "WHERE (((TBlSpending.Jaar)=[Forms]![FrmCib]![TXTIngavejaar])) OR (((TBlSpending.Jaar)=[Forms]![FrmCib]![TXTIngavejaar]+1)) OR (((TBlSpending.Jaar)=[Forms]![FrmCib]![TXTIngavejaar]+2)) OR (((TBlSpending.Jaar)=[Forms]![FrmCib]![TXTIngavejaar]+3)) OR (((TBlSpending.Jaar)=[Forms]![FrmCib]![TXTIngavejaar]+4));"
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert into TBLTempInvest " & StrSQL
DoCmd.SetWarnings True
Me.IstSelection.RowSource = ""
DoCmd.RunSQL "Drop Table TBlTempInvest;"
DoCmd.RunSQL "Create Table TBlTempInvest (CibSpendingId Long,CibId Number,Jaar Number,Kost Number);"
StrSQL = "Select TBLSpending.CibSpendingID, TblSpending.CibID, TblSpending.Jaar, TblSpending.Kost "
StrSQL = StrSQL & "From TblSpending "
StrSQL = StrSQL & "WHERE (((TBlSpending.Jaar)=[Forms]![FrmCib]![TXTIngavejaar])) OR (((TBlSpending.Jaar)=[Forms]![FrmCib]![TXTIngavejaar]+1)) OR (((TBlSpending.Jaar)=[Forms]![FrmCib]![TXTIngavejaar]+2)) OR (((TBlSpending.Jaar)=[Forms]![FrmCib]![TXTIngavejaar]+3)) OR (((TBlSpending.Jaar)=[Forms]![FrmCib]![TXTIngavejaar]+4));"
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert into TBLTempInvest " & StrSQL
DoCmd.SetWarnings True
StrSql1 = "TRANSFORM First(TBlTempInvest.Kost) AS FirstOfKost "
StrSql1 = StrSql1 & "SELECT TBlTempInvest.CibId, Afdelingen.Postteken, CategoryCib1.Categoryafkor, Personeel.UserID, TblCib.Omschrijving, Prioriteiten.PrioriAfk, QryInvestSum_Crosstab.[Total Of Kost] "
StrSql1 = StrSql1 & "FROM Prioriteiten INNER JOIN (Personeel INNER JOIN (CategoryCib1 INNER JOIN (Afdelingen INNER JOIN ((TblCib INNER JOIN TBlTempInvest ON TblCib.CibID = TBlTempInvest.CibId) INNER JOIN QryInvestSum_Crosstab ON TblCib.CibID = QryInvestSum_Crosstab.CibID) ON Afdelingen.AfdelingsID = TblCib.AfdelingsID) ON CategoryCib1.Category1ID = TblCib.Category1ID) ON Personeel.WerknemersID = TblCib.WerknemersID) ON Prioriteiten.PrioriteitenID = TblCib.PrioriteitID "
StrSql1 = StrSql1 & "WHERE (((TblCib.DatumGoedgekeurd) Is Null)) "
StrSql1 = StrSql1 & "GROUP BY TBlTempInvest.CibId, Afdelingen.Postteken, CategoryCib1.Categoryafkor, Personeel.UserID, TblCib.Omschrijving, Prioriteiten.PrioriAfk, QryInvestSum_Crosstab.[Total Of Kost] "
StrSql1 = StrSql1 & "PIVOT TBlTempInvest.Jaar;"
[/code]
StrSql1 = StrSql1 & "SELECT TBlTempInvest.CibId, Afdelingen.Postteken, CategoryCib1.Categoryafkor, Personeel.UserID, TblCib.Omschrijving, Prioriteiten.PrioriAfk, QryInvestSum_Crosstab.[Total Of Kost] "
StrSql1 = StrSql1 & "FROM Prioriteiten INNER JOIN (Personeel INNER JOIN (CategoryCib1 INNER JOIN (Afdelingen INNER JOIN ((TblCib INNER JOIN TBlTempInvest ON TblCib.CibID = TBlTempInvest.CibId) INNER JOIN QryInvestSum_Crosstab ON TblCib.CibID = QryInvestSum_Crosstab.CibID) ON Afdelingen.AfdelingsID = TblCib.AfdelingsID) ON CategoryCib1.Category1ID = TblCib.Category1ID) ON Personeel.WerknemersID = TblCib.WerknemersID) ON Prioriteiten.PrioriteitenID = TblCib.PrioriteitID "
StrSql1 = StrSql1 & "WHERE (((TblCib.DatumGoedgekeurd) Is Null)) "
StrSql1 = StrSql1 & "GROUP BY TBlTempInvest.CibId, Afdelingen.Postteken, CategoryCib1.Categoryafkor, Personeel.UserID, TblCib.Omschrijving, Prioriteiten.PrioriAfk, QryInvestSum_Crosstab.[Total Of Kost] "
StrSql1 = StrSql1 & "PIVOT TBlTempInvest.Jaar;"
[/code]
Romain | 2007.10.22 03:48 AM
Hi Romain,
I haven't got a lot of time to look right now, but it seems to me that all you need to do is modify the last line of the crosstab query you are building to this:
StrSql1 = StrSql1 & "PIVOT TBlTempInvest.Jaar in (" & ME.TXTSubmitYear & "," & ME.TXTSumityear +1 & "," & ME.TXTSumityear +2 & "," & ME.TXTSumityear +3 & "," ME.TXTSumityear +4 & ");"
Let me know if that doesn't work or I've missed the point. Good luck.
ewbi.develops | 2007.10.22 10:12 AM
Hallo Ewbi
You are super! This is great
Which I found you a little earlier .
Would have save me a long seach
Would have save me a long seach
Thanks again
Romain | 2007.10.23 03:27 AM
I found it very useful.
Mary K. | 2007.11.08 03:00 PM
thanks, this saved my day :-)
urs | 2007.11.15 01:52 AM
Thank you very much for this information, it saved my day aswell !
Andy | 2008.01.23 02:18 AM
SELECT qryavailableHours_Total.Dept, qryavailableHours_Total!total-qryavailableHours_Exclusions!Total AS Total, qryavailableHours_Total!mtd-qryavailableHours_Exclusions!mtd AS MTD
FROM qryavailableHours_Total LEFT JOIN qryavailableHours_Exclusions ON qryavailableHours_Total.Dept = qryavailableHours_Exclusions.Dept;
FROM qryavailableHours_Total LEFT JOIN qryavailableHours_Exclusions ON qryavailableHours_Total.Dept = qryavailableHours_Exclusions.Dept;
The query is structured as above. I want to add a IIf function stating that , IF " qryavailableHours_Exclusions!Total " is null then consider putting a zero on its place.
Please help me on this.
Thanks,
-Neil
Neil | 2008.03.17 09:09 AM
Seriously great solution. saved my day too.
Thank you author!!
Thank you author!!
harish Sukhwani | 2008.04.06 02:34 PM
Hey all!
Maybe you guys can help me:
I'm looking for some particular case of Transform-Pivot, where you don't use SUM(..) in the Transform, but just a text field...
For example, I have this structure:
Table Person(IdPerson, Name, age, etc)
Table MetaFields (IdField, Desc)
Table Data (IdPerson, IdField, Value)
Maybe you guys can help me:
I'm looking for some particular case of Transform-Pivot, where you don't use SUM(..) in the Transform, but just a text field...
For example, I have this structure:
Table Person(IdPerson, Name, age, etc)
Table MetaFields (IdField, Desc)
Table Data (IdPerson, IdField, Value)
Do you catch the idea? its like dynamic fields for a Person table, and I want to put all the meta fields as column, just like Name, age, etc... (I dont care about nulls, i dont need to filter them)
I did the query, but it returns one row for each meta field...
Thanks in advance!
Daniel | 2008.04.22 09:35 AM
Excellent article, thank you!
JulienV | 2008.05.07 04:06 AM
It also works fine without the aggregate function. This SQL put each value (max 255) for IdNr into a column, witout the aggregating...
TRANSFORM year
SELECT IdNr
FROM Tabel1
WHERE year In ('2007','2008','2009')
GROUP BY IdNr
ORDER BY 1, 2
PIVOT year;
SELECT IdNr
FROM Tabel1
WHERE year In ('2007','2008','2009')
GROUP BY IdNr
ORDER BY 1, 2
PIVOT year;
/Hans
Hans Norén | 2008.11.02 01:42 AM
Thanks for the article
Matthew Miskiewicz | 2008.12.05 01:39 PM
Thanks!
Rob | 2009.04.24 01:42 PM
I haven't done databases since the mid 90's so fixing a problem at work with Access/pivot is befuddling me. The query is:
TRANSFORM Avg([Outpatient Input Subcategory Ratings].[Rating Assigned]) AS [AvgOfRating Assigned]
SELECT [Outpatient Input Subcategory Ratings].[Variable Name]
FROM [Outpatient Input Subcategory Ratings]
GROUP BY [Outpatient Input Subcategory Ratings].[Variable Name]
PIVOT "Qtr " & Format([Date Rated],"q") In ("Qtr 1","Qtr 2","Qtr 3","Qtr 4");
SELECT [Outpatient Input Subcategory Ratings].[Variable Name]
FROM [Outpatient Input Subcategory Ratings]
GROUP BY [Outpatient Input Subcategory Ratings].[Variable Name]
PIVOT "Qtr " & Format([Date Rated],"q") In ("Qtr 1","Qtr 2","Qtr 3","Qtr 4");
I understand that it was developed using the crosstab wizard. However, the company is moving to semi-annual reporting and this is not a default format for MS. They want new reports to show "First Half", "Second Half". I played with q1+q2 type schemes in the query, but no luck.
Any help would be greatly appreciated....
Chris
ctcbryan | 2009.07.16 12:07 PM
Hi Chris,
You should be able to just change the PIVOT portion of that SQL TRANSFORM statement to this:
PIVOT IIf(Month([Date Rated]) < 7, "First Half", "Second Half")
You could add an "... In ('First Half', 'Second Half')" to this to be explicit about the order of the columns, but it's repetitive and I don't think it's necessary.
Btw, in case you didn't already know, without a WHERE clause to restrict the year of the rows being considered (or unless you are clearing the table of rows having [Date Rated] values for other years), you'll be averaging and reporting variable ratings for multiple years.
Hope this helps. Good luck!
ewbi.develops | 2009.07.16 02:16 PM
that was perfect - thank you! The table in the transform statement is actually a query that does that "year" filter.
Again, all the best!!
ctcbryan | 2009.07.17 07:53 AM
I need to calculate the difference between two ros in a cross tab. So, I have the following:
TRANSFORM Sum(RawData.ItemValue) AS SumOfItemValue
SELECT RawData.ShortName, RawData.ZFYOYR, RawData.SourceSystem
FROM RawData
GROUP BY RawData.ShortName, RawData.ZFYOYR, RawData.SourceSystem
PIVOT RawData.ItemName;
SELECT RawData.ShortName, RawData.ZFYOYR, RawData.SourceSystem
FROM RawData
GROUP BY RawData.ShortName, RawData.ZFYOYR, RawData.SourceSystem
PIVOT RawData.ItemName;
I am getting the values from two different source systems and would like to see the differences. So, I would like to have it calculate a new row that has a value for the difference between two systems. The current output looks like this:
Shortname ZFORYR SourceSystem AVal Bval
P1 2000 Main 100 200
P1 2000 Remote 95 200
P1 2001 Main 480 820
P1 2001 Remote 480 820
P1 2000 Main 100 200
P1 2000 Remote 95 200
P1 2001 Main 480 820
P1 2001 Remote 480 820
I would like it to look like this:
Shortname ZFORYR SourceSystem AVal Bval
P1 2000 Main 100 200
P1 2000 Remote 95 200
P1 2000 Difference 5 0
P1 2001 Main 480 820
P1 2001 Remote 480 820
P1 2001 Diference 0 0
Shortname ZFORYR SourceSystem AVal Bval
P1 2000 Main 100 200
P1 2000 Remote 95 200
P1 2000 Difference 5 0
P1 2001 Main 480 820
P1 2001 Remote 480 820
P1 2001 Diference 0 0
Any thoughts?
Mike | 2009.11.17 06:40 AM
Yikes, sorry Mike, just realized you left this comment in November. I don't suppose you still need help with this, but if you do, please just let me know - maybe send an email to the address at the bottom of the left margin above.
ewbi.develops | 2009.12.06 07:36 PM
Hey i have a question for you, i know this post is fairly old but i am looking for some answers. I was reading through this thread and it looks like you have everything i am looking for but one thing. In your PIVOT tables when there is not a value, it is blank. Is there anyway i can replace those blank spaces with zeros? Please email me back with a possible sample, this is the issue i am trying to resolve. Maybe it could be something like this (even though this is not working) Is there a solution for something like what i wrote below?
TRANSFORM If(IsNull(Sum(mytable.amount)),0) AS total
SELECT mytable.project
FROM mytable
GROUP BY mytable.project
PIVOT mytable.year;
SELECT mytable.project
FROM mytable
GROUP BY mytable.project
PIVOT mytable.year;
Anna | 2010.03.02 08:37 AM
Hi Anna,
I don't believe there's anyway to overcome the blank values within the TRANSFORM query itself, as these represent GROUPs for which there are simply no rows. You either need to filter the source data (or add pivot constraints) to avoid these rows, or expand the source data to include rows for all GROUPs (UNION, maybe?), or save the TRANSFORM query and use it as the basis for another query that adds 0 to the value columns to overcome the nulls (which requires explicitly naming the columns resulting from the TRANSFORM, a la a fixed set of PIVOT values - yuck). This post still gets traffic, so maybe someone will jump in with a better solution?
Good luck!
ewbi.develops | 2010.03.02 05:09 PM
Well thanks for your help anyway. I am not that savy at this stuff. I suppose i will have to change the whole query.
Anna | 2010.03.03 08:00 AM
Hello its me again. Back on this problem. I was just reading your response and it throws me off a little. I know you mention that the TRANSFORM represent groups and are not rows. I am guessing that they are not in a table format but just a crosstab query as a whole so i dont understand how you can use it as a basis for another query. I am not sure how that would work. I would love to just get rid of this statement all together and create a new one but I would rather quit my job first than try to attempt that. hahaahahaha i am a total beginner. The reponse you gave me, I am still trying to translate into English. Sorry, but this is what i have right now which works but does not allow me to go through and replace the nulls with zeros. Any ideas would be much appreciated.
TRANSFORM Sum(Working.Amount) AS [The Value]
SELECT Working.[Account No], [Account Numbers - DBA].Name AS AcctName, Working.Category
FROM (Categories INNER JOIN Working ON Categories.DBA = Working.Category) INNER JOIN [Account Numbers - DBA] ON (Working.[Account No] = [Account Numbers - DBA].[Account No]) AND (Categories.DBA = [Account Numbers - DBA].Category)
WHERE (((Working.Category)=64))
GROUP BY Working.[Account No], [Account Numbers - DBA].Name, Working.Category
PIVOT Working.Yr;
SELECT Working.[Account No], [Account Numbers - DBA].Name AS AcctName, Working.Category
FROM (Categories INNER JOIN Working ON Categories.DBA = Working.Category) INNER JOIN [Account Numbers - DBA] ON (Working.[Account No] = [Account Numbers - DBA].[Account No]) AND (Categories.DBA = [Account Numbers - DBA].Category)
WHERE (((Working.Category)=64))
GROUP BY Working.[Account No], [Account Numbers - DBA].Name, Working.Category
PIVOT Working.Yr;
Anna | 2010.03.25 11:36 AM
Hi Anna,
You can use a TRANSFORM query (or any query) as the basis for another query by first saving it and giving it a name and then writing another query that uses the just-saved named query as its data source. So, if I create a TRANSFORM query and save it as MyTransformQuery, I can then write another query that selects * from MyTransformQuery.
And, as I was suggesting, you could, if you knew the saved TRANSFORM query's column names (which you would if you explicitly named them in the PIVOT clause), apply an IsNull (or just ...+0) conversion to each column to eliminate the blank values. This would be a post-TRANSFORM fix-up.
Alternatively, you could do a pre-TRANSFORM fix-up by basing the TRANSFORM query on a query (or series of queries) that use UNION or otherwise expand the base data set to account for the missing PIVOT values (years) in each of the TRANSFORM's resulting GROUPs.
Unfortunately, it's a bit tough to convey all this in a blog comment. Hopefully this makes some sense.
ewbi.develops | 2010.03.30 12:38 PM
I cant believe it finally worked. I had no idea you can do a select from the results of another query. I have been looking for so many options but not once did i see anywhere that you could do that. Thank you so much for your help and starting my 3 day weekend off on a great note. I feel like i just won the lottery. Here is what i ended up with with Reports being the transform query. Thank You Thank You Thank You!!!! Just a few more ends to tie up and I will finally be done with this. Dont be surprised if you hear from me again.
Select [Account No] As AcctNo, AcctName, Category,
IIF( [1] Is Null, 0, [1]) As One,
IIF( [2] Is Null, 0, [2]) As Two
FROM Reports;
IIF( [1] Is Null, 0, [1]) As One,
IIF( [2] Is Null, 0, [2]) As Two
FROM Reports;
Anna | 2010.04.08 12:43 PM
Hi,
I am using average with Transform. How do I format the numbers to 0 decimals?
I am using average with Transform. How do I format the numbers to 0 decimals?
TRANSFORM Avg(uml.uml) AS AvgOfuml
SELECT uml.agency_code
FROM uml
GROUP BY uml.agency_code
ORDER BY uml.agency_code, uml.year
PIVOT uml.year;
SELECT uml.agency_code
FROM uml
GROUP BY uml.agency_code
ORDER BY uml.agency_code, uml.year
PIVOT uml.year;
Alice | 2010.09.02 02:17 PM
Sorry never mind. I did it in the properties in design view. I was wondering if I could add formatting into my code.
Thanks
Thanks
Alice | 2010.09.02 02:24 PM
Hey,
I am having a problem, I have two tables which given the values month wise for goods. Both the tables contains common goods (product codes) month wise.
But table 1 is we can say whole and table 2 is subset, and i need the difference of the values in table 3 product code and month wise. I m using Crosstab function but getting stuck and the transform func is giving error too..
I am having a problem, I have two tables which given the values month wise for goods. Both the tables contains common goods (product codes) month wise.
But table 1 is we can say whole and table 2 is subset, and i need the difference of the values in table 3 product code and month wise. I m using Crosstab function but getting stuck and the transform func is giving error too..
Riti | 2011.05.18 09:44 PM
Riti, not quite following what you've got. Can you describe some actual rows from the first two tables and then describe what you want to get from their difference?
ewbi.develops | 2011.05.18 11:57 PM
Here's my SQL statement:
TRANSFORM Count(conditionAccuralQuery.cidcount) AS CountOfcidcount SELECT "" AS HyperLink, conditionAccuralQuery.businessname, conditionAccuralQuery.name FROM conditionAccuralQuery
GROUP BY conditionAccuralQuery.businessname, conditionAccuralQuery.name
PIVOT conditionAccuralQuery.cid
Here's the conditionAccuralQuery:
SELECT bn.fieldname AS businessname, sm.cid, ot.name, count(sm.cid) AS cidcount
FROM ((tblObject AS ot INNER JOIN tblDataElement AS de ON ot.oid=de.oid) INNER JOIN tblBusinessName AS bn ON de.bid=bn.bid) INNER JOIN qrySuperMapping AS sm ON de.did=sm.did
GROUP BY bn.fieldname, sm.cid, ot.name
HAVING (((ot.name)='EPoolTalk UI'));
FROM ((tblObject AS ot INNER JOIN tblDataElement AS de ON ot.oid=de.oid) INNER JOIN tblBusinessName AS bn ON de.bid=bn.bid) INNER JOIN qrySuperMapping AS sm ON de.did=sm.did
GROUP BY bn.fieldname, sm.cid, ot.name
HAVING (((ot.name)='EPoolTalk UI'));
With respect to the transform query, I want 'X' to appear anywhere the number of occurances is greater than one. How do I achieve this?
Curtis
Curtis Sumpter | 2011.07.03 09:26 PM
Hi Curtis,
No time to put together a sample db to test with, but can't you just replace Count(conditionAccuralQuery.cidcount) in your TRANSFORM clause with IIF(Count(conditionAccuralQuery.cidcount) > 1, "x", "")?
ewbi.develops | 2011.07.05 07:12 PM
How can I save the results of a simple cross tab into a temp table? For instance if I wanted to save the results of the query:
PARAMETERS foryear Short;
TRANSFORM Sum(mytable.amount) AS total
SELECT mytable.project
FROM mytable
WHERE mytable.year In ([foryear],[foryear]+1)
GROUP BY mytable.project
PIVOT mytable.year;
PARAMETERS foryear Short;
TRANSFORM Sum(mytable.amount) AS total
SELECT mytable.project
FROM mytable
WHERE mytable.year In ([foryear],[foryear]+1)
GROUP BY mytable.project
PIVOT mytable.year;
into a temp table called temp1 so I could use that in another qry - how do I do that?
Many thanks!!
CB | 2011.11.24 11:01 AM
CB, do you wish to do this programmatically, or simply using the Access UI tools?
ewbi.develops | 2011.11.24 12:10 PM
Thanks..this helps a lot.
Is there any way to get the result of Transform and Pivot functions without using these two commands? With the normal SQL in Access?
My question is, if in the above example, if i have one more column 'Profit' and i have to get the result as "get a total of the amounts by project for each year for each profit". Is this possible?
Please reply me asap and thanks in advance.
Is there any way to get the result of Transform and Pivot functions without using these two commands? With the normal SQL in Access?
My question is, if in the above example, if i have one more column 'Profit' and i have to get the result as "get a total of the amounts by project for each year for each profit". Is this possible?
Please reply me asap and thanks in advance.
Ramya | 2012.07.06 03:01 AM
Is this possible? Sure. Fundamentally, pivot tables are just multi-dimensional GROUP BY statements having one aggregate operation (i.e., the TRANSFORM: e.g., SUM, AVG, etc.), where one dimension is selected for columnar presentation (i.e., the PIVOT). Without pivoting one of the dimensions to a columnar presentation, you can just write the GROUP BY statement, for example:
select project, year, profit, sum(amount) from mytable group by project, year, profit
ewbi.develops | 2012.07.06 05:49 AM
Hi,
I've learned a lot from this article. Thanks a lot!
I wonder if you could help for my following proble.
I have a table which has thousands of records and contains two fields as below:
Person Code
A G1D
A C3T
A C3T
B B4K
C A2T
A G1D
B E7S
B A2U
B H8P
Person Code
A G1D
A C3T
A C3T
B B4K
C A2T
A G1D
B E7S
B A2U
B H8P
How can I obtain the results as below so each row is a unique person and all the codes associate with that person is at the same row:
Person Code
A C3T, G1D
B A2U, B4k, E7S, H8P
C A2T
Many Thanks in advance!
Eason | 2012.08.05 02:24 PM
Hi Eason,
Good question. As best I can tell, there's no doing that with straight SQL. It's going to take some code-based post-processing or maybe a custom per-row routine to pull that off. A count-based crosstab of persons (rows) by code (columns) might help in the first case, because it would be pretty easy to loop over the columns having a non-0 value for each row appending the relevant column names (i.e., codes) together. If you're interested in such a thing, and need some help to do it, let me know and I'll try to find a minute to write up a quick sample. Good luck!
ewbi.develops | 2012.08.06 07:53 AM
Thank you for your reply. If you could, could you give me an example. Thanks again!
1 comments so far
kjjk
EmoticonEmoticon