Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

query not returning correct information

Posted on 2011-02-21
11
Medium Priority
?
783 Views
Last Modified: 2012-05-11
All

I am trying to use a query within a larger query....and I can't get blank cells to 'report'

in the attached code the first one returns staff with no SoE Review Date or a SoE Review date that has past.  some records return a 'blank' under the [SoE Review Date] as there is none.  This query returns corrects correct information... however

When I use this query in the second query - the 'blank' reords do not show.

I had assistance (Nz query) in a previous query to return 'none' in blank fields and while this works - if i use in this second query - I get 'none' in all records for the [SoE Review Date]

any ideas appreciated or please ask if i need to give more info

rgds
SELECT Z_ActiveEmployee_tblStatementDetails.[Department Description], Z_SoEEligible.[Employee ID], Z_ActiveEmployee_tblStatementDetails.Surname, Z_ActiveEmployee_tblStatementDetails.[Given Name], Z_ActiveEmployee_tblStatementDetails.[Supervisor Name], Z_ActiveEmployee_tblStatementDetails.[Last Start Date], Z_ActiveEmployee_tblStatementDetails.[SoE review date]
FROM Z_SoEEligible INNER JOIN Z_ActiveEmployee_tblStatementDetails ON Z_SoEEligible.[Employee ID] = Z_ActiveEmployee_tblStatementDetails.[Employee ID]
WHERE (((Z_SoEEligible.[Employee ID])<>"8708481" And (Z_SoEEligible.[Employee ID])<>"4215253" And (Z_SoEEligible.[Employee ID])<>"4660940" And (Z_SoEEligible.[Employee ID])<>"4262515" And (Z_SoEEligible.[Employee ID])<>"4002301") AND ((Z_ActiveEmployee_tblStatementDetails.[Last Start Date])<Now()-60) AND ((Z_ActiveEmployee_tblStatementDetails.[SoE review date])<Now() Or (Z_ActiveEmployee_tblStatementDetails.[SoE review date]) Is Null))
ORDER BY Z_ActiveEmployee_tblStatementDetails.[Department Description], Z_ActiveEmployee_tblStatementDetails.[Given Name], Z_ActiveEmployee_tblStatementDetails.[Supervisor Name];


SELECT Z_ActiveEmployee_tblStatementDetails.[Department Description], Z_SoEEligible.[Employee ID], Z_ActiveEmployee_tblStatementDetails.Surname, Z_ActiveEmployee_tblStatementDetails.[Given Name], Z_ActiveEmployee_tblStatementDetails.[Supervisor Name], Z_ActiveEmployee_tblStatementDetails.[Last Start Date], Z_ActiveEmployee_tblStatementDetails.[SoE review date]
FROM Z_SoEEligible INNER JOIN Z_ActiveEmployee_tblStatementDetails ON Z_SoEEligible.[Employee ID] = Z_ActiveEmployee_tblStatementDetails.[Employee ID]
WHERE (((Z_SoEEligible.[Employee ID])<>"8708481" And (Z_SoEEligible.[Employee ID])<>"4215253" And (Z_SoEEligible.[Employee ID])<>"4660940" And (Z_SoEEligible.[Employee ID])<>"4262515" And (Z_SoEEligible.[Employee ID])<>"4002301") AND ((Z_ActiveEmployee_tblStatementDetails.[Last Start Date])<Now()-60) AND ((Z_ActiveEmployee_tblStatementDetails.[SoE review date])<Now() Or (Z_ActiveEmployee_tblStatementDetails.[SoE review date]) Is Null))
ORDER BY Z_ActiveEmployee_tblStatementDetails.[Department Description], Z_ActiveEmployee_tblStatementDetails.[Given Name], Z_ActiveEmployee_tblStatementDetails.[Supervisor Name];

Open in new window

0
Comment
Question by:shaz0503
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 34947143
Do you need assistance in forming the NZ function.
SELECT Z_ActiveEmployee_tblStatementDetails.[Department Description], 
         Z_SoEEligible.[Employee ID], 
         Z_ActiveEmployee_tblStatementDetails.Surname, 
         Z_ActiveEmployee_tblStatementDetails.[Given Name], 
         Z_ActiveEmployee_tblStatementDetails.[Supervisor Name], 
         Z_ActiveEmployee_tblStatementDetails.[Last Start Date], 
         NZ(Z_ActiveEmployee_tblStatementDetails.[SoE review date], 
            "None") AS [SoE review date] 
    FROM Z_SoEEligible 
         INNER JOIN Z_ActiveEmployee_tblStatementDetails 
           ON Z_SoEEligible.[Employee ID] = Z_ActiveEmployee_tblStatementDetails.[Employee ID]
   WHERE (((Z_SoEEligible.[Employee ID]) <> "8708481" 
           AND (Z_SoEEligible.[Employee ID]) <> "4215253" 
           AND (Z_SoEEligible.[Employee ID]) <> "4660940" 
           AND (Z_SoEEligible.[Employee ID]) <> "4262515" 
           AND (Z_SoEEligible.[Employee ID]) <> "4002301") 
          AND ((Z_ActiveEmployee_tblStatementDetails.[Last Start Date]) < NOW() - 60) 
          AND ((Z_ActiveEmployee_tblStatementDetails.[SoE review date]) < NOW() 
                OR (Z_ActiveEmployee_tblStatementDetails.[SoE review date]) IS NULL)) 
ORDER BY Z_ActiveEmployee_tblStatementDetails.[Department Description], 
         Z_ActiveEmployee_tblStatementDetails.[Given Name], 
         Z_ActiveEmployee_tblStatementDetails.[Supervisor Name];

Open in new window

I don't see difference between your two statements. If you are looking for something else, post some sample data with expected result.
0
 

Author Comment

by:shaz0503
ID: 34947193
Apologies

Accidentally posted same code twice...

Attached is the second code (For file Z_CASS_Soe.....)

and also the two final query results -

in the file Z-CASS..... there should be blank records for Karen Fox for example as she has no SoE Review date...

as mentioned the first query works fine - the problem is including it in the second query....
SELECT DeptByArea.School, DeptByArea.[Department Description], Z_SoEEligible.[Employee ID], Z_SoEEligible.Surname, Z_SoEEligible.[Given Name], Z_SoEEligible.[Supervisor Name], [Z_SoE Past Due].[SoE review date] AS [SoE Past Due], [Z_SoE Due within 60 days].[SoE Due Now] AS [SoE Now Due], SoEPastDueComments.Comments, Z_SoEEligible.[Eligibility Group]
FROM (((((DeptByArea INNER JOIN Z_SoEEligible ON DeptByArea.[Department Description] = Z_SoEEligible.[Department Description]) LEFT JOIN [Z_SoE Due within 60 days] ON Z_SoEEligible.[Employee ID] = [Z_SoE Due within 60 days].[Employee ID]) LEFT JOIN [Z_SoE Past Due] ON Z_SoEEligible.[Employee ID] = [Z_SoE Past Due].[Employee ID]) LEFT JOIN SoEPastDueComments ON Z_SoEEligible.[Employee ID] = SoEPastDueComments.[Employee ID]) LEFT JOIN [EmpTerm>12] ON Z_SoEEligible.[Employee ID] = [EmpTerm>12].[Employee ID]) LEFT JOIN [LastStartDate>2mths] ON Z_SoEEligible.[Employee ID] = [LastStartDate>2mths].[Employee ID]
WHERE ((([Z_SoE Past Due].[SoE review date]) Is Not Null)) OR ((([Z_SoE Due within 60 days].[SoE Due Now]) Is Not Null));

Open in new window

Z-SoE-Past-Due.xlsx
Z-CASS-SoEPastDue-SoEDueNow.xlsx
0
 
LVL 46

Expert Comment

by:aikimark
ID: 34947277
The parens were a bit unbalanced and I converted the <> comparisons to a Not In() clause.
SELECT Z_ActiveEmployee_tblStatementDetails.[Department Description], 
Z_SoEEligible.[Employee ID], Z_ActiveEmployee_tblStatementDetails.Surname,
Z_ActiveEmployee_tblStatementDetails.[Given Name],
 Z_ActiveEmployee_tblStatementDetails.[Supervisor Name],
 Z_ActiveEmployee_tblStatementDetails.[Last Start Date],
 Z_ActiveEmployee_tblStatementDetails.[SoE review date]

FROM Z_SoEEligible INNER JOIN Z_ActiveEmployee_tblStatementDetails ON 
Z_SoEEligible.[Employee ID] = Z_ActiveEmployee_tblStatementDetails.[Employee ID]

WHERE 
(Z_SoEEligible.[Employee ID] Not In ("8708481", "4215253", "4660940", "4262515", "4002301")) 
AND 
((Z_ActiveEmployee_tblStatementDetails.[Last Start Date])<Now()-60) 
AND 
((Z_ActiveEmployee_tblStatementDetails.[SoE review date]<Now()) Or (Z_ActiveEmployee_tblStatementDetails.[SoE review date] Is Null) )

ORDER BY Z_ActiveEmployee_tblStatementDetails.[Department Description], Z_ActiveEmployee_tblStatementDetails.[Given Name], Z_ActiveEmployee_tblStatementDetails.[Supervisor Name];

Open in new window

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 41

Expert Comment

by:Sharath
ID: 34947374
check this.
SELECT DeptByArea.School, 
       DeptByArea.[Department Description], 
       Z_SoEEligible.[Employee ID], 
       Z_SoEEligible.Surname, 
       Z_SoEEligible.[Given Name], 
       Z_SoEEligible.[Supervisor Name], 
       [Z_SoE Past Due].[SoE review date]       AS [SoE Past Due], 
       [Z_SoE Due within 60 days].[SoE Due Now] AS [SoE Now Due], 
       SoEPastDueComments.Comments, 
       Z_SoEEligible.[Eligibility Group] 
  FROM (((((DeptByArea 
            INNER JOIN Z_SoEEligible 
              ON DeptByArea.[Department Description] = Z_SoEEligible.[Department Description])
           LEFT JOIN [Z_SoE Due within 60 days] 
             ON Z_SoEEligible.[Employee ID] = [Z_SoE Due within 60 days].[Employee ID] 
                AND [Z_SoE Due within 60 days].[SoE Due Now] IS NOT NULL) 
          LEFT JOIN [Z_SoE Past Due] 
            ON Z_SoEEligible.[Employee ID] = [Z_SoE Past Due].[Employee ID] 
               AND [Z_SoE Past Due].[SoE review date] IS NOT NULL) 
         LEFT JOIN SoEPastDueComments 
           ON Z_SoEEligible.[Employee ID] = SoEPastDueComments.[Employee ID]) 
        LEFT JOIN [EmpTerm>12] 
          ON Z_SoEEligible.[Employee ID] = [EmpTerm>12].[Employee ID]) 
       LEFT JOIN [LastStartDate>2mths] 
         ON Z_SoEEligible.[Employee ID] = [LastStartDate>2mths].[Employee ID];

Open in new window

0
 

Author Comment

by:shaz0503
ID: 34947735
Sharath_123

Thanks

I have had a go at this and the following 'error' comes up and I don't understand why


Join expression not supported

AND [Z_SoE Due within 60 days].[SoE Due Now] IS NOT NULL


Any thoughts - I am using 2007 if that matters

rgds
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 34947805
give a try.
SELECT DeptByArea.School, 
       DeptByArea.[Department Description], 
       Z_SoEEligible.[Employee ID], 
       Z_SoEEligible.Surname, 
       Z_SoEEligible.[Given Name], 
       Z_SoEEligible.[Supervisor Name], 
       [Z_SoE Past Due].[SoE review date]       AS [SoE Past Due], 
       [Z_SoE Due within 60 days].[SoE Due Now] AS [SoE Now Due], 
       SoEPastDueComments.Comments, 
       Z_SoEEligible.[Eligibility Group] 
  FROM (((((DeptByArea 
            INNER JOIN Z_SoEEligible 
              ON DeptByArea.[Department Description] = Z_SoEEligible.[Department Description])
           LEFT JOIN [Z_SoE Due within 60 days] 
             ON (Z_SoEEligible.[Employee ID] = [Z_SoE Due within 60 days].[Employee ID] 
                AND [Z_SoE Due within 60 days].[SoE Due Now] IS NOT NULL)) 
          LEFT JOIN [Z_SoE Past Due] 
            ON (Z_SoEEligible.[Employee ID] = [Z_SoE Past Due].[Employee ID] 
               AND [Z_SoE Past Due].[SoE review date] IS NOT NULL)) 
         LEFT JOIN SoEPastDueComments 
           ON Z_SoEEligible.[Employee ID] = SoEPastDueComments.[Employee ID]) 
        LEFT JOIN [EmpTerm>12] 
          ON Z_SoEEligible.[Employee ID] = [EmpTerm>12].[Employee ID]) 
       LEFT JOIN [LastStartDate>2mths] 
         ON Z_SoEEligible.[Employee ID] = [LastStartDate>2mths].[Employee ID];

Open in new window

0
 

Author Comment

by:shaz0503
ID: 34947922
Sharath

Thanks - new error and I have gone through the query and can't see where there is anything amiss.......


can't represent the join expression (Z_SoEEligible.[Employee ID] = [Z_SoE Due within 60 days].[Employee ID]
                AND [Z_SoE Due within 60 days].[SoE Due Now] IS NOT NULL))
in Design View

field deleted or renamed, misspelled etc etc error

grrrrrr

S
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34947929
Could you copy-paste the SQL opening in SQL view?
0
 

Author Comment

by:shaz0503
ID: 34947944
I simply copied and pasted your SQL...

S
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34947989
check this.
SELECT DeptByArea.School, 
       DeptByArea.[Department Description], 
       Z_SoEEligible.[Employee ID], 
       Z_SoEEligible.Surname, 
       Z_SoEEligible.[Given Name], 
       Z_SoEEligible.[Supervisor Name], 
       [Z_SoE Past Due].[SoE review date]       AS [SoE Past Due], 
       [Z_SoE Due within 60 days].[SoE Due Now] AS [SoE Now Due], 
       SoEPastDueComments.Comments, 
       Z_SoEEligible.[Eligibility Group] 
  FROM (((((DeptByArea 
            INNER JOIN Z_SoEEligible 
              ON DeptByArea.[Department Description] = Z_SoEEligible.[Department Description])
           LEFT JOIN [Z_SoE Due within 60 days] 
             ON (Z_SoEEligible.[Employee ID] = [Z_SoE Due within 60 days].[Employee ID] 
                AND ([Z_SoE Due within 60 days].[SoE Due Now] IS NOT NULL))) 
          LEFT JOIN [Z_SoE Past Due] 
            ON (Z_SoEEligible.[Employee ID] = [Z_SoE Past Due].[Employee ID] 
               AND ([Z_SoE Past Due].[SoE review date] IS NOT NULL))) 
         LEFT JOIN SoEPastDueComments 
           ON Z_SoEEligible.[Employee ID] = SoEPastDueComments.[Employee ID]) 
        LEFT JOIN [EmpTerm>12] 
          ON Z_SoEEligible.[Employee ID] = [EmpTerm>12].[Employee ID]) 
       LEFT JOIN [LastStartDate>2mths] 
         ON Z_SoEEligible.[Employee ID] = [LastStartDate>2mths].[Employee ID];

Open in new window

0
 

Author Closing Comment

by:shaz0503
ID: 34948028
Sharath

I got it - had to add another field to the inital query...

Many thanks for your help - was a useful exercise to have to go through the code several times - makes for a better understanding.

For your info have added the SQL that got me what I needed.


SELECT DeptByArea.School, DeptByArea.[Department Description], Z_SoEEligible.[Employee ID], Z_SoEEligible.Surname, Z_SoEEligible.[Given Name], Z_SoEEligible.[Supervisor Name], [Z_SoE Past Due].[SoE review date] AS [SoE Past Due], [Z_SoE Due within 60 days].[SoE Due Now] AS [SoE Now Due], SoEPastDueComments.Comments, Z_SoEEligible.[Eligibility Group]
FROM (((((DeptByArea INNER JOIN Z_SoEEligible ON DeptByArea.[Department Description] = Z_SoEEligible.[Department Description]) LEFT JOIN [Z_SoE Due within 60 days] ON Z_SoEEligible.[Employee ID] = [Z_SoE Due within 60 days].[Employee ID]) LEFT JOIN [Z_SoE Past Due] ON Z_SoEEligible.[Employee ID] = [Z_SoE Past Due].[Employee ID]) LEFT JOIN SoEPastDueComments ON Z_SoEEligible.[Employee ID] = SoEPastDueComments.[Employee ID]) LEFT JOIN [EmpTerm>12] ON Z_SoEEligible.[Employee ID] = [EmpTerm>12].[Employee ID]) LEFT JOIN [LastStartDate>2mths] ON Z_SoEEligible.[Employee ID] = [LastStartDate>2mths].[Employee ID]
WHERE ((([Z_SoE Past Due].[Last Start Date])<Now()-60)) OR ((([Z_SoE Past Due].[SoE review date]) Is Not Null)) OR ((([Z_SoE Due within 60 days].[SoE Due Now]) Is Not Null));
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question