Need advice on how to format query 1

[houstonbill]

I am looking for advice on the best way to get a result. I have 2 tables that are automatically updated daily as a result of a mainframe download. One is an “tblOpenCases” and the other is “tlbClosedCases”. Obviously these will change daily in that what is open today may or may not be closed and once that happens, it shows up on the closed table. For reporting purposes, I have to record and retain by date what was opened and closed on each day. Even though the Open table (in particular) will drop its cases once they are closed, I do not want the report to change the totals originally recorded for the report. Currently, I have a Make Table Query that pulls the current date totals from the Open and Closed tables combined which provides a new table by date and a permanent record. This, along with a few other make table queries, is activated at the time of startup.

Because there is a sequence of events that take place each day (different database activates download of data, then my adhoc database is opened) I find that sometimes the made table functions drop a date on occasion and I have to recover data.

I would like to know if there is another way that I can obtain and retain the # of open cases by date in a on-going list?

Below is an example of some of the fields that are used, with date1 being the primary date as to when the case is opened in the system?

RecDt FirstName LastName CNum Date1 Date1Status Date2 Date2Status

 

[GingerR]

Hi--First a question:

by this

I have to record and retain by date what was opened and closed on each day

do you mean "How many cases have an OPEN DATE of each day, and how many cases have a CLOSE DATE of each day? Or do you mean "How many cases were still open" each day and "How many total cases were closed that day"?

Which below is what you want to show?

DATA
Cnum OpenDate ClosedDate
1 1/1/07 1/8/07
2 1/2/07
3 1/4/07 1/7/07

OUTPUT A?
Date OPEN CLOSED
1/1/07 1 0
1/2/07 1 0
1/3/07 0 0
1/4/07 1 0
1/5/07 0 0
1/6/07 0 0
1/7/07 0 1
1/8/07 0 1

OR OUTPUT B (running total)?
Date OPEN CLOSED
1/1/07 1 0
1/2/07 2 0
1/3/07 2 0
1/4/07 3 0
1/5/07 3 0
1/6/07 3 0
1/7/07 2 1
1/8/07 1 2

More Questions: So it sounds like a case is EITHER in the Open or Closed table, right? So a case cannot be in both? And I assume that there is a CLOSED DATE some place? And I assume that the tables hold all the cases that ever existed and that none are removed from the CLOSED table at any point?

I think then you do not need to "store" the calculations, but can calculate them on the fly.

You can UNION the two tables together, to get one list:

CNum OpenDate ClosedDate
1 1/1/07 1/8/07
2 1/2/07
3 1/4/07 1/7/07

Then depending on the output you want from above (A or B)you can write a query that shows what you want. You might need to have an additional table that has all calendar dates in it (so you can show all calendar days regardless of if there is any case activity). Anyhow, answer the above questions, write a UNION query to put your tables together, create a CalendarDays table, and try to make a query between the table and the Union query to get what you want. Let us know if you need more help.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[houstonbill]

Ginger, what I need is how many cases have an OPEN DATE on each day, and how many cases have a CLOSE DATE on each day which I believe you have as output A. Once that # is set for the day, I do not want it to change. A case cannot be on both tables.
I already have a union query to bring both tables together, and I also have a "DateToday" table. As I think this through as well as based on your feedback, if I have a query like the SQL below which utilizes the date today table as well as 1 query off the open table and another off the closed table, it should accomplish what I want....I think. I set up some test queries and I will check it tomorrow to see if it maintains the previous days data. I didn't use the Union Query because the daily numbers keep moving from open to closed.
Does this appear to be the correct direction?



SELECT tbl_DateToday.DateToday, [Count Of Pending Prof].Prof, [Count Of Fac Pending].Fac
FROM [Count Of Pending Prof] RIGHT JOIN ([Count Of Fac Pending] RIGHT JOIN tbl_DateToday ON [Count Of Fac Pending].FromDate = tbl_DateToday.DateToday) ON [Count Of Pending Prof].FromDate = tbl_DateToday.DateToday;

 

[GingerR]

I don't know-Sorry I'm not good at reading SQL and knowing what it's doing. I know what you mean tho--

I suppose what I'd have in a query design grid is the table of all dates in the universe beginning with whenever you need it to begin with, and ending far into the future. You can make a criteria of <Date() to only report until 'yesterday'.

Then two queries, one made on each of your tables that groups by date and counts the items.

These three things (table and two queries) are in this last query, with the arrows pointing from the date in the big table of all dates to the date fields in the two counting queries.

------------------------------
Table: CalendarDates
Field: CalendarDate
Holds all dates
------------------------------
Query: CasesOpenCount {Totals query}

Date OpenCases
{GroupBy} {Count}
1/1/07 3
1/2/07 1
1/5/07 2
------------------------------
Query: CasesClosedCount
{similar data to other query above)
------------------------------

So here is my final query:

SELECT CalendarDates.CalendarDate, CasesOpenCount.OpenCases, CasesClosedCount.ClosedCount
FROM (CalendarDates LEFT JOIN CasesClosedCount ON CalendarDates.CalendarDate = CasesClosedCount.ClosedDate) LEFT JOIN CasesOpenCount ON CalendarDates.CalendarDate = CasesOpenCount.RecdDate;

Sorry it's not using your table/field names. Hope you can sort it out. If not, tell me your table/field names and I'll do it again.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[houstonbill]

OK, so I tried to reconfigure this and cannot make it work. Here are the two queries & table that need to come together. Should maybe we be usig a Union Query? Just asking.

Count Of Fac Pending
Fac
From Date
Count of Pending Prof
Prof
From Date
tbl_DateToday
DateToday

 

[GingerR]

Hi--what data is in DateToday? Just a single date (today's)? or lots of dates?

I also suggest not using spaces in any object or field names. Not illegal but more work, and can cause problems some day.

Anyhow, I'll just get my query and sub in your object names, no guarentee it'll work cause I don't know what data your queries have (I assume it's dates and numbers):

SELECT tbl_DateToday.DateToday, [Count Of Fac Pending].Fac, [Count of Pending Prof].Prof FROM (tbl_DateToday LEFT JOIN [Count of Pending Prof] ON tbl_DateToday.DateToday = [Count of Pending Prof].[From Date]) LEFT JOIN [Count Of Fac Pending] ON tbl_DateToday.DateToday = [Count Of Fac Pending].[From Date];

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[houstonbill]

The Date today has lots of dates, basically it is as you described in your calendar table. tbl_DateToday is a make table query by itself. Each day when the database is opened it populates with the current date, adding to the list of previous dates. This table is used with multiple queries to bring all data together in differnt ways not only for the current date but also provides historical data.

Your sample above pulls this particular data perfectly, but will it populate tomorrow with the new information and retain what is showing for today?

 

[GingerR]

It will show data for every day that is listed in your tbl_DateToday. It will always show how many items are opened each day, and how many are closed. Seems to me that this will be fine, since a case is listed only one time and it has one OPEN and one CLOSED date.

I suppose it could change if someone today puts a CLOSED date of 12/1/07 onto a case, then there will be one more item CLOSED on 12/1/07 than showed yesterday. But that's how many were closed on that day, right? How did you accomdate this using your "old" way? Or does that never happen?

Check it out tomorrow and see if it does what you want. Let us know if you need more help.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[GingerR]

Hi--driving to work just now I realized that this will not work because you are not counting the closed cases by when they were OPENED. So I go back to my original suggestion: UNION the two tables together. If they have all the same fields, it's as easy as

Select * from tblOpenCases UNION Select * from tblClosedCases;

Then on your two counting queries, base them on this union query instead of on the two tables. Then it will 'preserve history'.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[houstonbill]

Got it! thanks so much for your help. I would have responded sooner but had some OP surgery on 12/21 and I am just now getting back to some of my projects.

You have been a big help Ginger. Thanks again.

 

[GingerR]

No problem--have a good holiday!

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html