IIF Iserror MS Access Queries

<<Please Help... I'm going crazy>>

 Well first thing is you should not be using DSum() in a query as it will perform very poorly.  The query parser cannot optimize the statement.  The Domain function were meant to be used in places where SQL can't; queries are not that place.

Instead, do the following:

1. Construct a query that returns the sum of what you want.

2. Save it.

3. In the query where you need the sum(), add the first query as a "table".  

4. Peform a join on the appropriate fields.

5. Double click on the join and select option 2 or 3 as appropriate (you want all records returned from your second query).

6. Drag down the sum field into the grid.

7. Surround it with NZ(<field>,0)

 Which will return a 0 if no records found.

Jim.

Hi Jim,

Thanks for your response!  :)  But I'm attempting to get a Running Total which is why the DSum function is being used.

Currently my query returns a running total for the number of NEW Cases being worked by Rep on a specified day.

If I remove the DSum function how would I get the Running Total in a query ... Every where I search pointed me in the direction of DSum.

When I attempted your recommendation it just gave me a sum for each individual day as opposed to a running sum.

Thanks again !  :)

OK, you want something slightly different then.  Kind of like the Dsum(), but it's an SQL statement inside your first, so it's called a subselect.  In general, it looks like this:

SELECT T1.field,   (SELECT Sum(valuefield) AS Total   FROM datasource   WHERE datasource.sortfield <= T1.sortfield) AS Total FROM datasource AS T1

So your looking at something like this:

SELECT T1.[Agent ID], T1.[Last Touch Date], (SELECT Sum([New Cases]) As Total FROM QRY_Daily Productivity_NEWOverall WHERE QRY_Daily Productivity_NEWOverall.[Agent ID] = [Agent ID]  AND QRY_Daily Productivity_NEWOverall.[Last Touch Date] <= T1.[Last Touch Date]) As Total FROM QRY_Daily Productivity_NEWOverall AS T1 Order By T1.[Agent ID]

  'T1' is just an alias for the table name so it does't get confused over what is getting refered to where.

Jim.

Hi Jim...

Would your SQL code work with using Queries.  My DSum was in a sub-Query...  After correcting some relationship issues I had to re-write my code..

Also will your version eliminate my initial issue which was to remove the #Error when there isn't any New cases for the date?  Is it possible to just have the last date's running total result populate the field?  

Meaning if there were Follow Ups and New Cases on 2/5 but only Follow Ups (without any New Cases) on 2/6 is it possible to have the Running Total result from 2/5 show in the New cases field for 2/6?


Hope that's not confusing?

The SQL version of my sub-query reads:

SELECT [QRY_Daily Productivity_FU_2].[Agent ID], [QRY_Daily Productivity_FU_2].[Last Touch Date], DSum("[# of FU Cases]","QRY_Daily Productivity_FU_2","[QRY_Daily Productivity_FU_2]![Agent ID] =" & [QRY_Daily Productivity_FU_2]![Agent ID] & " And [QRY_Daily Productivity_FU_2]![Last Touch Date]<=#" & [QRY_Daily Productivity_FU_2]![Last Touch Date] & "#") AS [FU Total], DSum("[NEW Cases]","QRY_Daily Productivity_NEW_2","[QRY_Daily Productivity_NEW_2]![Agent ID] =" & [QRY_Daily Productivity_NEW_2]![Agent ID] & " And [QRY_Daily Productivity_NEW_2]![Date Case was Assigned]<=#" & [QRY_Daily Productivity_NEW_2]![Date Case was Assigned] & "#") AS [NEW Total]
FROM [QRY_Daily Productivity_FU_2] LEFT JOIN [QRY_Daily Productivity_NEW_2] ON ([QRY_Daily Productivity_FU_2].[Last Touch Date] = [QRY_Daily Productivity_NEW_2].[Date Case was Assigned]) AND ([QRY_Daily Productivity_FU_2].[Agent ID] = [QRY_Daily Productivity_NEW_2].[Agent ID])
GROUP BY [QRY_Daily Productivity_FU_2].[Agent ID], [QRY_Daily Productivity_FU_2].[Last Touch Date], [QRY_Daily Productivity_NEW_2].[NEW Cases], [QRY_Daily Productivity_NEW_2].[Date Case was Assigned], [QRY_Daily Productivity_FU_2].[# of FU Cases], [QRY_Daily Productivity_NEW_2].[Agent ID]
ORDER BY [QRY_Daily Productivity_FU_2].[Agent ID], [QRY_Daily Productivity_FU_2].[Last Touch Date], [QRY_Daily Productivity_NEW_2].[Date Case was Assigned];

I attempted to use your code but was getting a 'Missing ")" error and couldn't figure out where.  And I didn't have a table to replace T1 with.

Image of Datasheet View where you can see the #Error in fields where New Cases have no data for that Date:
 
Image of Design View where the 2 queries I'm using are shown:


Tonya~

Hi again Jim,

I tried your recommendation again to see if it would give a running total without the error but it seems to be summing all the Cases and giving everyone the same result.

SELECT [QRY_Daily Productivity_FU_2].[Agent ID], [QRY_Daily Productivity_FU_2].[Last Touch Date], (SELECT Sum([# of FU Cases]) As FUTotal
FROM [QRY_Daily Productivity_FU_2]
WHERE [QRY_Daily Productivity_FU_2].[Agent ID] = [QRY_Daily Productivity_FU_2].[Agent ID]  AND [QRY_Daily Productivity_FU_2]![Last Touch Date] <= [QRY_Daily Productivity_FU_2]![Last Touch Date]) AS Total
FROM [QRY_Daily Productivity_FU_2]
GROUP BY [QRY_Daily Productivity_FU_2].[Agent ID], [QRY_Daily Productivity_FU_2].[Last Touch Date], [QRY_Daily Productivity_FU_2].[Last Touch Date]
ORDER BY [QRY_Daily Productivity_FU_2].[Agent ID], [QRY_Daily Productivity_FU_2].[Last Touch Date], [QRY_Daily Productivity_FU_2].[Last Touch Date];



And here's the Design view

<<I tried your recommendation again to see if it would give a running total without the error but it seems to be summing all the Cases and giving everyone the same result.>>

 So what your looking for in the output is one row per agent per last touch date, with a running sum of cases for all dates prior to that and including the the current row?

Jim.

Jim,

Yes, if that's possible I would like to have one output row per agent with a running sum for Follow Up cases and returning the running sum of any New cases on that same date.  

Using "DSum" is producing what I want as the image shows below:

but my problem comes in when there aren't any New cases assigned on a date that the agent had follow ups.  Instead of bringing down the latest running sum result it gives: #Error.  

There will be days an agent has follow ups but no new cases assigned.  And since this is the case Access is producing the #Error.  I would like the last running sum result for New cases to show instead of the #Error.  Like in the image above, for 2/8/13 I would like to show the 46 from the previous date's running sum and for the 2nd repeat the running sum result: 14 from 2/1/13.

Is this possible?

Thanks

When you have a left outer join, I thought a LEFT OUTER JOIN will return all records from the left table with matched records from the right table and null values for any rows which cannot be matched

Does anyone know how to prevent the #Error from appearing when there's no records to display in the connecting table/ query?

If you can post the tables and the queries involved in a small DB (or send them to me direct - e-mails inmy profile), I'll get it working for you.

Jim.

ok Jim... Give me a sec.. Thanks!

Tonya~

Jim,

Attached is a sample DB ... I removed the Agent Names and replaced them with ID #s ...  so you'll see the following warning message but please disregard as it doesn't affect the calculation..


Selecting the following query:  

QRY_Daily Productivity_NEWOverall

Below is what each rep should have:
Agent ID 1 -
MTD is 57 New Cases
                56 Follow Ups

Agent ID 2-  
MTD is  4 New Cases
                 4 Follow Ups      

Agent ID 3 -
MTD is  3 New Cases
                 1 Follow Ups

Agent ID 4-  
MTD is  6 New Cases
                 6 Follow Ups      

Agent ID 7 -
MTD is  #Error New Cases  but should show a blank since there weren't any new cases assigned for that date or if possible bring the running total from the last date NEW cases were assigned down.
                 3 Follow Ups


and so forth for the remaining IDs ...

Image of above data (datasheet view):
EE-Sample-DB.zip

FYI I'm working on this now...day got away from me (sorry).  I'll have something for you in a bit.

Jim.

Tonya,

 Little confused as to what constitutes a new case vs a follow up case.

 Looking at this:


 For Agent ID 7, what is the #'s for each type, the date, and why?  For example on 2/8, you have listed in the comment above that there are 3 follow ups and no new cases.

 How did you get to that point?

 Is it the number of new cases for a day (2/7 had 3) and as of 2/8, there are still x open?

Jim.

Jim,

New Cases are stored in the Router Tracker table and are noted "NEW" in the Case Type column.

Any case in the Router will be NEW and the query is used to run a daily running total grouped by Agent and Date Case was Assigned so on 2/7/13 had 3 new cases assigned and none were assigned as of 2/8.

If I was to run a MTD for Jan for the example above Agent ID 7 would have 3 for Jan 14 and Agent ID 8 would have 2 for 1/16.

Data for the sample goes through 2/8.

Tonya

And a follow up case is defined as what then?

 What I'm trying to get a grasp of is the basic logic.  Your query to me seems to be "overbuilt".  That is you took a couple of base queries and built on top of them.   Now were taking that and trying to add the running sums on top of that.  That's inefficent (or at the very least getting very complex).

 So when I run into this, I always take a step back and look at what I'm doing.   In this case, I believe I want:

1. A list of agents with each date (question here is what defines the dates to appear)
2. For that date, a count of the FU cases and the new cases.
3. A running sum for both.

But the last only makes since if FU cases is not already a running sum.  That's why I can't figure out why agent 7 should have anything on the 8th.

 Sorry to be dragging this out like this (you've waited long enough already), but I just don't want to send you blindly down the wrong path with these.

Jim.

Ok... This could be very much the case (my queries being over built) as I didn't know how to obtain the running sum and confused myself at the end ... so I know it's looking rather redundant, apologies ...   :S

Follow Ups are based the Agent table and New Cases on the Router so each query should be based off of a single table instead of a joined ..

Right now I have it set up where qry_Daily Productivity_NEW_2 and qry_Daily Productivity_FU_2 are reporting case counts based on the 2 tables linked to each other.. I corrected this to have each query feed directly from the table that's housing the specified data (follow ups or new).  Hope that wasn't confusing.

Again my apologies, if something is looking out of order it may be due to my novice skill level.

Tonya
EE-Sample-DB.zip

<<Again my apologies, if something is looking out of order it may be due to my novice skill level.>>

 No problem at all.  Like I said, just want to make sure I'm not sending you in the wrong direction with anything and it could have just as easily been me not understanding something.

 Sometimes a complex set of queries is what you are stuck with, but when you start reaching 3 and 4 levels deep, it's time to step back and look at what your doing.

 It's such an easy thing to fall into too.   In fact I worked on a app that had one query over seven levels deep.  Queries worked, but they were slow.

 I'll download your new sample now and have a look.

Jim.

Thank you sooo much Jim!  

Wish I could double your point amount!!  Your patience and dedication to helping resolve this issue is truly appreciated.  Because you could've ran at the 1st sight of my dysfunctional db setup, haha..

Thanks again!  Kindest regards,

Tonya

Not a problem and glad to help.  Just sorry it took me so long.

 Just never know when I'll get bogged down with work.

Jim.