Linked Table Contains Specific Records

[eric333]

Hi,

I have a report with two tables - packages (table 1) and statuses (table 2). There is a common column in both tables for linking - that is the package number. Each time there is a change in the package's status, an entry is made in Table 2. For example, V for verified, X for destroyed, S for sent. I am trying to identify certain types of packages (using a column in the packages table that identifies the package type) where there is not an entry in Table 2 with code S (for sent).

I tried a select statement of:

{Table1.Type} = "1" and
not ({Table2.Status} in ["S"])

But, that did not work properly. It returned all instances of a Package Type 1 regardless of whether or not the package as a Status of S.

Thanks in advance for any suggestions.

 

[IanWaterman]

What is join between two table inner or left outer?

Try using an inner (Equal) join, that should work.

Ian

 

[eric333]

I tried both. A friend helpmed me build a SQL view so I'm good now. Thanks anyways!