Crosstab query does not limit dates shown

[ZZSteele]

Hello,

I am trying to get a crosstab query to display JUST the dates that are selected from combo boxes on a form. Basically we look at past profit and loss statements and see what trends we can come up with. I have about 2 1/2 years worth of data and the only way I can get the data to show up is by using the following SQL statement...

TRANSFORM Sum([ZS multi month test].
[SumOfMBAL]) AS SumOfSumOfMBAL
SELECT [ZS multi month test].[GROUP NAME],
[ZS multi month test].[PL SORT], Sum([ZS multi month test].[SumOfMBAL]) AS [Total Of SumOfMBAL]
FROM [ZS multi month test]
GROUP BY [ZS multi month test].[GROUP NAME],
[ZS multi month test].[PL SORT]
ORDER BY [ZS multi month test].[PL SORT]
PIVOT [ZS multi month test].[DATE] In ("01/31/2003","02/28/2003","03/31/2003",
"04/30/2003","05/31/2003","06/30/2003",
"07/31/2003","08/31/2003","09/30/2003",
"10/31/2003","11/30/2003","12/31/2003",
"01/31/2004","02/29/2004","03/31/2004",
"04/30/2004","05/31/2004","06/30/2004",
"07/31/2004","08/31/2004","09/30/2004",
"10/31/2004","11/30/2004","12/31/2004",
"01/31/2005","02/28/2005","03/31/2005",
"04/30/2005","05/31/2005","06/30/2005",
"07/31/2005","08/31/2005","09/30/2005",
"10/31/2005","11/30/2005","12/31/2005");

The user selects a customer and a date range then the fields are populated. As you can guess the query has a long list of dates with empty fields and I don't want these to be shown.

I have searched for anything on past postings and did not find an answer. Let me know if you can come up with something or know a way to fix this (preferably) in SQL.

Thanks in advance

 

[mdmilner]

This may seem like a 'ramble' but - Access doesn't always behave the way you think it should when you try to create complex queries --- particularly if you try to use subqueries (queries within queries).

The workaround is to build them in two or more steps. Build a query, then another based on it.

My suggestion, based on the above is this. Build a query that sorts the date range, then base the pivot table on that query.

As your parameter, use something like:

WHERE [ZS multi month test].[DATE] >= #1/31/2003# and [ZS multi month test].[DATE] <= #12/31/2005#

As a note, notice the Pound sign (#) - that's necessary when using parameters with dates. It may also be the source of your syntax problem.

Mike

 

[ZZSteele]

I set up another query, but am still not able to get around the date thing. I am still not getting just the dates in the range to show up. I always have to list what will show (hard code) the labels. Is there some code that will let the chosen date range show and nothing else. Currently the query shows up like:

Name SumofBal 01/31/03 02/28/03 03/31/03 04/30/03 05/31/03
Wages 1234.00 ----------- 230.00 502.00 502.00 -----------

But I want it to show like:

Name SumofBal 02/28/03 03/31/03 04/30/03
Wages 1234.00 230.00 502.00 502.00


Thanks for helping