Outer Join in Xtab Query doesn't work

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I'm keeping track of weekly church donations by people
with numbered envelopes. Table1 is a list of people with
envelope #s as primary key. Table2 is a list of
contributions by envelope # giving $ in the envelope,
where $ can be assigned to several funds. No trouble
joining the tables by envelope #s, one-to-many. My
crosstab query has people (env #s) as rows and funds as
columns; total contributions by each person to each fund
as entries. I want people who have given nothing in the
period to appear in the table, so used an outer join
(arrow points to Table2). My query is missing non-
contributors, so the outer join isn't working. A teeny
test DB set up like this works fine, and has a column
inserted before the fund columns with a heading "<>",
which cannot be seen in Design View. Any idea of what I'm
doing wrong, and what's with the "<>"?
Bonus Q: When I do a Word Mailmerge using this DB, it
shows Select Queries for choosing, but no Crosstab
Queries. It appears I can trick Word into doing what I
want by making a Select Query by adding a column to a
Crosstab Query. Is this how it is supposed to work?
 
Possible clue:
If you have criteria defined for table2, the outer join
can behave unexpectedly. If this is the case modify the
criteria from

Where tbl2.col = 'test'
to
Where (tbl2.col = 'test' OR TBL2.COL IS NULL)
 
Back
Top