Maintain history of daily numbers based on expression 1

[kentwoodjean]

Not sure my description accurately says what I am trying to do. Have a table (tblLastUpdated) that contains information about contracts and is updated daily based on a auto download. The primary date field is "recvdDt" that describes when actual case was received, "Date1" that tells me when the department began working on the contract, and "CloseDt" that tells me when the case was removed from open inventory. There is no reference to the current date. I need to record the daily inventory, or total number of "Open" cases daily by date. This table retains the entire history of activity regarding each contract. Using this table I am able to pull the current inventory (for today) but what I don't know how to do is get my query to pull the current inventory figure today as well as the Inventory # from previous dates. Is there a way that I can do this?
Below is the sql for my inventory query. Also, I pull my count by RespCd.

SELECT Count(tblLastUpdated.[Resp Cd]) AS [CountOfResp Cd]
FROM tblLastUpdated
WHERE (((tblLastUpdated.[Resp Cd])="790" Or (tblLastUpdated.[Resp Cd])="792" Or (tblLastUpdated.[Resp Cd])="798") AND ((tblLastUpdated.[Closed Dt]) Is Null));

 

[GingerR]

So for each calendar date, I counted a case as "OPEN" if it has recvdDt <= the calendar date, but CloseDt is null or >= the calendar date.

I have a table called tblWeekEnding which I use for a diff purpose, but it has a field with every date in it, i.e.

1/1/04
1/2/04
1/3/04

etc which I use for a multitude of purposes. You can create it easily in Excel and import it in. This is what I would use to determine your list of calendar dates, i.e. list all of the dates for example for 2004 and how many cases were open each of those dates.

So this table name = tblWeekending
Field = CalendarDate

I made a new table per your post called CaseDates with the fields as you described.

I wrote this query:

SELECT tblWeekEnding.CalendarDate, Sum(IIf([RecvdDate]<=[CalendarDate] And ([CloseDt] Is Null Or [CloseDt]>[CalendarDate]),1,0)) AS [Open]
FROM tblWeekEnding, CaseDates
GROUP BY tblWeekEnding.CalendarDate
HAVING (((tblWeekEnding.CalendarDate) Between #1/1/2004# And #12/31/2004#));

my data was this:

CaseID	RecvdDate	Date1	CloseDt
1	1/1/2004	1/2/2004	2/8/2004
2	1/2/2004	1/4/2004	2/15/2004
3	1/3/2004	1/5/2004	3/1/2004
4	1/2/2004	1/4/2004	3/8/2004

and my result is this:

CalendarDate	Open
1/1/2004	1
1/2/2004	3
1/3/2004	4
1/4/2004	4
1/5/2004	4
1/6/2004	4
1/7/2004	4
1/8/2004	4
1/9/2004	4
1/10/2004	4
1/11/2004	4
1/12/2004	4
1/13/2004	4
1/14/2004	4
1/15/2004	4
1/16/2004	4
1/17/2004	4
1/18/2004	4
1/19/2004	4
1/20/2004	4
1/21/2004	4
1/22/2004	4
1/23/2004	4
1/24/2004	4
1/25/2004	4
1/26/2004	4
1/27/2004	4
1/28/2004	4
1/29/2004	4
1/30/2004	4
1/31/2004	4
2/1/2004	4
2/2/2004	4
2/3/2004	4
2/4/2004	4
2/5/2004	4
2/6/2004	4
2/7/2004	4
2/8/2004	3
2/9/2004	3
2/10/2004	3
2/11/2004	3
2/12/2004	3
2/13/2004	3
2/14/2004	3
2/15/2004	2
2/16/2004	2
2/17/2004	2
2/18/2004	2
2/19/2004	2
2/20/2004	2
2/21/2004	2
2/22/2004	2
2/23/2004	2
2/24/2004	2
2/25/2004	2
2/26/2004	2
2/27/2004	2
2/28/2004	2
2/29/2004	2
3/1/2004	1
3/2/2004	1
3/3/2004	1
3/4/2004	1
3/5/2004	1
3/6/2004	1
3/7/2004	1
3/8/2004	0
3/9/2004	0
3/10/2004	0
3/11/2004	0
3/12/2004	0
3/13/2004	0
3/14/2004	0
3/15/2004	0
3/16/2004	0

In my query i limited the date range to 1/1/04-12/31/04 so obviously you can change that.

Hope this helps point you in the right direction.

g

 

[kentwoodjean]

Thanks for the excellent direction. It is quate a different apprach than I would have known to go. I will work on this tomorrow and merge it into my databas. I will let you know how this works out.