Query Criteria

[Guest]

I have a table regarding tours that are given at our facility. The main
fields are Tour ID, Tour Date, Tour Guide, Host Organization, etc. All of
the tours are in CY05 and CY06. I need to create a pivot chart that compares
the CY05 with CY06 sum of tours by Host Organization and Tour Date. I have a
query that works fine if I just want CY05 and a query that works fine for
CY06 and a query that works fine for both years combined. However, I am not
able to figure out how to set up criteria in one query that will break apart
the CY05 and CY06 results so that I can make the pivot chart display the two
years side by side. Any help is appreciated.

 

[Guest]

Post your SQL.

 

[Guest]

SELECT Left([HostDept/Org],2) AS Org, Count(*) AS OrgCount, Tours.TourDate
FROM Attendees INNER JOIN Tours ON Attendees.AttendeeID = Tours.AttendeeID
GROUP BY Left([HostDept/Org],2), Tours.TourDate
HAVING (((Tours.TourDate) Like "*6"));
This is the SQL I am using to populate the CY2006 Tours.

 

[Guest]

Change your TourDate field to a datatype of DateTime from your present text.

Use this query --
SELECT Left([HostDept/Org],2) AS Org, Count(*) AS OrgCount, Year([TourDate])
AS Tour_Year
FROM Attendees INNER JOIN Tours ON Attendees.AttendeeID = Tours.AttendeeID
GROUP BY Left([HostDept/Org],2), Year([TourDate]);

You can add a year to the criteria if you want. By using the DateTime field
you can make use of other breakouts like by the quarter, month, week, day, or
even hour.

SELECT Left([HostDept/Org],2) AS Org, Count(*) AS OrgCount, Tours.TourDate
FROM Attendees INNER JOIN Tours ON Attendees.AttendeeID = Tours.AttendeeID
GROUP BY Left([HostDept/Org],2), Tours.TourDate
HAVING (((Tours.TourDate) Like "*6"));
This is the SQL I am using to populate the CY2006 Tours.

Post your SQL.

 

[Guest]

Thank You!

Change your TourDate field to a datatype of DateTime from your present text.

Use this query --
SELECT Left([HostDept/Org],2) AS Org, Count(*) AS OrgCount, Year([TourDate])
AS Tour_Year
FROM Attendees INNER JOIN Tours ON Attendees.AttendeeID = Tours.AttendeeID
GROUP BY Left([HostDept/Org],2), Year([TourDate]);

You can add a year to the criteria if you want. By using the DateTime field
you can make use of other breakouts like by the quarter, month, week, day, or
even hour.

SELECT Left([HostDept/Org],2) AS Org, Count(*) AS OrgCount, Tours.TourDate
FROM Attendees INNER JOIN Tours ON Attendees.AttendeeID = Tours.AttendeeID
GROUP BY Left([HostDept/Org],2), Tours.TourDate
HAVING (((Tours.TourDate) Like "*6"));
This is the SQL I am using to populate the CY2006 Tours.

Post your SQL.

:

I have a table regarding tours that are given at our facility. The main
fields are Tour ID, Tour Date, Tour Guide, Host Organization, etc. All of
the tours are in CY05 and CY06. I need to create a pivot chart that compares
the CY05 with CY06 sum of tours by Host Organization and Tour Date. I have a
query that works fine if I just want CY05 and a query that works fine for
CY06 and a query that works fine for both years combined. However, I am not
able to figure out how to set up criteria in one query that will break apart
the CY05 and CY06 results so that I can make the pivot chart display the two
years side by side. Any help is appreciated.