Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Location
    galway, ireland
    Posts
    4

    Question Unanswered: MS Access: problem joining queries...date formats getting messed up

    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*

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Try removing the "Format" from your 1st 2 queries. When access is converting them back into dates for use in the third query, it converts
    Mar - 04 into 3/4/05.
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •