Solved

Create chart of record aging current and any historical time range

Posted on 2012-03-15
11
270 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now