[AccessD] Crosstab query

Hi A.D.Tejal:

Thanks for the link. I will review it further and see if there are any
revelations and usable techniques. I had the report model completed by
either setting a range or padding with dummy fields so it would always line
up. The days were in sequence so the report always worked.

The client admitted that is what they had asked for but not what they
needed. What they have decided on was to only show fields (dates) where
there are data hits and have the report extend over a number of months. In
addition they want the top header to show the month number and the bottom
header row to show the day number.

The information can be gathered through a cross tab query but the
requirement for 2 top rows, which an Access crosstab can not produce and a
series non sequential report columns, there appears to be no eloquent
solution... just brute force coding. :-(

If anyone has experience with a similar project would be so kind as to share
their expertise it would be greatly appreciated.

Again thank you A.D.Tejal.

Jim   

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
Sent: Monday, September 10, 2007 10:32 PM
To: Access Developers discussion and problem solving
Cc: A.D.TEJPAL
Subject: Re: [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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com