jwcolby
jwcolby at colbyconsulting.com
Mon Sep 10 07:40:10 CDT 2007
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 -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of philippe pons Sent: Monday, September 10, 2007 12:40 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Crosstab query Hi Jim, I discovered that recently: PIVOT column-headings-field IN(value1, value2, ...) Regards, Philippe 2007/9/10, Jim Lawrence <accessd at shaw.ca>: > > Hi All: > > When creating a crosstab query is there a way to force a column for > each day in a month even though there is no data for that date? > > I have experimented with adding a dummy record with name of 'zzz' and > one item for each date of the particular month. It is functional but kludgy. > There must be a better way. > > TIA > Jim > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com