Results 1 to 2 of 2

Thread: Query Help

  1. #1
    Join Date
    Feb 2008
    Posts
    27

    Unanswered: Query Help

    I have the query below so far, what I am trying to produce out of it is a column called Red Flag. I am using data for last month and the month before, I am trying to create a report where if an Atty Grade is C or D for both last month and the month before and they are in the same state then the red flag would be a "Yes". I figure it would be an iif statement, but can't seem to get it too look to see if the Atty name and state are equal and grade is C or D for the months I need. Any help...

    SELECT [PMSI Master].State, [PMSI Master].Atty, [PMSI Master].[Days to Sale On Time], [PMSI Master].[Days to Sale Total Count], [PMSI Master].[DTS Summary - PMSI_% On Time], [PMSI Master].[DTS Grade], [PMSI Master].[DTS Summary - PMSI_Month], Year([DTS Summary - PMSI_Month])*12+DatePart("m",[DTS Summary - PMSI_Month])=Year(Date())*12+DatePart("m",Date())-1 AS [Previous Month], Year([DTS Summary - PMSI_Month])*12+DatePart("m",[DTS Summary - PMSI_Month])=Year(Date())*12+DatePart("m",Date())-2 AS [Previous Month 2]
    FROM [PMSI Master]
    WHERE ((([PMSI Master].[DTS Grade])="C" Or ([PMSI Master].[DTS Grade])="D") AND ((Year([DTS Summary - PMSI_Month])*12+DatePart("m",[DTS Summary - PMSI_Month])=Year(Date())*12+DatePart("m",Date())-1)=-1)) OR ((([PMSI Master].[DTS Grade])="C" Or ([PMSI Master].[DTS Grade])="D") AND ((Year([DTS Summary - PMSI_Month])*12+DatePart("m",[DTS Summary - PMSI_Month])=Year(Date())*12+DatePart("m",Date())-2)=-1))
    ORDER BY [PMSI Master].State;

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Make one query to get the Cs and Ds from 2 months back.

    Make another query to get the Cs and Ds from 1 month back.

    Make another query with both these queries in it and link them.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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