[AccessD] Crosstab query

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




More information about the AccessD mailing list