Problems with query grouping dates (1 Viewer)

T

Triona*

Guest
Hi folks,
Im have a Customer Complaints table and im trying to form a query which will show the number of complaints Opened per month vs the number of complaints Closed per month...i want to use the query as the rowsource for a chart but the date formats keep getting messed up and i cant fix it...

The table has a DateofComplaint field, which is used to find open complaints per month, and a DateofClosure field, which is filled in when a complaint is closed and used to find closed complaints per month.

I've been finding the Open complaints in one query and the Closed in another query, and then created a third query to join the results together to form the rowsource for the graph...but the date formats are getting messed up along the way...ive pasted the queries below. Can anyone suggest an easier way to do this in one query???

1. qryOpenByMonth:

SELECT Format([CustomerCompsDB].[DateofComplaint],"mmm-yy") AS DateAdded, Count([CustomerCompsDB].[ID]) AS Total
FROM CustomerCompsDB
GROUP BY Format([CustomerCompsDB].[DateofComplaint],"mmm-yy")
WITH OWNERACCESS OPTION;

2. qyyClosedByMonth:

SELECT Format([CustomerCompsDB].[ClosedDate],"mmm-yy") AS ClosedDate, Count([CustomerCompsDB].[ID]) AS Total
FROM CustomerCompsDB
WHERE ((([CustomerCompsDB].[ClosedDate]) Is Not Null))
GROUP BY Format([ClosedDate],"mmm-yy")
WITH OWNERACCESS OPTION;

3. qryOpenvsClosed

SELECT Format([qryClosedByMonth].[ClosedDate],"mmm-yy") AS ClosedDate, [qryClosedByMonth].[Total] AS [Total Closed], [qryOpenByMonth].[Total] AS [Total Opened]
FROM qryOpenByMonth RIGHT JOIN qryClosedByMonth ON [qryOpenByMonth].[DateAdded]=[qryClosedByMonth].[ClosedDate]
WITH OWNERACCESS OPTION;

When query 3 is run is gives the wrong year (all 05 instead of 03,04,05 etc)

Please help!!!

thanks,
triona*
 

FoFa

Registered User
Joined
Jan 29, 2003
Messages
3,672
Don't format the dates in your third query. They are strings from the other formats already.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom