Union Query - Getting More that expected or None 1

[regava]

Access 2000 - I am implementing a Tracking System that among other things requires that a tickler be provided so that the users will know what is to be done next on each individual case. I developed a Union Query that is doing the job except for this section. See the code below. In the first section "NO ACTION" I expected to get rows for those items that did not have any Calendar event. I am getting nothing eventhough there are 4.
In the second section "NO ACTION2" I expected to get rows for those items that only have closed Calendar events (Status <> "PENDING"). I am getting those plus other that have open (Status = "PENDING") calendar events. I have worked on this problems for several days and cannot find what is wrong. Any help will be deeply appreciated.

QUERY:

SELECT "NO ACTION" AS Cat, "NO ACTION" AS Item, "NO ACTION" AS Action, Date() AS ActDate, tblTracking.Tatno AS TatNo, tblTracking.DisplTatno AS DispTat, tblTracking.ALJName AS Judge, LEFT(tblTracking.Taxpayer,20) AS TP, tblTracking.Related AS Rel, tblTracking.Consol AS Con, "A" AS Class
FROM tblTracking
WHERE IsNull(tblTracking.ClosedDate)
     AND tblTracking.Consol <> "C"
     AND tblTracking.SineDie = "N"
     AND not isNull([tblTracking].[AcknowledgeDate])
     AND ( (Exists (Select * FROM tblCalendar INNER JOIN tblTracking on tblTracking.Tatno = tblCalendar.Tatno
                         WHERE tblTracking.Tatno = tblCalendar.Tatno)) = false)

UNION SELECT "NO ACTION2" AS Cat, "NO ACTION2" AS Item, "NO ACTION2" AS [Action], Date() AS ActDate, tblTracking.Tatno AS TatNo, tblTracking.DisplTatno AS DispTat, tblTracking.ALJName AS Judge, Left(tblTracking.Taxpayer,20) AS TP, tblTracking.Related AS Rel, tblTracking.Consol AS Con, "A" AS Class
FROM tblTracking
WHERE tblTracking.Consol<>"C"
     AND IsNull(tblTracking.ClosedDate)
     AND tblTracking.SineDie="N"
     AND NOT IsNull(tblTracking.AcknowledgeDate)
     AND ((Exists (Select * FROM tblCalendar INNER JOIN tblTracking on tblTracking.Tatno = tblCalendar.Tatno
                            WHERE tblTracking.Tatno = tblCalendar.Tatno)) AND Needcalendar(tblTracking.Tatno) = 2)
ORDER BY Tatno, actdate;

VBA CODE
'================================================
Public Static Function NeedCalendar(strTatno As String) As Integer
Dim strSqlx As String
Dim dbx As Database
Dim recx As Recordset
Dim reopen As Integer
Dim reclosed As Integer
reopen = 0
reclosed = 0
strSqlx = "tblCalendar"
Set dbx = CurrentDb()
Set recx = dbx.OpenRecordset(strSqlx)
If Not recx.BOF And Not recx.EOF Then
  Do While Not recx.EOF
    If recx("Tatno") = strTatno Then
      If recx("Status") = "PENDING" Then
        reopen = reopen + 1
      Else
        reclosed = reclosed + 1
      End If
    End If
    recx.MoveNext
  Loop
End If
NeedCalendar = SetExit(reopen, reclosed)
recx.Close
Set recx = Nothing
End Function
'================================================
Public Static Function SetExit(intOpen As Integer, intClose As Integer) As Integer
If intOpen = 0 And intClose = 0 Then
   SetExit = 0                  'Have no records
   Exit Function
   ElseIf intOpen > 0 And intClose > 0 Then
             SetExit = 3                  'Have both closed and open records
             Exit Function
             ElseIf intOpen > 0 And intClose = 0 Then
                       SetExit = 1                  'Have only open records
                       Exit Function
                       ElseIf intOpen = 0 And intClose > 0 Then
                                 SetExit = 2                  'Have only closed records
                             End If
End Function
'================================================

 

[Golom]

Look at the SQL statements in your EXISTS clauses. You are referencing "tblTracking" in both of them but that is also the table in the outer query. If you don't assign different table aliases to the EXISTS selects then the SQL parser may believe that they are coordinated sub-queries. Do something like

SELECT * 
FROM tblCalendar [red]As C[/red] INNER JOIN tblTracking [red]As T[/red] 
     ON [red]T[/red].Tatno = [red]C[/red].Tatno
WHERE   [red]T[/red].Tatno = [red]C[/red].Tatno

for the EXISTS-clause statements.

The WHERE clause duplicates the ON conditions so, if this is not intended to be a coordinated sub-query, the WHERE clause is redundant.

If you DO intend them to be coordinated sub-queries then adjust your aliases accordingly.

 

[regava]

Golom thank you. I did as you mentioned and nothing changed.
Also, when I changed the parameter in the function needcalendar to T2.Tatno and tried to execute it, the system ask me to "Enter Paramenter Value." I change it back to tblTracking.Tatno and I got the results mentioned. Any ideas.

 

[Golom]

I seem to be missing something here.

I don't see where your code is running the UNION query. You are opening the table "tblCalendar" and iterating through it; computing some values; etc. ... but where does the UNION query come into all this?

Also, I don't see any place in "NeedCalendar" that you might logically change something to "T2.Tatno". Field names (unless you do some weird things in the SQL) are not usually returned with the table name attached.

"Enter Paramenter Value." usually means that you have specified a field name that doesn't exist in the table you are referencing.

 

[regava]

Golom I am sorry I was not clear in my response. On the exists-clause statement when I changed needcalendar(tblTracking.Tatno) to needcalendar(T2.Tatno) is when I got the error message.
There are two unique and independent conditions taking place, a) No calendar event at all ("NO ACTION"), and b) calendar event(s) that took place but they are not "PENDING" any more ("NO ACTION2"), if there is any PENDING calendar, it does not qualify. These two conditions are put together and eventually a report will be issued.

 

[regava]

Can someone help me with this. Please give me something to work on I am reaching the end of the rop.

 

[lespaul]

might be easier if we could see some data and your expected results:

[tt]
tblTracking
ClosedDate Consol TatNo other relevant fields etc....
data data data
data data data
data data data

tblCalendar
TatNo other relevant fields etc.
data
data
data
[/tt]

and your expected results from the sample data...

Thanks,


Leslie

Have you met Hardy Heron?

 

[regava]

I have modified my original query to call the function ActionZero, which returns what I need. However, the way this query is written it will go through more than 6000 records in tblTracking when it only needs to go through no more that 60 records. This is my problem, I do not want to execute the function ActionZero if any of the 4 previous statements in WHERE is not true. In other words the ActionZero function should be executed if the previous statements are all true. Is there any way to accomplish this? Any suggestion is appreciated.

UNION SELECT "NO ACTION" AS Cat, "NO ACTION" AS Item, "NO ACTION" AS Action, Date() AS ActDate, tblTracking.Tatno AS TatNo, tblTracking.DisplTatno AS DispTat,
tblTracking.ALJName AS Judge, LEFT(tblTracking.Taxpayer,20) AS TP, tblTracking.Related AS Rel, tblTracking.Consol AS Con, "A" AS Class
FROM tblTracking
WHERE (IsNull(tblTracking.ClosedDate) 
AND tblTracking.Consol <> "C"
AND tblTracking.SineDie = "N"
AND NOT IsNull(tblTracking.AcknowledgeDate))
AND ActionZero(tblTracking.Tatno) = 0

UNION SELECT "NO ACTION2" AS Cat, "NO ACTION2" AS Item, "NO ACTION2" AS Action, Date() AS ActDate, tblTracking.Tatno AS TatNo, tblTracking.DisplTatno AS DispTat,
tblTracking.ALJName AS Judge, LEFT(tblTracking.Taxpayer,20) AS TP, tblTracking.Related AS Rel, tblTracking.Consol AS Con, "A" AS Class
FROM tblTracking
WHERE (IsNull(tblTracking.ClosedDate) 
AND tblTracking.Consol <> "C"
AND tblTracking.SineDie = "N"
AND NOT IsNull(tblTracking.AcknowledgeDate))
AND ActionZero(tblTracking.Tatno) = 2

 

[Golom]

I don't think that you can prevent ActionZero running but you should be able to short-circuit it.

Public Function ActionZero (Tatno, ClosedDate, _
                            Consol, SineDie, _
                            AcknowledgeDate) 

If     NOT IsNull(ClosedDate) 
   OR  Consol = "C"
   OR  SineDie <> "N"
   OR  IsNull(AcknowledgeDate) Then
   
   ActionZero = 99
   Exit Function
End If
[COLOR=black cyan]' Regular ActionZero Code[/color]
End Function

 

[regava]

Golon, Thank you very much for your help. Everything is working as it should, nice and smooth. Have a great day.