Glenn-
You can force the column names to be constant by adding an IN clause to the SQL.
TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT Products.[Product Name]
FROM Products INNER JOIN (Customers RIGHT JOIN (Orders INNER JOIN [Order Details] ON Orders.[Order ID] = [Order Details].[Order ID]) ON Customers.ID = Orders.[Customer ID]) ON Products.ID = [Order Details].[Product ID]
GROUP BY Products.[Product Name]
PIVOT Customers.Company In ("Company A","Company B","Company C");
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Feb 16, 2016, at 5:40 PM, 'Glenn Lloyd' argeedblu@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I find myself needing to work with crosstabs lately and needing to use the output of a crosstab as the data source for another query. Crosstab field names are dynamic. By that I mean that the field names in the query result depend on the data being transformed. So the field list will have differing numbers of fields from run to run. I am currently managing the output by using VBA to generate TRANSFORM SQL at runtime and then replace the SQL in a querydef. This provides me with the source for further downstream processing that is responsive to current data and user request.
Can anyone share their insight into using crosstabs especially as the source for further querying?
Glenn
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar