Rabu, 17 Februari 2016

RE: [MS_AccessPros] Crosstab as data source for subsequent queries

 

Glen: You're welcome.

John: Thanks, I like the idea.  Given my typical work, I would need the Distinct query to get the names and loop them to build the IN statement as most (all?) of this kind of querying for me is done in VBA on the fly.
Thinking out loud, could Access handle a distinct statement as a sub-query within the IN, and if so, would that "stabilize" the company name headings in the way we are discussing?

-Steve



To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 17 Feb 2016 06:49:05 -0500
Subject: RE: [MS_AccessPros] Crosstab as data source for subsequent queries

 

Thanks Steve, John, and Duane. Your comments and suggestions are very helpful.

 

Glenn

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, February 16, 2016 4:48 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Crosstab as data source for subsequent queries

 

 

Steve-

 

In my example, no DISTINCT required to find all the unique company names from the Customers table, but I can envision some cases where DISTINCT might be required.

 

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 7:51 PM, Steve Conklin StephenMConklin@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 

John:

It looks like, in order to build the IN, I would have to run a DISTINCT to get those values?

 

thanks,

Steve

 

 


To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 16 Feb 2016 18:42:00 +0000
Subject: RE: [MS_AccessPros] Crosstab as data source for subsequent queries

 

 

John, brilliant.  I'm going to remember this one.

 

I hope. 

 

;-)

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, February 16, 2016 10:52 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Crosstab as data source for subsequent queries

 



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

 

 

 

 



 



This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

 

 


__._,_.___

Posted by: Steve Conklin <stephenmconklin@hotmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (9)

.

__,_._,___

Tidak ada komentar:

Poskan Komentar