I am designing a two-table report.

 

The first is a Room No. table which connects to a Resident table to get the resident information.

 

When the report finds that more than one resident has lived there (i.e. finds more than one matching record in the lookup table), the report skips over the room no. completely.

 

So, my report is only showing Room Numbers with one matching record in the second table.

 

I am using a left outer join, not enforced.

 

Any possible solutions to this problem?

I am using a record select statement. The tables go through a G/L table as well so I can see pricing information.

 

Unit = Room No. table.

And G/L Accounts are being filtered ( The report should only show certain Account numbers), as well as a date filter on posting date (Only information for 2009):

 

 

 SELECT "Unit"."Unit", "Customer"."Name", "G/L Entry"."Amount", "G/L Entry"."Posting Date", "G/L Entry"."G/L Account No_"

 FROM   ("Unit" "Unit" LEFT OUTER JOIN "G/L Entry" "G/L Entry" ON ("Unit"."Unit"="G/L Entry"."Unit Code") AND ("Unit"."Tenant Code"="G/L Entry"."Source No_")) LEFT OUTER JOIN "Customer" "Customer" ON "G/L Entry"."Source No_"="Customer"."No_"
 WHERE  "G/L Entry"."Posting Date">={d '2009-01-01'} AND ("G/L Entry"."G/L Account No_"='52021' OR "G/L Entry"."G/L Account No_"='52022' OR "G/L Entry"."G/L Account No_"='52023' OR "G/L Entry"."G/L Account No_"='52024' OR "G/L Entry"."G/L Account No_"='52025' OR "G/L Entry"."G/L Account No_"='52511' OR "G/L Entry"."G/L Account No_"='52515' OR "G/L Entry"."G/L Account No_"='52516' OR "G/L Entry"."G/L Account No_"='52517')
 ORDER BY "Unit"."Unit"

 

Is there an easy way to change the WHERE to an AND?