Can't see way to include outer join data in report

  • Thread starter Thread starter Brad Wood
  • Start date Start date
B

Brad Wood

My organization used to log tasks for projects in a spreadsheet. I
convinced them that a better process would be to create a database with
a small data entry utility and then simply run reports instead of
creating a new spreadsheet every month.
That worked fine except for one detail. The spreadsheet always included
every project whether hours were logged for it or not (this made it easy
to see at a glance that no work was done on a given project). I can't
reproduce that behaviour on a report.

For simplification, let me say that I have a Project table
(ProjectID, ProjectName)
with related entries in a Task table
(TaskID, TaskDescription, TaskDate).

When I create a query that lists tasks under their parent project with
associated dates, some projects will fall off the list when I add a date
range to the where clause (when no tasks were entered for that project
in that range).
Performing a left outer join makes no difference because as long as a
date range is included in the where clause, an outer join and an inner
join give the same results.

I can't think of a solution to this...
 
Brad Wood said:
My organization used to log tasks for projects in a spreadsheet. I
convinced them that a better process would be to create a database with
a small data entry utility and then simply run reports instead of
creating a new spreadsheet every month.
That worked fine except for one detail. The spreadsheet always included
every project whether hours were logged for it or not (this made it easy
to see at a glance that no work was done on a given project). I can't
reproduce that behaviour on a report.

For simplification, let me say that I have a Project table
(ProjectID, ProjectName)
with related entries in a Task table
(TaskID, TaskDescription, TaskDate).

When I create a query that lists tasks under their parent project with
associated dates, some projects will fall off the list when I add a date
range to the where clause (when no tasks were entered for that project
in that range).
Performing a left outer join makes no difference because as long as a
date range is included in the where clause, an outer join and an inner
join give the same results.
Click to expand...


How about using two queries. One to filter the tasks by the
date range and a second query that uses a Left Join from
the project table to the first query.

For a report, this can be done in a single query by using a
the first query as a virtual table in the from clause, but
it might be more difficult to write/understand this complex
query.
 
I couldn't see how to do this w/o a union. I assume you're talking
about a nested select. Given this regular select, could you modify it
to do what you describe?

select
ProjectName, TaskDescription, TaskDate
from
Project inner join Task on Project.ProjectID=Task.ProjectID
where
TaskDate > #6/1/05#;
 
For future reference (not easy to figure out), the query using tables
described earlier would be like yay:

SELECT
Project.ProjectName, t.TaskDescription
FROM
Project LEFT JOIN
[
SELECT
p.ProjectName, t.TaskDescription, t.ProjectID
FROM
Project as p INNER JOIN Task as t ON p.ProjectID = t.ProjectID
WHERE
(((t.TaskDate)>#5/25/2005#))
]. AS bobo
ON Project.ProjectID=bobo.ProjectID;

Note especially the "." after the right bracket.
 
Now I have this query that works fine, but I don't see any way to make
it work within a report. If I pass a filter string to my report like I
have in this query (t.TaskDate > #5/25/2005#), the report results show
just as they had before. It seems that the filter is not applied to the
nested select / virtual table, but instead to the whole result set
rendering the left join useless.
I tried making it a parameterized query and referencing the parameter
within the nested query; same results.

I hope I'm missing something...
 
Have you tried?

SELECT
ProjectName, TaskDescription, TaskDate
FROM
Project LEFT join Task on Project.ProjectID=Task.ProjectID
WHERE
TaskDate > #6/1/05# OR Task.ProjectID Is Null;
 
I hadn't; that is all that is needed.

However, I still can't get the query to work with a report. I need the
query's where clause to retain only the check for null and then add in
the date check when I run the report (in the "where condition" parameter
of the OpenReport function).

When I do that I get an empty result set...
 
I don't see why the sub query should have a joinin it. I
was thinking more along these lines:

SELECT
Project.ProjectName, t.TaskDescription
FROM
Project LEFT JOIN
[
SELECT
t.TaskDescription, t.ProjectID
FROM
Task as t
WHERE
(((t.TaskDate)>#5/25/2005#))
]. AS bobo
ON Project.ProjectID=bobo.ProjectID;
--
Marsh
MVP [MS Access]



Brad Wood said:
For future reference (not easy to figure out), the query using tables
described earlier would be like yay:

SELECT
Project.ProjectName, t.TaskDescription
FROM
Project LEFT JOIN
[
SELECT
p.ProjectName, t.TaskDescription, t.ProjectID
FROM
Project as p INNER JOIN Task as t ON p.ProjectID = t.ProjectID
WHERE
(((t.TaskDate)>#5/25/2005#))
]. AS bobo
ON Project.ProjectID=bobo.ProjectID;

Note especially the "." after the right bracket.

Marshall said:
How about using two queries. One to filter the tasks by the
date range and a second query that uses a Left Join from
the project table to the first query.

For a report, this can be done in a single query by using a
the first query as a virtual table in the from clause, but
it might be more difficult to write/understand this complex
query.
Click to expand...
Click to expand...
 
Back
Top