Solved

Create chart of record aging current and any historical time range

Posted on 2012-03-15
11
272 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
  • 5
  • 3
  • 3
11 Comments
 
LVL 39

Accepted Solution

by:
als315 earned 250 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 22

Assisted Solution

by:Nico Bontenbal
Nico Bontenbal earned 250 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 39

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 39

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Search for text in a .txt file 14 46
Exporting Access Tables as CSV 3 24
Batch Export Reports (with multiple parameters) As PDF 2 24
MS Access Delete All Excel sheets 1 10
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

821 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