Show Companies with no MainContacts iamnu <iamnubw[ at ]gmail.com> 12/11/2008 11:27:11 PM CoName Table has a CompanyName field. Contacts Table has the name of a Contact, and a MainContact field (yes/ no). So the SQL statement would include CompanyName, Contact and MainContact. I want to display (list) those CompanyNames which do not have a MainContact. How do I make this calculation?

Re: Show Companies with no MainContacts Marshall Barton <marshbarton[ at ]wowway.com> 12/12/2008 12:26:38 AM iamnu wrote: [Quoted Text] >CoName Table has a CompanyName field. >Contacts Table has the name of a Contact, and a MainContact field (yes/ >no). >So the SQL statement would include CompanyName, Contact and >MainContact. > >I want to display (list) those CompanyNames which do not have a >MainContact. Try using something like: SELECT CompanyName FROM CoName WHERE Not Exist (SELECT 1 FROM Contacts WHERE Contacts.CoID = CompanyName.CoID And MainContact) -- Marsh MVP [MS Access]

Re: Show Companies with no MainContacts iamnu <iamnubw[ at ]gmail.com> 12/12/2008 4:08:32 AM On Dec 11, 5:26 pm, Marshall Barton <marshbar...[ at ]wowway.com> wrote: [Quoted Text] > iamnu wrote: > >CoName Table has a CompanyName field. > >Contacts Table has the name of a Contact, and a MainContact field (yes/ > >no). > >So the SQL statement would include CompanyName, Contact and > >MainContact. > > >I want to display (list) those CompanyNames which do not have a > >MainContact. > > Try using something like: > > SELECT CompanyName > FROM CoName > WHERE Not Exist (SELECT 1 >                                                                         FROM Contacts >                                                                         WHERE Contacts.CoID = CompanyName.CoID >                                                                                         And MainContact) > > -- > Marsh > MVP [MS Access] I'm getting a syntax error on "Not Exist". Did you mean something else?

Re: Show Companies with no MainContacts Marshall Barton <marshbarton[ at ]wowway.com> 12/12/2008 5:12:30 PM iamnu wrote: [Quoted Text] >On Dec 11, 5:26 pm, Marshall Barton <marshbar...[ at ]wowway.com> wrote: >> iamnu wrote: >> >CoName Table has a CompanyName field. >> >Contacts Table has the name of a Contact, and a MainContact field (yes/ >> >no). >> >So the SQL statement would include CompanyName, Contact and >> >MainContact. >> >> >I want to display (list) those CompanyNames which do not have a >> >MainContact. >> >> Try using something like: >> >> SELECT CompanyName >> FROM CoName >> WHERE Not Exist (SELECT 1 >>                                                                         FROM Contacts >>                                                                         WHERE Contacts.CoID = CompanyName.CoID >>                                                                                         And MainContact) >> > >I'm getting a syntax error on "Not Exist". Did you mean something >else? No, that's what I meant. Post your query. -- Marsh MVP [MS Access]

Re: Show Companies with no MainContacts iamnu <iamnubw[ at ]gmail.com> 12/12/2008 6:33:10 PM On Dec 12, 10:12 am, Marshall Barton <marshbar...[ at ]wowway.com> wrote: [Quoted Text] > iamnuwrote: > >On Dec 11, 5:26 pm, Marshall Barton <marshbar...[ at ]wowway.com> wrote: > >>iamnuwrote: > >> >CoName Table has a CompanyName field. > >> >Contacts Table has the name of a Contact, and a MainContact field (yes/ > >> >no). > >> >So the SQL statement would include CompanyName, Contact and > >> >MainContact. > > >> >I want to display (list) those CompanyNames which do not have a > >> >MainContact. > > >> Try using something like: > > >> SELECT CompanyName > >> FROM CoName > >> WHERE Not Exist (SELECT 1 > >>                                                                         FROM Contacts > >>                                                                         WHERE Contacts.CoID = CompanyName.CoID > >>                                                                                         And MainContact) > > >I'm getting a syntax error on "Not Exist".  Did you mean something > >else? > > No, that's what I meant.  Post your query. > > -- > Marsh > MVP [MS Access] Here is my query... SELECT CoNameID FROM tblAddresses WHERE Not Exist ((((SELECT 1 FROM tblContacts WHERE tblAddresses.AddrID = tblContacts.AddrID And MainContact)) =False));

Re: Show Companies with no MainContacts Marshall Barton <marshbarton[ at ]wowway.com> 12/13/2008 2:54:14 PM iamnu wrote: [Quoted Text] >SELECT CoNameID >FROM tblAddresses >WHERE Not Exist ((((SELECT 1 > FROM tblContacts >WHERE tblAddresses.AddrID = tblContacts.AddrID And MainContact)) >=False)); The parens are out of whack and the =False makes no sense. If you can't get the query design view to generate SQL more like what I posted, then skip the design view and do it all in SQL view. -- Marsh MVP [MS Access]