[AccessD] Crosstab query

Jim,

    As the number of days for various one month periods are not identical, left join method using a query filtered for the spell in question, as suggested by Andy, should be preferred.

        My sample db named MotelBooking demonstrates this approach. It is available at Rogers Access Library (other developers library). Link - http://www.rogersaccesslibrary.com/OtherLibraries.asp#Tejpal,A.D.


    On the main switchboard, second command button captioned "Booking Status (Style 2) - Dates Across Columns", is pertinent to this case.

Best wishes,
A.D.Tejpal
---------------

  ----- Original Message ----- 
  From: jwcolby 
  To: 'Access Developers discussion and problem solving' 
  Sent: Monday, September 10, 2007 23:56
  Subject: Re: [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