Wrox Programmer Forums Formulas using dates and percentages in Access 2010
 | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersâ€™ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
August 6th, 2015, 12:47 PM
 Hagridore Guest Posts: n/a
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:
Code:
`Number of Action Targets: Sum(IIf(Year([ActionTargetDate])=2014,1,0))`
To count the number of actions that actually took place in 2014:
Code:
` 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:
Code:
`=[txtActionActualDates]/[txtActionTargetDates]*100`
(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.

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is Off HTML code is OffTrackbacks are Off Pingbacks are On Refbacks are Off Forum Rules

 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

All times are GMT -4. The time now is 09:09 AM.