[AccessD] Crosstab query

No I am simply pointing out that if you get in a situation where the number
of columns change, you have to manually edit SQL of a query.  As long as you
document where to go to make the change everything is fine, but it is
non-intuitive.  The other way you would be modifying (adding / deleting) the
fields in a table, perhaps with a make table query that you modify as the
number of columns change.  Also non-intuitive, but perhaps less so since the
table is visible.

I do use the IN() clause simply because that was the method I discovered
first.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Monday, September 10, 2007 2:10 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Crosstab query

Hi John: Are you suggesting the Andy's method would be more a more universal
solution. Jim


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Monday, September 10, 2007 5:40 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Crosstab query

This works well for places where the number of columns are fixed such as the
days of the week.  I use it as well.  The issue comes when you need to add a
column, you have to manually edit the SQL statement to add a value to the
IN() clause.  It works but is kludgy. 


John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Monday, September 10, 2007 7:13 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Crosstab query

Thanks Philippe, this looks like an excellent method. Jim