If record is in X table then do not show in Y - how to design a criteria?

[behedwin]

Hi

I have a row source that is based of Profile_Table.
The first column in this row source is Profile_ID

I want to create a criteria that exclude ID numbers if they are represented in Time_Table.

Example.
If i have Profile_ID "77"
It will show in my row source.
But if i add The number "77" in column Profile_ID (secondary key) in table Time_Table
I want this record to not show in row source seletion.

Is this possible?


If profile_table.profile_id = time_table.time_id then
exclude from query
else
include in query
end if

 

[welshgasman]

Use an unmatched query as the form source?

 

[behedwin]

Hm, how?

How do i create a query that display all records that are NOT in Time_Table?

Time_Table have Time_ID that have a one to one relationship with Profile_ID in Profile_Table

I want a query that display WHAT records in Profile_Table are not represented in Time_Table?

 

[welshgasman]

Use the query wizard and the Unmatched query option. Save the query and use this as the record source for the form.

http://www.google.co.uk/search?q=ac...-ab&gfe_rd=cr&dcr=0&ei=RYkVWviWLejR8gfmx6DIBg

 

[xenou]

you can also write the SQL yourself

select * from Profile_Table t1 
where not exists (
    select * from Time_Table t2 where t1.profile_id = t2.time_id)

or

select * 
from 
    Profile_Table t1
    left join Time_Table t2
    on t1.profile_id = t2.time_id
where 
    t2.time_id is null