Jim Lawrence
accessd at shaw.ca
Wed Sep 12 12:21:23 CDT 2007
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