Running a Crosstab Query for a specific date range (1 Viewer)

jol

New member
Local time
Today, 12:56
Joined
Dec 18, 2023
Messages
5
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

I’m here to help
Staff member
Local time
Today, 09:56
Joined
Oct 29, 2018
Messages
21,553
Hi. Welcome to AWF!

What did the error message say?
 

jol

New member
Local time
Today, 12:56
Joined
Dec 18, 2023
Messages
5
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:
Code:
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."
 
Last edited:

ebs17

Well-known member
Local time
Today, 18:56
Joined
Feb 7, 2020
Messages
1,991
SQL:
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

New member
Local time
Today, 12:56
Joined
Dec 18, 2023
Messages
5
SQL:
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

Well-known member
Local time
Today, 18:56
Joined
Feb 7, 2020
Messages
1,991
I have no idea where a GUID should be needed in a date field, and I don't know your table either.
 

jol

New member
Local time
Today, 12:56
Joined
Dec 18, 2023
Messages
5
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

New member
Local time
Today, 12:56
Joined
Dec 18, 2023
Messages
5
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

Super Moderator
Staff member
Local time
Today, 12:56
Joined
Feb 19, 2002
Messages
43,512
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.

1702933181057.png
 

Users who are viewing this thread

Top Bottom