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?