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 be counted 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 [Report Date], 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.

Have you tried using an "Or" instead of an "And" statement? e.g. Date1 <= 1/2/2012 or Date2 <- 1/2/2012, etc. By doing this, only one of the values needs to be true in order to that row of data to be included.

I have tried that. Because it is a parameter query, I tried R4RPlan_Date < [Report Date] OR R4RActual_Date < [Report Date]. It didn't work, it just went off the R4RPlan_Date. I need it to look at the fields individually, without me having to write separate queries for each date field or it asking the user to reenter the date multiple times.

Make sense?

Well, could you post that SQL where you have it like that? It's hard to see what's going on without it. My guess would be that you also have a combination of "And" statements somewhere in there but no way to know for sure without seeing that actual syntax.

Sure.


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])) OR (((RICEFW_Dates.R4RActual_Date)<[Report Date]))
GROUP BY RICEFW_Dates.Owning_Team, RICEFW_Dates.Del_Type;


I just ran it again to see my results. It isn't necessarily counting all of the specs in the planned date. So for the team "Reporting" I know I have 42 planned specs with 16 actual. The query this way shows me 16 planned and 16 actual. It's also disregarding the parameter for the Owning Team now. The other problem with the OR statement is I don't want the planned date to be counted if it does not match the parameter criteria because that spec was not planned to be done for the time frame I am running the report. For example...

With parameter in R4RPlan_Date with [Report Date] < 2/22/12:

Owning_Team Del_Type R4RPlan_Date R4RActual_Date
DT - Data Functional Design 2/3/2012 2/6/2012
DT - Data Functional Design 2/17/2012
DT - Data Functional Design 2/17/2012 2/14/2012
DT - Data Functional Design 2/17/2012

With parameter in R4RActual_Date with [Report Date] < 2/22/12:

Owning_Team Del_Type R4RPlan_Date R4RActual_Date
DT - Data Functional Design 2/3/2012 2/6/2012
DT - Data Functional Design 2/17/2012 2/14/2012
DT - Data Functional Design 3/23/2012 2/13/2012

There are 5 unique planned dates, but only 4 match the criteria of being < 2/22/12. In my counts I want to see 4 planned counted and 3 actual counted in my results.

This post has been edited by stuckinaccess: Feb 23 2012, 03:22 PM

Assuming that you do not want to show any rows that contain a scope of "Removed" and the "Team" must have "*" in it (BTW, this is used as a wildcard in syntax, so probably not the best to be going on). Your syntax would be like this.



(((RICEFW_Dates.Scope)<>"Removed") AND ((RICEFW_Dates.Owning_Team) Like "*" & [Team]) AND ((RICEFW_Dates.R4RPlan_Date)<[Report Date])) OR (((RICEFW_Dates.Scope)<>"Removed") AND ((RICEFW_Dates.Owning_Team) Like "*" & [Team]) (((RICEFW_Dates.R4RActual_Date)<[Report Date]))

I just basically C&Ped your syntax, so the # of ")" might be off.

Ugh! I figured this was something basic I was forgetting to do.... How would you suggest removing the additional counts as mentioned above?

I was using the wildcard just so I could quickly check my results for accuracy. In the future a form will be made with a combo box to choose the team.

Since the revised date supersedes the plan date, I was thinking of using the following IF statement in my query. Unfortunately, I don't have any revised dates in order to check for accuracy. Will please look at this and let me know if you think I have it written properly, and in the correct sequence?


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 ((IIf([RICEFW_Dates]![R4R_Revised_Date]>[RICEFW_Dates]![R4RPlan_Date],[RICEFW_Dates]![R4R_Revised_Date],[RICEFW_Dates]![R4RPlan_Date]))<[Report Date])) OR (((RICEFW_Dates.Scope)<>"Removed") AND ((RICEFW_Dates.Owning_Team) Like "*" & [Team]) AND ((RICEFW_Dates.R4RActual_Date)<[Report Date]))
GROUP BY RICEFW_Dates.Owning_Team, RICEFW_Dates.Del_Type;


Thank you for taking the time to help me, it's greatly appreciated.

This post has been edited by stuckinaccess: Feb 23 2012, 03:44 PM

So I wrote out the complete query and although the actual dates for each field are correct, the plan dates are not. Each plan date field says there are 5 plan dates because the first field pulled in 5 rows. So even though the other fields may have planned dates in them, they do not match the criteria of being <[Report Date]. I'm not sure how to fix that. Suggestions?

This post has been edited by stuckinaccess: Feb 23 2012, 04:16 PM

If you could send me a stripped down version of yoru database with just some dummy data, I can take a look at it. You can upload it here or just go to my website and e-mail me through there or you can even upload the database through the "Request Information" section. It would be easier to see what's going on that way.

Thank you for all your help. I ended up figuring out what my problem was.

stuckinaccess,

I'm glad you got your Paramter Query fixed, but it seems like you might have your database designed incorrectly. Instead of having different date Fields for each of the Reports you should just have one. Then add another Field for the name of Report Type. Thenmaybe you could do a Crosstab -Parameter.

Just my two cents!
RAZMaddaz
(IMG:style_emoticons/default/2cents.gif)