Maybe I'm over complicating it or maybe I should create a macro that inserts a temp table to do is (although then I couldn't have a historic data access to this particular report which would be annoying but still acceptable)
Probably you will give me some ideas that I didn't think of.
I have an Access database with some links to tables inside a Oracle database. These tables keep information about all the calls received and sent by a call center which provides support to certain applications and softwares (some as well knows as Excel, some are specific of this company) for a particular company.
One table has information about the application itself
SYM (which is the application accronym)
ASSIGNEE (person responsible for providing specific support)
Another table has information about the call itself and the query/problem related to that call
and much more
The relationship between this two tables is PERSID ->CATEGORY
My report needs to count how many calls (queries/problems reported) in a certain period of time and compare that data to the data of previous years but only for the 25 applications with the highest number of call/queries during that period on each year.
Let me put an example and explain how it goes. Let's say that I need to get the report for the month of November which just finished.
Step 1 - From the Oracle database, I would extract all the information from Nov 2013 and then from Nov 2012 and Nov 2011 (two previous years). This should remove any duplicated entry among others. That is, it should exclude any record with a CLASS="MASS", TYPE="PRB" or CAUSE="DUPL"
Step 2 -Then, I would get the names of the 25 applications which got the highest number of calls in Nov 2013, the 25 applications with the highest number of calls in Nov 2012 and same for 2011 (three different queries)
For steps 1 and 2, I use 3 queries in Access. The three of them are equal except that each one gets the data from one year
SELECT TOP 25 CALL_REQ.CATEGORY, IIf([SYM]="W2k","WINDOWS",[SYM]) AS Aplic, Count(CALL_REQ.REF_NUM) AS Total, [Año] AS [Year]
FROM PROB_CTG RIGHT JOIN CALL_REQ ON PROB_CTG.PERSID = CALL_REQ.CATEGORY
WHERE (((IIf([OPEN_DATE] Is Null,"",CvrtFromUnixTime([OPEN_DATE]))) Like "/" & [Month] & "/" & [Año] & "") AND ((CALL_REQ.CAUSA)<>"DUPL" Or (CALL_REQ.CAUSA) Is Null) AND ((CALL_REQ.tipo)<>"PRB") AND ((CALL_REQ.clase)<>"MASIVA"))
GROUP BY CALL_REQ.CATEGORY, IIf([SYM]="W2k","WINDOWS",[SYM]), [Year]
ORDER BY Count(CALL_REQ.REF_NUM) DESC;
Now, I have 3 lists with 25 names of applications on them. Some of these applications will be the same in all three years. Some applications will be in just one list.
Step 3 - Then I have to create one list with the name of all the applications from all the three lists, and the data for the month of November for each of those.
The result of these should look something like
2011 2012 2013
App1 150 200 125
App2 25 125 152
App3 275 351 452
App35 125 355 3
This table should be ordered DESC from year 2013
This is my problem. To do this I have used UNION query which, indeed, gets all the application listed and the data if already exists for the three years.
The problem arises when App5 is on the top 25 for year 2012 (as an example) but it wasn't on the top 25 in 2011 or 2012. The UNION will not provide that.
So if I do the UNION to get the list of applications, then I have to do another query to get the data for all those applications and for the three years.
I tried to do this query using an InSelect as a condition for the field SYM (that is, only include the applications listed in the UNION query) but the computer simply dies.
Each of the first queries need at least 1 min to run.
Do you think that there is another way, a better way to get what I want?
So the problem is the first three? Probably, because CALL_REQ contains a huge amount of rows? In combination with some data manipulation functions in the SELECT, WHERE and GROUP BY?
Maybe you could try to do a first extraction of the rows from the three year-months needed without doing any joining and data manipulation?
WHERE OPEN_DATE > '2011-11-01'
AND OPEN_DATE < '2011-11-30'
AND CAUSA <> "DUPL"
AND tipo <>"PRB"
AND clase <> "MASIVA"
Of course, you'll have to add all those parenthesis Bill likes so much, and maybe '2011-11-01' and '2011-11-30' must be transformed in unixtime (or whatever).
Once you have this table, then you can join it to the applications table without the huge mass of rows you're not interested in, and without having to operate on the date.
Well, it is true that there are some ordering and sorting and that there's a IF there (basically, because each call is related to one application and where it says W2K needs to be substituted by WINDOWS)
And, yes, there are lots of info in that database as this call center provides technical support to some 7,000 users
And, definately, dates need to be converted from UNIX time so there's no way to get rid off the convertion funtion either.
I did wanted to create a view that would be updated each month so I could have the data I needed and work only with that but I only have reading permissions in the Oracle database.
Creating a temp table in Access importing this data could be a solution but it is a "problem" when you want to compare November report with October's as the temp table would have to be re-filled with the appropiate data each time you request for a report.
If you don't want to try out my solution, at least try to put the date conversion on the 'fixed' values, not on the date column.