Include blanks in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a problem that seems like it should be easy, but I can't get it to
work. I'm working with a DB that has a table with test results. I have two
queries, one that takes all of the dates that tests were given, and then
counts all of the test results, giving me a total for each date of how many
tests were given. Another query does the same thing, but only counts test
results that were "positive." However, if no results for the day were
positive, that day is obviosly skipped. So, my two queries end up looking
something like this:

qryPositives: qryTotals:
3/28/05 2 3/28/05 6
4/01/05 1 4/01/05 2
4/12/05 4 4/6/05 2
5/02/05 1 4/12/05 8
5/05/05 2 4/26/05 3
5/02/05
4
5/05/05
5
5/21/05
4

I then have a 3rd query that merges the two, pulling the date column from
qryTotals and then the totals from each query. The problem is, when I do
that, it only returns dates that have a value from BOTH queries. So, in the
above example, 4/6/05, 4/26/05, and 5/21/05 would all be lost. What I'd like
to do is have the final query either put a blank or a 0 in the "positives"
column if there is no data for this date. Thanks in advance for any help.
 
Sounds like all you need to do is to change the join in the final query to
be an OUTER join where it gets all of the rows from the totals and any
matching rows from the positives. Based on the limited information you
supplied about the two Queries it might look a lot like this:

SELECT qryTotals.TestDate as TestDate,
qryTotals.CountOfTestResults as TestResults,
qryPositives.CountOfPositives as PositiveResults
FROM qryTotals LEFT JOIN qryPositives
ON qryTotals.TestDate = qryPositives.TestDate
ORDER BY qryTotals.TestDate

Ron W
 
Thanks, Ron! That did the trick. I thought it might be something simple,
but I wasn't previously aware of the different join types. Thanks again for
the tip.
 
Back
Top