Help with a query

[Keven Denen]

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...

 

[Gina Whipp]

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

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...

 

[John Spencer]

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