shaz0503
asked on
query not returning correct information
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
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];
ASKER
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....
Z-CASS-SoEPastDue-SoEDueNow.xlsx
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));
Z-SoE-Past-Due.xlsxZ-CASS-SoEPastDue-SoEDueNow.xlsx
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];
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];
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Could you copy-paste the SQL opening in SQL view?
ASKER
I simply copied and pasted your SQL...
S
S
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];
ASKER
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.Comment s, 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.[Employ ee 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].[Emp loyee 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));
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.Comment
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.[Employ
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));
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.