If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with a query
Trying to figure this query out.
I have two tables; Company and Contact. 1 Company can have many contacts, but only 1 primary contact marked by a yes/no field (Pri?) in the contact table. The primary contact is the decision maker, so a company could have contacts, without having one of them set as primary. The query I have so far. SELECT Company.Name, Contact.Name, Contact.[Pri?] FROM Company LEFT JOIN Contact ON Company.AcctID = Contact.AcctID WHERE Contact.[Pri?] Is Null OR Contact.[Pri?]=True; This gives me all Companies that have a primary contact set and all companies that don't have any contact at all. What I don't get is companies that have contacts but no decision maker. What I want is a record for every company, including the decision maker if there is one, but a blank field for contact if there is no primary contact. Is this possible with a single query? Am I even making sense? I've been thinking about this for too long and can't tell if I'm making sense anymore... |
#2
|
|||
|
|||
Help with a query
Keven,
You make sense... The problem with your request is that there is nothing to identify just one contact from the Companies that are not null or Pri? is not set to True. So you need to come up with a *trigger* to pull only one Contact from the Companies table where there are many Contacts to one Company. And when I think about it, it would require another field to be able to tag a single contact and then ensure that field is fillled in. Perhaps something on the form that says... No Primary Contact, you need to select a Main Contact... or something to that effext. -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Keven Denen" wrote in message ... Trying to figure this query out. I have two tables; Company and Contact. 1 Company can have many contacts, but only 1 primary contact marked by a yes/no field (Pri?) in the contact table. The primary contact is the decision maker, so a company could have contacts, without having one of them set as primary. The query I have so far. SELECT Company.Name, Contact.Name, Contact.[Pri?] FROM Company LEFT JOIN Contact ON Company.AcctID = Contact.AcctID WHERE Contact.[Pri?] Is Null OR Contact.[Pri?]=True; This gives me all Companies that have a primary contact set and all companies that don't have any contact at all. What I don't get is companies that have contacts but no decision maker. What I want is a record for every company, including the decision maker if there is one, but a blank field for contact if there is no primary contact. Is this possible with a single query? Am I even making sense? I've been thinking about this for too long and can't tell if I'm making sense anymore... |
#3
|
|||
|
|||
Help with a query
You could do this in ONE query if your table and field names followed the
naming guidelines - ONLY letters, numbers, and the underscore character. Unfortunately you have a field named "PRI?". So to do this you are going to need TWO queries. First query saved as qPriorityContacts SELECT Contact.AcctID, Contact.Name FROM Contacts WHERE [Pri?] = True SELECT Company.Name, Q.Name as PriorityContact FROM Company LEFT JOIN qPriorityContacts as Q ON Company.AcctID = Q.AcctID IF PRI? were Pri then your query would look like SELECT Company.Name, Q.Name as PriorityContact FROM Company LEFT JOIN (SELECT Contact.AcctID, Contact.Name FROM Contacts WHERE Contact.Pri = True) as Q ON Company.AcctID = Q.AcctID John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Keven Denen wrote: Trying to figure this query out. I have two tables; Company and Contact. 1 Company can have many contacts, but only 1 primary contact marked by a yes/no field (Pri?) in the contact table. The primary contact is the decision maker, so a company could have contacts, without having one of them set as primary. The query I have so far. SELECT Company.Name, Contact.Name, Contact.[Pri?] FROM Company LEFT JOIN Contact ON Company.AcctID = Contact.AcctID WHERE Contact.[Pri?] Is Null OR Contact.[Pri?]=True; This gives me all Companies that have a primary contact set and all companies that don't have any contact at all. What I don't get is companies that have contacts but no decision maker. What I want is a record for every company, including the decision maker if there is one, but a blank field for contact if there is no primary contact. Is this possible with a single query? Am I even making sense? I've been thinking about this for too long and can't tell if I'm making sense anymore... |
Thread Tools | |
Display Modes | |
|
|