1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access multiple date query issue

Discussion in 'Business Applications' started by nomad0321, Feb 23, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. nomad0321

    nomad0321 Thread Starter

    Joined:
    Feb 23, 2012
    Messages:
    2
    Hello!

    I have an issue I've looked at 50 different ways from Sunday and am stuck on the resolution. I am tracking metrics for a project, stored on SharePoint, which I dump into Excel and use Access 2007 for my reports.

    The report has multiple date fields, i.e.: R4RPlan_Date, R4RActual_Date, BusPlan_Date, BusActual_Date, etc... on table RICEFW_Dates.

    What I am trying to do is set a parameter to query all the fields for a specific date, i.e. <[Report Date], without having to run multiple parameter queries. The end result will be a count of the number of dates in each field meeting the criteria.

    The problem comes in when teams complete their criteria before it was planned, i.e., R4RPlan_Date = 3/23/12, R4RActual_Date = 2/6/12, <[Report Date] = 2/22/12.

    If I add <[Report Date] as a criteria for the R4RPlan_Date, I will get all the plan dates I'm looking for, but I will not get all of the actuals since the plan date is after the specified [Report Date], but the actual was completed w/in the frame of the parameter. If I put the [Report Date] parameter in the R4RActual_Date field, I get the correct number of actuals, but since I am querying the field with lesser amount of data, I do not get all of the planned dates which meet the [Report Date] parameter. So if the plan date is greater than the parameter date, I want it to show up IF the actual date is not null, otherwise it should follow the parameter criteria (<[Report Date]). This needs to completed for every date field in the query. Each date has a planned and actual.

    The other caveat is there is a revised date which will supersede the plan date. So if R4R_Revised_Date > R4RPlan_Date, then R4R_Revised_Date should be used for the parameter criteria, otherwise the R4RPlan_Date should be used for the parameter criteria.

    I apologize if this is confusing, but any help would be appreciated. I would be more than happy to clarify any of the points I've made.

    Here is the SQL I am working with:

    PARAMETERS [Report Date] DateTime;
    SELECT RICEFW_Dates.Owning_Team, RICEFW_Dates.Del_Type, Count(RICEFW_Dates.R4RPlan_Date) AS CountOfR4RPlan_Date, Count(RICEFW_Dates.R4RActual_Date) AS CountOfR4RActual_Date
    FROM RICEFW_Dates
    WHERE (((RICEFW_Dates.Scope)<>"Removed") AND ((RICEFW_Dates.Owning_Team) Like "*" & [Team]) AND ((RICEFW_Dates.R4RPlan_Date)<[Report Date]))
    GROUP BY RICEFW_Dates.Owning_Team, RICEFW_Dates.Del_Type;


    Thanks.
     
  2. nomad0321

    nomad0321 Thread Starter

    Joined:
    Feb 23, 2012
    Messages:
    2
    Solved in another forum.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1042367