>Hi all! I'm back ;)!
>
>I'm trying to create a local view and having some problems. I have 3 tables I want to include which are part of a many-to-many relationship
>
>The tables are:
> allcodes <--- contacts ---> person
>
>The fields I want to display in my view are:
> - allcodes.dispval for codetype='position'
> .and. contacts.poscode=allcodes.codeno
> - person.first,middle,last,phone,email for
> person.persid=contacts.persid
>
>I also want to filter only contacts.agencycode="1234" so only one agency's contacts display in a view (or can I link that filter to my form's data environment?)
>
>The final view should look like this:
>Position........First.......Middle Initial......Last.......Phone
>
>In View Designer I can create the relation between Contacts and Person and even show only agency "1234" contacts. But when I try to link Allcodes.displayvalue to my position code in Contacts.dbf I'm getting 0 records in my view.
>
>Oh, yeah, here's the sql that's screwing up:
>
>SELECT Allcodes.dispval, Contacts.poscode, Person.first, Person.middlei,;
> Person.last, Person.phone, Person.email;
> FROM ird!contacts, ird!person, ird!allcodes;
> WHERE Contacts.persid = Person.persid;
> AND (Contacts.acode = "11000";
> AND Contacts.poscode = Allcodes.codeno);
> ORDER BY Contacts.poscode
>
>Please help :)
>
>Mandy

Two possibilities, Mandy.

First, if there is no Allcodes.codeno to correspond with the Contacts.poscode, then that row won't show in the final result. For example, if Contacts.acode is blank, you must have a blank record in Allcodes. Check field lengths, etc. If you're using VFP5 use a left outer join here.

Second, why the () after the first AND. You shouldn't need them.

HTH
Barbara