Crosstab Parameter Query

[leighms]

Hi

I am trying to run a cross tab parameter query for date range, to produce a table below;
--------Deceased----Moving----service----etc (Closed reasons)
Branch
abc------Totals---
def
ghi
My current sql statement appears as below, the problem I have is it will not seperate the totals for each month, it asks me for a date range but just gives me overall totals;
PARAMETERS [start] DateTime, [end] DateTime;
TRANSFORM Count([Closed accounts].[Date Closed]) AS [CountOfDate Closed]
SELECT [Closed accounts].Branch, Count([Closed accounts].[Date Closed]) AS [Total Of Date Closed]
FROM [Closed accounts]
GROUP BY [Closed accounts].Branch
PIVOT [Closed accounts].[Primary reason for account closure];

 

[Guus2005]

You use two parameters [Start] and [End] both datatime types which you don't use in the crosstab query.

Before converting the data into a crosstab query first create the result you want in a regular query.

Create the Where or Having clause using the parameters.
You are missing the field which represents the month. When you group by on that field you will get the sums that you want in the crosstab query.

HTH

 

[leighms]

Hi
I am still not having much luck, i created the following query;
SELECT [Closed accounts].[Date Closed], [Closed accounts].[Primary reason for account closure], [Closed accounts].Branch
FROM [Closed accounts]
WHERE ((([Closed accounts].[Date Closed]) Between [start date] And [end date]));
I the created a crosstab query from this, however I can't get the closed reasons as column headings, I know how to change these but when I do I get an error, cross tab query is;
TRANSFORM Count(qryparameterdaterange.[Primary reason for account closure]) AS [CountOfPrimary reason for account closure]
SELECT qryparameterdaterange.Branch, Count(qryparameterdaterange.[Primary reason for account closure]) AS [Total Of Primary reason for account closure]
FROM qryparameterdaterange
WHERE ((([qryparameterdaterange_DateClosed])<>False))
GROUP BY qryparameterdaterange.Branch
PIVOT Format([Date Closed],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

I feel that it is close I just can't seem to get everything I need

 

[Guus2005]

can you post a sample database?
It is easier for me to look into it.

 

[vbaInet]

You can only have one Column Heading. It is a Crosstab Query rule.

You can have more than one Row Heading.

 

[leighms]

HI

I am having trouble downloading the file, I keep getting error, will keep trying.
VBAinet - The report i am trying to generate involves rows that list the branch and columns that list reason codes. I currently have the exact same report that gives me YTD figures, I just need one that gives me month to month figures and rather then have 12 different queries, I would prefer to do a parameter query

 

[vbaInet]

If you don't want to PIVOT by the date field remove it and select your Reasons field as the Column Heading.

 

[leighms]

Hi

When I change the pivot to Primary Reasons, it comes up with the following error;
Access database does not recognize '[startdate] as a valid field name or expression.
The following SQL statement is one that shows YTD figures, I just need to a the parameter query so i can also do month to month, but each time I add a paramater it just shows the YTD totals
TRANSFORM Count([Closed accounts].[Date Closed]) AS [CountOfDate Closed]
SELECT [Closed accounts].Branch, Count([Closed accounts].[Date Closed]) AS [Total Of Date Closed]
FROM [Closed accounts]
GROUP BY [Closed accounts].Branch
PIVOT [Closed accounts].[Primary reason for account closure];

 

[vbaInet]

You need to add a WHERE part for the Date parameter.

 

[leighms]

I have pretty much tried every combination i can with this query, I don't think it will work. If i have the months as column headings it works but does not give me totals of each reason. Everytime I add a where statement or a parameter it states an error. I think because the column heading aren't months it wont work

 

[vbaInet]

Like I previously mentioned, you can ONLY have ONE Column Heading, not two, not three, just ONE.

If you just want to filter your records by a particular month then you need to add a WHERE part. If you've tried this and it's not working, what exactly is the error?