Running a Crosstab Query for a specific date range

[jol]

I want to be able to run a query that provides the results for a specific date range. I've been able to do this for a simple query using:
SELECT [Contact Tracker].[Date of Contact], [Contact Tracker].[Type of Contact], [Contact Tracker].[Contact Name]
FROM [Contact Tracker]
GROUP BY [Contact Tracker].[Date of Contact], [Contact Tracker].[Type of Contact], [Contact Tracker].[Contact Name]
HAVING ((([Contact Tracker].[Date of Contact]) Between [EnterStartDate] And [EnterEndDate]))
ORDER BY [Contact Tracker].[Type of Contact], [Contact Tracker].[Contact Name];


but when I try to do the same for a Crosstab query, I get error messages.

Here is my Crosstab query:
TRANSFORM Count([Contact Tracker].[ID]) AS CountOfID
SELECT [Contact Tracker].[Contact Name], Count([Contact Tracker].[ID]) AS [Total Of ID]
FROM [Contact Tracker]
GROUP BY [Contact Tracker].[Contact Name]
PIVOT [Contact Tracker].[Type of Contact];

I'd appreciate any advice!

 

[theDBguy]

Hi. Welcome to AWF!

What did the error message say?

 

[jol]

Hi. Welcome to AWF!

What did the error message say?

Thanks for the welcome! I'm happy to have this resource.

I've tried it multiple different ways and have gotten a multitude of errors.
If I try to add the HAVING statement above after the GROUP BY, it says "Syntax Error in TRANSFORM statement."
If I try adding between [EnterStartDate] and [EnterEndDate] under Criteria in the Design View:

TRANSFORM Count([Contact Tracker].ID) AS CountOfID
SELECT [Contact Tracker].[Contact Name], Count([Contact Tracker].ID) AS [Total Of ID]
FROM [Contact Tracker]
WHERE (([Contact Tracker].[Date of Contact] Between [EnterStartDate] And [EnterEndDate]))
GROUP BY [Contact Tracker].[Contact Name]
PIVOT [Contact Tracker].[Type of Contact];

it says "The Microsoft Access database engine does not recognize [EnterStartDate] as a valid field name or extension."

 

[ebs17]

PARAMETERS EnterStartDate Date, EnterEndDate Date;
TRANSFORM Count(ID) AS CountOfID
SELECT [Contact Name], Count(ID) AS [Total Of ID]
FROM [Contact Tracker]
WHERE [Date of Contact] Between [EnterStartDate] And [EnterEndDate]
GROUP BY [Contact Name]
PIVOT [Type of Contact];

 

[jol]

PARAMETERS EnterStartDate Date, EnterEndDate Date;
TRANSFORM Count(ID) AS CountOfID
SELECT [Contact Name], Count(ID) AS [Total Of ID]
FROM [Contact Tracker]
WHERE [Date of Contact] Between [EnterStartDate] And [EnterEndDate]
GROUP BY [Contact Name]
PIVOT [Type of Contact];

Thanks. When I tried this, I get the error:
Malformed GUID in query expression '[Date of Contact] Between [EnterStartDate] and [EnterEndDate]'.

 

[ebs17]

I have no idea where a GUID should be needed in a date field, and I don't know your table either.

 

[jol]

I have no idea where a GUID should be needed in a date field, and I don't know your table either.

I appreciate your attempt to help. I don't know where a GUID would be needed either. My table is quite simple.

 

[jol]

I got it to work with this SQL:
PARAMETERS [start date] DateTime, [end date] DateTime;
TRANSFORM Count([Contact Tracker].ID) AS CountOfID
SELECT [Contact Tracker].[Contact Name]
FROM [Contact Tracker]
WHERE ((([Contact Tracker].[Date of Contact]) Between [start date] And [end date]))
GROUP BY [Contact Tracker].[Contact Name]
PIVOT [Contact Tracker].[Type of Contact];

Thanks for the help!

 

[Pat Hartman]

SELECT [Contact Tracker].[Date of Contact], [Contact Tracker].[Type of Contact], [Contact Tracker].[Contact Name]
FROM [Contact Tracker]
GROUP BY [Contact Tracker].[Date of Contact], [Contact Tracker].[Type of Contact], [Contact Tracker].[Contact Name]
HAVING ((([Contact Tracker].[Date of Contact]) Between [EnterStartDate] And [EnterEndDate]))
ORDER BY [Contact Tracker].[Type of Contact], [Contact Tracker].[Contact Name];

The WHERE clause is evaluated before the aggregation and the HAVING is evaluated after the aggregation so logically, any field in the original select clause is available before aggregation and therefore criteria should use a WHERE. Only use HAVING when the feld in the criteria is actually calculated like Sum() or Count(), etc. This makes the queries more efficient. So even though you have no trouble with this query, change the HAVING to a WHERE for efficiency.

Now as to the crosstab. Apparently the programmer at MS who wrote the code to process the crosstab query didn't get the memo and so unlike other types of queries, the crosstab REQUIRES arguments to be defined. So, right click on the gray area of the query def and choose the Parameters option. Note that even if it is a sub query that has the parameters, they will need to be defined if the query is ultimately used in a crosstab.