Display records if no information in subtable

  • Thread starter Thread starter mewins
  • Start date Start date
M

mewins

I am trying to create a query that returns client names regardless if they
have a value in a subtable.

In tblMainClientInfo, I have LastName, FirstName
In tblFundHist, I have Funding
In tblESOLHist, I have CurrentClass (either "yes" or "no") and CurrentCore
(which displays the name of the class)

I would like the query to return all clients grouped together by funding and
to list what value they have in CurrentCore if their CurrentClass="yes". I
can make this work to only show clients who have a value in CurrentCore, but
I'm not sure how to make the records pop up if they don't have a record in
tblESOLHist or if CurrentClass="no."

I tried doing
IIf([tblesolhist].[currentclass]="yes",[tblesolhist].[currentcore]," ") but
this only gives me the records of clients with entries in tblESOLHist.

Ideas? Thanks
 
Use a left join in your query - tblMainClientInfo to the other tables.
This allows the name to be returned even if there is no records for the
other data.
 
Sorry, I should have posted this before:

SELECT [firstname] & " " & [lastname] AS [Full Name],
tblMainClientInfo.City, tblMainClientInfo.JobDev, tblFundHist.Funding,
IIf(tblesolhist.currentclass="yes",tblesolhist.currentcore," ") AS Class,
tblWkTasks.WkTask, tblWkTasks.WkAssignDt, tblWkTasks.WkStaff,
tblWkTasks.WkTaskDueDt
FROM ((tblMainClientInfo INNER JOIN tblFundHist ON
tblMainClientInfo.ClientID = tblFundHist.ClientID) INNER JOIN tblESOLHist ON
tblMainClientInfo.ClientID = tblESOLHist.ClientID) INNER JOIN tblWkTasks ON
tblMainClientInfo.ClientID = tblWkTasks.ClientID
WHERE (((tblWkTasks.WkTask) Is Not Null) AND ((tblFundHist.FndStartDt) Is
Not Null) AND ((tblFundHist.FndEndDt) Is Null) AND ((tblWkTasks.WkDtTaskComp)
Is Null))
ORDER BY tblFundHist.Funding;

Ideally, this would give me all clients who have uncompleted tasks grouped
by Funding and would display their CurrentClass if they had one, or just a
blank space if they didn't have a value for CurrentClass

tkelley via AccessMonster.com said:
Can you go into the SQL view and paste the SQL code you have so far. I'm
inclined to think you need to and "or is null" to your "=Yes" criteria. But
I'm not certain without seeing the code. I'm also assuming you've built it
as an outer join.
I am trying to create a query that returns client names regardless if they
have a value in a subtable.

In tblMainClientInfo, I have LastName, FirstName
In tblFundHist, I have Funding
In tblESOLHist, I have CurrentClass (either "yes" or "no") and CurrentCore
(which displays the name of the class)

I would like the query to return all clients grouped together by funding and
to list what value they have in CurrentCore if their CurrentClass="yes". I
can make this work to only show clients who have a value in CurrentCore, but
I'm not sure how to make the records pop up if they don't have a record in
tblESOLHist or if CurrentClass="no."

I tried doing
IIf([tblesolhist].[currentclass]="yes",[tblesolhist].[currentcore]," ") but
this only gives me the records of clients with entries in tblESOLHist.

Ideas? Thanks
Click to expand...
Click to expand...
 
Ok, so now I've tried to make a left join for tblMainClientInfo with
tblESOLHist and also tblWkTasks, but the query won't go. I get an error
message that highlights the second "FROM" and says only "Syntax error in FROM
clause." Here's the SQL:

SELECT [firstname] & " " & [lastname] AS [Full Name],
tblMainClientInfo.City, tblMainClientInfo.JobDev, tblFundHist.Funding,
IIf(tblesolhist.currentclass="yes",tblesolhist.currentcore," ") AS Class,
tblWkTasks.WkTask, tblWkTasks.WkAssignDt, tblWkTasks.WkStaff,
tblWkTasks.WkTaskDueDt
FROM (tblMainClientInfo INNER JOIN tblFundHist ON tblMainClientInfo.ClientID
= tblFundHist.ClientID)
FROM (tblMainClientInfo LEFT JOIN tblESOLHist ON tblMainClientInfo.ClientID
= tblESOLHist.ClientID)--this is the one w/the error
FROM (tblMainClientInfo LEFT JOIN tblWkTasks ON tblMainClientInfo.ClientID
= tblWkTasks.ClientID)
WHERE (((tblWkTasks.WkTask) Is Not Null) AND ((tblFundHist.FndStartDt) Is
Not Null) AND ((tblFundHist.FndEndDt) Is Null) AND ((tblWkTasks.WkDtTaskComp)
Is Null))
ORDER BY tblFundHist.Funding;

tkelley via AccessMonster.com said:
Karl's instinct below is correct. You aren't using an outer join (left join).
Check out this from Access help, play with it, and see if you don't get where
you need to be (to find it yourself, search for "left join"). If you don't
know how to modify a join, search for "modify a join"

Syntax
FROM table1 [ LEFT | RIGHT ] JOIN table2
ON table1.field1 compopr table2.field2

The LEFT JOIN and RIGHT JOIN operations have these parts:

Part Description
table1, table2 The names of the tables from which records are combined.
field1, field2 The names of the fields that are joined. The fields must be of
the same data type and contain the same kind of data, but they do not need to
have the same name.
compopr Any relational comparison operator: "=," "<," ">," "<=," ">=," or "<>.
"


Remarks
Use a LEFT JOIN operation to create a left outer join . Left outer joins
include all of the records from the first (left) of two tables, even if there
are no matching values for records in the second (right) table.


Sorry, I should have posted this before:

SELECT [firstname] & " " & [lastname] AS [Full Name],
tblMainClientInfo.City, tblMainClientInfo.JobDev, tblFundHist.Funding,
IIf(tblesolhist.currentclass="yes",tblesolhist.currentcore," ") AS Class,
tblWkTasks.WkTask, tblWkTasks.WkAssignDt, tblWkTasks.WkStaff,
tblWkTasks.WkTaskDueDt
FROM ((tblMainClientInfo INNER JOIN tblFundHist ON
tblMainClientInfo.ClientID = tblFundHist.ClientID) INNER JOIN tblESOLHist ON
tblMainClientInfo.ClientID = tblESOLHist.ClientID) INNER JOIN tblWkTasks ON
tblMainClientInfo.ClientID = tblWkTasks.ClientID
WHERE (((tblWkTasks.WkTask) Is Not Null) AND ((tblFundHist.FndStartDt) Is
Not Null) AND ((tblFundHist.FndEndDt) Is Null) AND ((tblWkTasks.WkDtTaskComp)
Is Null))
ORDER BY tblFundHist.Funding;

Ideally, this would give me all clients who have uncompleted tasks grouped
by Funding and would display their CurrentClass if they had one, or just a
blank space if they didn't have a value for CurrentClass
Can you go into the SQL view and paste the SQL code you have so far. I'm
inclined to think you need to and "or is null" to your "=Yes" criteria. But
Click to expand...
[quoted text clipped - 20 lines]
Ideas? Thanks
Click to expand...
Click to expand...
Click to expand...
 
ONLY ONE FROM CLAUSE in a query. The following query may have correct syntax,
but I'm not sure it will return the list of records you are looking for.

SELECT [firstname] & " " & [lastname] AS [Full Name]
, tblMainClientInfo.City
, tblMainClientInfo.JobDev
, tblFundHist.Funding
, IIf(tblesolhist.currentclass="yes",tblesolhist.currentcore," ") AS Class
, tblWkTasks.WkTask, tblWkTasks.WkAssignDt
, tblWkTasks.WkStaff
, tblWkTasks.WkTaskDueDt
FROM (((tblMainClientInfo INNER JOIN tblFundHist
ON tblMainClientInfo.ClientID = tblFundHist.ClientID)
LEFT JOIN tblESOLHist
ON tblMainClientInfo.ClientID = tblESOLHist.ClientID)
LEFT JOIN tblWkTasks
ON tblMainClientInfo.ClientID = tblWkTasks.ClientID)
WHERE (((tblWkTasks.WkTask) Is Not Null)
AND ((tblFundHist.FndStartDt) Is Not Null)
AND ((tblFundHist.FndEndDt) Is Null)
AND ((tblWkTasks.WkDtTaskComp) Is Null))
ORDER BY tblFundHist.Funding;


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks so much, that took care of the issue!

John Spencer said:
ONLY ONE FROM CLAUSE in a query. The following query may have correct syntax,
but I'm not sure it will return the list of records you are looking for.

SELECT [firstname] & " " & [lastname] AS [Full Name]
, tblMainClientInfo.City
, tblMainClientInfo.JobDev
, tblFundHist.Funding
, IIf(tblesolhist.currentclass="yes",tblesolhist.currentcore," ") AS Class
, tblWkTasks.WkTask, tblWkTasks.WkAssignDt
, tblWkTasks.WkStaff
, tblWkTasks.WkTaskDueDt
FROM (((tblMainClientInfo INNER JOIN tblFundHist
ON tblMainClientInfo.ClientID = tblFundHist.ClientID)
LEFT JOIN tblESOLHist
ON tblMainClientInfo.ClientID = tblESOLHist.ClientID)
LEFT JOIN tblWkTasks
ON tblMainClientInfo.ClientID = tblWkTasks.ClientID)
WHERE (((tblWkTasks.WkTask) Is Not Null)
AND ((tblFundHist.FndStartDt) Is Not Null)
AND ((tblFundHist.FndEndDt) Is Null)
AND ((tblWkTasks.WkDtTaskComp) Is Null))
ORDER BY tblFundHist.Funding;


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Ok, so now I've tried to make a left join for tblMainClientInfo with
tblESOLHist and also tblWkTasks, but the query won't go. I get an error
message that highlights the second "FROM" and says only "Syntax error in FROM
clause." Here's the SQL:

SELECT [firstname] & " " & [lastname] AS [Full Name],
tblMainClientInfo.City, tblMainClientInfo.JobDev, tblFundHist.Funding,
IIf(tblesolhist.currentclass="yes",tblesolhist.currentcore," ") AS Class,
tblWkTasks.WkTask, tblWkTasks.WkAssignDt, tblWkTasks.WkStaff,
tblWkTasks.WkTaskDueDt
FROM (tblMainClientInfo INNER JOIN tblFundHist ON tblMainClientInfo.ClientID
= tblFundHist.ClientID)
FROM (tblMainClientInfo LEFT JOIN tblESOLHist ON tblMainClientInfo.ClientID
= tblESOLHist.ClientID)--this is the one w/the error
FROM (tblMainClientInfo LEFT JOIN tblWkTasks ON tblMainClientInfo.ClientID
= tblWkTasks.ClientID)
WHERE (((tblWkTasks.WkTask) Is Not Null) AND ((tblFundHist.FndStartDt) Is
Not Null) AND ((tblFundHist.FndEndDt) Is Null) AND ((tblWkTasks.WkDtTaskComp)
Is Null))
ORDER BY tblFundHist.Funding;
Click to expand...
Click to expand...
 
Back
Top