Create chart of record aging current and any historical time range

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
LVL 7
valmaticAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
You can add dimension to your data source (CurrentData & 6MonthAgo with union query, for example) in and build you chart
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nico BontenbalCommented:
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
valmaticAuthor Commented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Nico BontenbalCommented:
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
als315Commented:
Look example. Form1 - Chart, Table1 - results of query for current period, Table2 - previous data.
DBchart.mdb
0
valmaticAuthor Commented:
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
valmaticAuthor Commented:
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
als315Commented:
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
Nico BontenbalCommented:
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
valmaticAuthor Commented:
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
valmaticAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.