Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 6th, 2015, 12:47 PM
Posts: n/a
Default Formulas using dates and percentages in Access 2010

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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Populating data from MS Access 2010 to VB.net 2010 treeview nodes jayqdomingo .NET 4 and Visual Studio 2010 General Discussions 0 March 19th, 2013 12:12 PM
Accessing OneNote 2010 from Access 2010 alapee Access VBA 1 December 7th, 2011 07:14 PM
Using Access 2010 and Access Services with SP 2010 kmaford BOOK: Professional SharePoint 2010 Development 0 September 14th, 2010 04:25 PM
Crystal Reports: Displaying percentages of total q Goalie35 Crystal Reports 0 December 21st, 2006 05:51 PM
Getting percentages with math functions tslag XSLT 1 June 22nd, 2006 11:47 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.