calling a previous month data

[Alex Martinez]

Hello,

I am using Access 2002 and I need a little help in my query. I have a file
call Claims, which has the following fields; month end, completed date,
assigned To. I work for an insurance company when claims come in they are
assigned to a claims examiner and later marked a completed date. At the end
of the month I will query for the completed date (I use "Is Not Null") for
example the month of September 30, 2005. Then on a separtate query I query
for records that has no completed date. We call this carry over. I only
use month end date such as 1/31/2005, 2/28/2005, 3/31/2005, 4/30/2005 etc.
Now here what I need at the end of the month for example September 30, 2005
I like to get the number of August carry overs for October 31,2005 I like to
get the number of Sepetember 30, 2005 carryover. How can I make this type of
query? Also, is it possible I can get the total count of completed claims
and the number of carry overs in one query? Any tips will be appreciated.
Thank you.

 

[David S via AccessMonster.com]

Hi Alex,

To be able to get what you're after, you need some way of telling when a
Claim was started - this is because to find out what was carried over at some
point in the past, you need to count the number of claims with ([Start Date]
< [Target Date]) AND (([End Date] > [Target Date]) OR ([End Date] Is Null))

In this case, you want to make the [Target Date] the end of the previous
month - I'd say you could do this with a calculation like DateSerial (Year(
[Enter Month End Date]), Month([Enter Month End Date], 0).

So your for queries are going to look something like:
CarryOverCount:
SELECT [Enter Month End Date] AS MonthEndDate, Count(Claims.[Start Date]) AS
[CountOfStart Date]
FROM Claims
WHERE (((Claims.[Completed Date]) Is Null))
GROUP BY [Enter Month End Date];

LastMonthEnd:
SELECT CarryOver.MonthEndDate, DateSerial(Year([MonthEndDate]),Month(
[MonthEndDate]),0) AS LastMonthEnd
FROM CarryOver;

CarryOverCountLastMonth:
SELECT LastMonthEnd.MonthEndDate, Count(Claims.[Start Date]) AS [CountOfStart
Date]
FROM LastMonthEnd, Claims
WHERE (((Claims.[Start Date])<[LastMonthEnd]) AND ((Claims.[Completed Date])>
[LastMonthEnd] Or (Claims.[Completed Date]) Is Null))
GROUP BY LastMonthEnd.MonthEndDate;

Bringing it all together:
MonthEndQuery:
SELECT CarryOverCount.MonthEndDate, CarryOverCount.[CountOfStart Date] AS
CarryOversThisMonth, CarryOverCountLastMonth.[CountOfStart Date] AS
CarryOversLastMonth
FROM CarryOverCount, CarryOverCountLastMonth;

Note that we don't have to actually join these tables, because there's only
one record in each of them. Using the same approach, we could add the total
count of complete claims:

CompletedClaimCount:
SELECT Count(Claims.[Completed Date]) AS [CountOfCompleted Date]
FROM Claims
WHERE (((Claims.[Completed Date]) Is Not Null));

So MonthEndQuery becomes:
SELECT CarryOverCount.MonthEndDate, CarryOverCount.[CountOfStart Date] AS
CarryOversThisMonth, CarryOverCountLastMonth.[CountOfStart Date] AS
CarryOversLastMonth, CompletedClaimCount.[CountOfCompleted Date] AS
TotalCompletedClaims
FROM CarryOverCount, CarryOverCountLastMonth, CompletedClaimCount;