Greetings! I have a database with currently only just under 300 records but that has the potential to head into the thousands. I can't get specific as my company is "very concerned" about security, and I'm only able to participate in these forums by keeping all references to and specifics about the project I'm working on hidden. My primary table has 34 fields, all of which are necessary data about each record, and which do connect to other data in some ten other tables.
Many of the fields hold various dates. I have a series of calculations which count the number of target dates for a particular action within a certain date range and compare it to the number of actual actions that took place within a the date range to come up with a basic percentage for accomplishment of the related task.
A query which includes columns for a Record ID, Action Target Date, and Actual Action Date.
To count the number of targets for the action in 2014:
Number of Action Targets: Sum(IIf(Year([ActionTargetDate])=2014,1,0))
To count the number of actions that actually took place in 2014:
Number of Actual Actions: Sum(IIf(Year([ActualActionDate])=2014,1,0))
A report includes the data points from this query, each in their own text box, named txtActionTargetDates and txtActionActualDates. A third text box, txtPercentAccomplished, reports a simple percentage:
(I'm not sure if I needed to use the code tags or not here.)
However, what I'd like to do is have a formula that compares the target date of the action to the date of the actual action for that record and determines if that action was on time or not. I then would like to show a percentage of actions that happened on time. And I'm at a loss. I want it to only look in the subset of data for the year being looked at, in this case 2014. I think I'll need another query, but I'm not sure how to make the criteria be both the year and whether or not the actual action date falls before or on the same date as the target date. Is this as simple as putting the counting function I use above as one criteria and then doing another criteria which looks at when the dates occur with relation to each other? I haven't been able to get anything to work right. I'd appreciate any and all help.