Adding records to filtered query via linked table

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

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
 
"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.
 
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
 
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)
 
Back
Top