Count record each month till record closed?

[vapid2323]

Sorry I tried to think of a good subject

I have the query below and it works fine

SELECT Format(DateAdd("d",[AwareDate],30),"yyyy/mm") AS AwareDatePlus30, Sum(IIf(DateDiff("d",DateAdd("d",[AwareDate],30),[DateClosed])<=0,0,1)) AS [AwareDateOpen>30Count]
FROM qryCQA
GROUP BY Format(DateAdd("d",[AwareDate],30),"yyyy/mm");

It looks at any records that are open longer then 30 days for any givin month. If they are then I add them to a tally and we are all good.

The issue is that I need to tally the records EVERY month they are open over 30 days not just the first one.

So if record 1/1/2011 was open till 4/1/2011 I want to get a count, one for each month.

2/1/2011 = 1
3/1/2011 = 1
4/1/2011 = 1

Currently it will only count one time then it will stop.

Any ideas?

 

[MarkK]

You are finding the difference right, between two numbers, in this case dates-- which are stored as numbers--so you use some form of subtraction. Maybe...

[COLOR="Green"]'gives you total days[/COLOR]
DateClosed - AwareDate
[COLOR="Green"]'gives you how many 30 day chunks exist in 'total days'[/COLOR]
(DateClosed - AwareDate) / 30

Or use the DateDiff() function. But it's just a subtraction. Nothing fancy.
Mark

 

[vapid2323]

You are finding the difference right, between two numbers, in this case dates-- which are stored as numbers--so you use some form of subtraction. Maybe...

[COLOR=green]'gives you total days[/COLOR]
DateClosed - AwareDate
[COLOR=green]'gives you how many 30 day chunks exist in 'total days'[/COLOR]
(DateClosed - AwareDate) / 30

Or use the DateDiff() function. But it's just a subtraction. Nothing fancy.
Mark

Ok I see what your doing here, and I think its the direction I need to take.

Now the math is much closer but we still have an issue, if I do it this way it will count up the value and add it to the first month it was over 30 days. I need it to spread that out so its one for each month.

Using hte example from my first post the new math will give me:

2/1/2011 = 3.5555

I still need:
2/1/2011 = 1
3/1/2011 = 1
4/1/2011 = 1

Now another issue is that I need to take into account the REAL days in a month, I am sure this might not be as easy.

This has to be very exact as its going to be FDA approved etc.

 

[MarkK]

If you end up with a decimal maybe you need to use the Fix() function which truncates, as opposed to rounds, the number.
And how do you distinguish REAL days from other, presumably UNREAL, days.

...take into account the REAL days in a month...

Cheers,
Mark

 

[vapid2323]

If you end up with a decimal maybe you need to use the Fix() function which truncates, as opposed to rounds, the number.
And how do you distinguish REAL days from other, presumably UNREAL, days.

Cheers,
Mark

lol, good point.

I mean that I need to account for the days in the month.

June = 30 days
July = 31 days
August = 31 days

 

[vapid2323]

I am getting the idea this may not be possible without VBA. Any thoughts?

 

[MarkK]

If you have a snag in finding a solution here I don't know what it is. You suggested the some days are real and others perhaps not, and I joked about it, but what I mean is that I don't understand what's not working about subtracting start from end and lopping off the decimal. What is wrong with that as a solution?
Cheers,
Mark

 

[vapid2323]

If you have a snag in finding a solution here I don't know what it is. You suggested the some days are real and others perhaps not, and I joked about it, but what I mean is that I don't understand what's not working about subtracting start from end and lopping off the decimal. What is wrong with that as a solution?
Cheers,
Mark

I dont understand how to get the query to count the values for each month. Currently it will just pile them all onto the first month, I need to spread out the values accross all the months.

So if I get a value of 2.55 I cant group all those on Sept, I need to spread the values across Sept and Dec etc.

 

[MarkK]

So is there a record for each month? Can you describe the data structure into which you need to put this data? I would expect that your object has a start and an end date, and that's it. Then, if you need to display the intervening time you do subtraction.
But what is the exact structure of your data that you need to distribute these ones like this? And it suggests a design problem with the data structures, because this shouldn't be very hard.

 

[vapid2323]

So is there a record for each month? Can you describe the data structure into which you need to put this data? I would expect that your object has a start and an end date, and that's it. Then, if you need to display the intervening time you do subtraction.
But what is the exact structure of your data that you need to distribute these ones like this? And it suggests a design problem with the data structures, because this shouldn't be very hard.

Ok it might be VERY easy and its my ignorance getting in the way, but we will see.

I have two dates:

• AwareDate
• DateClosed

I need to create a report that shows, by month the following:

• Received - A count of the inital date record was recived (this is just the AwareDate's month)
• Open - A count for each month a record is open
• Closed - A count of the closed date
• Open>30Days - A count for each month a record is open over 30 days.

So I hope that makes it a bit more clear, I can come up with Received and Closed metrics simple enough because they only involve one month.

Its the ongoin tally of the Open and Open>30Days that I cant wrap my head around.

 

[vapid2323]

Monday bump, I do have to ask, if this is truly easy can someone give me a little hint this is killing me and I need to find another way to calculate it if SQL alone cant do it.

 

[MarkK]

I don't understand this...

I need to create a report that shows, by month the following:
Received - A count of the inital date record was recived (this is just the AwareDate's month)
Open - A count for each month a record is open
Closed - A count of the closed date
Open>30Days - A count for each month a record is open over 30 days.

This is by month? But if you summarize by month, how many months the record is open is one. Received and closed are not monthly. Open>30, monthly, can never be true in February. I don't understand this.
Mark

 

[vapid2323]

I don't understand this...

This is by month? But if you summarize by month, how many months the record is open is one. Received and closed are not monthly. Open>30, monthly, can never be true in February. I don't understand this.
Mark

I have made some sample data of what the end query will look like. I expect this to take more then on query to finish my main focus is understanding how to create the Open and Open>30 Days

The sample will hopefully help you understand what I am trying to do.

 

[MarkK]

Oh, I see.
One thing you could do is create a field in a query that exposes the year and month, so something like...

YearAndMonth: CInt(Year(AwareDate) & Month(AwareDate))

... and GroupBy that field. Then you can run domain aggregate functions, like Count(), on records that occur in 1108, say.
Also, look at grouping, sorting and totalling in a report. You can group by month--and any other time period--and using a section header or footer you can summarize any of your detail records that fall in that group.
Does that make sense?
Mark

 

[vapid2323]

Oh, I see.
One thing you could do is create a field in a query that exposes the year and month, so something like...

YearAndMonth: CInt(Year(AwareDate) & Month(AwareDate))

... and GroupBy that field. Then you can run domain aggregate functions, like Count(), on records that occur in 1108, say.
Also, look at grouping, sorting and totalling in a report. You can group by month--and any other time period--and using a section header or footer you can summarize any of your detail records that fall in that group.
Does that make sense?
Mark

When I read that I almost feel like we have come full circle , My first sample query already groups by month. And I allready have done the counts on those records. That works just fine when the record only shows for one month (Like the recived and closed stats) But that still will not provide me with the open or Open>30 Days stats.

Let see if I can claify:

With my current math a record that opens on 1/1/2011 and closed on 6/1/2011 would show up like this in a query.

1/1/2011 = 1
2/1/2011 = 0
3/1/2011 = 0
4/1/2011 = 0
5/1/2011 = 0
6/1/2011 = 0

Because there is no date or value to Count() inbetween the open and closed dates.

What I need it to show is:

1/1/2011 = 1
2/1/2011 = 1
3/1/2011 = 1
4/1/2011 = 1
5/1/2011 = 1
6/1/2011 = 1

So I can get a Count() of 6

 

[MarkK]

Sorry man, but I don't know what else to tell you. If you want to post a database I'll gladly take a look at this further, but I agree, we're spinning our wheels here a little.
Cheers,

 

[G37Sam]

What I would do is, use VBA code to do the count for each month and store that value, then use SQL to generate the report

I understand that might introduce a little data duplication, but is a much easier route

 

[vapid2323]

Sorry man, but I don't know what else to tell you. If you want to post a database I'll gladly take a look at this further, but I agree, we're spinning our wheels here a little.
Cheers,

I have started a thread over in the VBA section to see if they might have a solution. Also I cant really send my database over its on a SQL server and all the code in it is restricted by my company.

Not sure it would help you anyhow as the sample data I listed in the last post is the same as whats in the table

Anyhow the VBA post can be found at: http://www.access-programmers.co.uk/forums/showthread.php?p=1092225#post1092225