Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Create chart of record aging current and any historical time range

Posted on 2012-03-15
11
Medium Priority
?
279 Views
Last Modified: 2012-03-20
Hi.  I have an existing database.  Record is initiated and moves through a routing cycle an finally gets closed.  I created a chart to show aging of currently opened records (current date minus initiate date), sort of a paredo chart (attached)...   User wants to be able to use two separate date ranges so he can see aging for all current opened records (as is now) plus a 2nd set of bars on my chart that will show what it loked like 6 months ago.  That sounds impossible to me but thought I'd check with some of the pros here...    

Problem is I have no mecanism to take a snapshot of the data let's say 6 months ago or whenever...   I could give him current open vs. currently closed within range but no erally what he's asking for...   Any thoughts?  qryOpenCAPAAging1.pdf
0
Comment
Question by:valmatic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 40

Accepted Solution

by:
als315 earned 1000 total points
ID: 37728885
You can add dimension to your data source (CurrentData & 6MonthAgo with union query, for example) in and build you chart
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 37729277
Is 'closed' a yes/no field or a date field? If it's a date field you can query all the records that have an opened data < 6 months ago and a closed date > 6 months ago. This will give you the records that were open 6 months ago.

If you don't have the dat field yet, in Access 2010 you could add the field ClosedData to the database and use a data macro to fill this field with the current date when the Closed field is checked. This way you don't need to update any of your other code.
0
 
LVL 7

Author Comment

by:valmatic
ID: 37730028
Hi.  Closed field is a date field.  Data periods are variable.  I need to build a prompt for period range 1 and period range 2.  From what you're saying i should be able to build a query for period 1 which will pull up my current aging chart.  I can then build a second query to handle my other date period and through ALS' idea of a union query I should be able to pull it all together to build the chart but how can I show aging from period two yet still keep it within my aging ranges so the bars are side by side for comparison?  

I'm thinking while I type here..  So query 2 will pull in all data for range 2 and I can still use my initiate date to start the aging calc but what do I use as my end aging date?  Period 1(current) compares age of open record against current date (current Date - Initiate Date).   So let's say period 2 is 08/11/2012 - 02/11/2012.   What date replaces current date in this calc?   I'm really having a rough time wrapping my head around this request... thanks
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 22

Assisted Solution

by:Nico Bontenbal
Nico Bontenbal earned 1000 total points
ID: 37731034
You don't have two periods you have two 'snapshots'. One being the situation as it is today all records without an end data and the age being current date minus start date. The other one is a snapshot of (let's say) 6 months ago so 9/16/2011. That's all the records with a start date < 9/16/2011 and an end date > 9/16/2011 (the records that were open on 9/16/2011). Your age there is 9/16/2011 minus end date.

Draw a timeline on a paper from 1/1/2011 until today. Above this timeline draw a few lines with various start and end dates. Now draw a vertical line at today and at 9/16/2011. You'll see that all the horizontal lines that cross the 9/16/2011 are the ones that were open on 9/16/2011. All of these have a startdate < 9/16/2011 and an end date > 9/16/2011.
0
 
LVL 40

Expert Comment

by:als315
ID: 37732598
Look example. Form1 - Chart, Table1 - results of query for current period, Table2 - previous data.
DBchart.mdb
0
 
LVL 7

Author Comment

by:valmatic
ID: 37739499
Thanks for spelling that out Nicobo.  Like I said, Just couldn't make my brain wrap around that logic but makes total sense now.  

ALS, I am using two queries vs. 2 tables as in your example.  Looks like it should be the same  idea though.   Shouldn't matter if the fields in my queries that I'm getting my final age counts from are built thruogh the expression builder vs. actual table fields right?

One last question and I think I should be good.  This is a little off-topic but related so figured I'd see if either of you had a quick answer on this. If not I'll repost under a differet question..  

I need to build a calc to get my age count on the 2nd set of dates and so created a field in expression builder that would pull my snapshot date into my query but it's not working.
Field = Current Date: [Forms]![frmCallCAPAAging]![SnapDate].  This should pull the date entered on an entry form I set up.  Can either of you see anything wrong with the format of this date.  No errors - just blank cells in this column of data.  thanks
0
 
LVL 7

Author Comment

by:valmatic
ID: 37739537
I do see this in each of my cells - looks like chinese symbol and both form field and query field are formatted to short date.

¿¿
0
 
LVL 40

Expert Comment

by:als315
ID: 37740735
There are no difference between tables and queries for next query.
If your field is unbound, try to use
Current Date: Datevalue([Forms]![frmCallCAPAAging]![SnapDate])
or
Current Date: Format([Forms]![frmCallCAPAAging]![SnapDate],"mm/dd/yyyy")
if you like to have date as text
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 37741076
If you cursor is still in the field on the form the value is not yet updated. Could that be the problem. If you get Chinese characters your database might be corrupt. Try to repair it. If that doesn't help please post another question and attach (the part of ) the database that has the problem.
0
 
LVL 7

Author Comment

by:valmatic
ID: 37743200
Current Date: Datevalue([Forms]![frmCallCAPAAging]![SnapDate]) - worked perfectly.  thanks

I'm going to leave open for a bit longer.  Working on some other stuff before I get to attempt my union query.
0
 
LVL 7

Author Comment

by:valmatic
ID: 37744725
I created the union query.  That's some neat stuff.  I've never even heard of a Union query before this.  Anyway,  works perfectly.  Thanks for all of the input everyone...
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question