Using Date Table to Track Historical Data

[kentwood]

I am re-designing a database for 2008 and trying to eliminate my Make Table Queries as I have found them to be somewhat consistant over the last year, particularily when the users do not open the database on a given date. It seems there should be a simple way to accomplish what I want but I am struggling and need some assistance.

I have attached a sample of a few tables from my database, Open Cases, Closed Cases, and Date Today. The Open and Closed tables change daily due to a Corporate download and contain several date fields which have different meanings. As new cases are opened, they go on the open table, and as an open case is closed, it moves to the closed table. The tbl_Date Today is pre-populated with dates of working days only. I have a query called "Count Of Shelf Comb" that counts the number of open cases as of today, which in truth is for all activity through the previous business day. What I want is to have a query that will show each date on the tbl_date today as well has what the total count of open cases was for that date......a permanent history of the amounts.

How can I accomplish this without using a "Make Table Query".

 

[CEH]

Well, sometimes hard to imagine everything... But it sounds like you are tracking cases by date and if opened are closed... So why 2 tables? I would have fields instead of another table to track Open/Closed, DateOpened and DateClosed. With those 3 you should be able to do queries for any info needed.

 

[kentwood]

The open table and closed table are part of the Corporate database that I use and I am stuck with them. My database is an adhoc off the main dbase. In my question I am only working from the open table based on criteria. What meets criteria today and is reflected in the sum total may not meet the same criteria tomorrow and therefore not be included in the total. Based on the SQL of my query below, I get a total as of the day I run it. Next day is another total for that day but the previous days total is gone. There is so specific date field that I can use to line up with the date today field of that table. I want to retain each days total as part of the history which is why I figured I need to use the tbl_Date Today, which lists all work days.

SELECT Count(*) AS [CountOfDesk Cd], Sum(IIf([Resp Cd] Like "*[02468]*",1,0)) AS Facility, Sum(IIf([Resp Cd] Like "*[13579]*",1,0)) AS Profess
FROM tblOpenCases
WHERE (((tblOpenCases.[Desk Cd])="00"));

I previously began my SELECT statement with the following SELECT IIf(Weekday(Date())=2,Date()-3,Date()-1) AS [Date]. This however, does not work when there are holidays in the week . This was meant to respond to non working Saturdays and Sundays and ensure that when the data was pulled on Monday, it would have Fridays date, and if pulled Tuesday through Friday it would have the previous days date. Works great as a make table query, and gave me a nice history, but I want to avoid the make table at startup. It is a real problem when the users do not open the dbase on any particular day.