Adding records to filtered query via linked table

[Guest]

Hello,

I have one table (DATA) with records sorted by date in a YYYY-MM format.
But there is not a record for every month.

The second table (MONTHS) is a list of all months in 2005, in the same
YYYY-MM format.

My first goal is to produce a list of all months, with data showing where
data exists. This part is easy--when I Left-Join MONTHS to DATA with no
additional filters, I get:
YYYY-MM Data1 Data2
2005-01 1 a
2005-02
2005-03 2 b
2005-04 2 c
2005-05 1 a
2006-06
2007-07 1 d

My problem comes because I want to add criteria to this query on Data1 to
only show the "1" records, but I still want every month to show up.

This is what I want:
2005-01 1 a
2005-02
2005-03
2005-04
2005-05 1 a
2006-06
2007-07 1 d

But this is what I get--months missing:
2005-01 1 a
2005-05 1 a
2007-07 1 d

Is it even possible to do this?

Thanks!
LJones

 

[Duane Hookom]

"only show the "1" records" and "still want every month to show up" are not
compatible specifications. You can't have your cake and eat it too.

 

[Guest]

Well, why can't I??

I ended up creating separate tables for each criteria. Then, linking the
tables worked because I did not need to filter for anything.

Thanks.
/Lisa

 

[John Spencer (MVP)]

Try one of the following options

Try a calculated field

Field: Data1Calc: IIF(Data1 <> 1, Null, Data1)

You might have to go to

IIF(Data1 <>1 OR Data1 Is Null, Null, Data1)