Eliminating Multiple Results in Query

[magmike]

I have a table related to the master table called Status. One company
can have multiple statuses, such as Quoting, Can Bid Next Year, Big
(1000+ employees), Decision Made At Another Location, etc. There is a
Status Types table as well, so each status type has it's own ID
(StatusTypeID).

I created a query to display companies that either have the DROP
status (meaning the decision maker invited the sales rep to stop by
sometime without an appointment) or VISIT (meaning the sales rep
thinks he needs to stop by sometime (because he can't get the guy on
the phone or something). In reality, no company should have both - but
sales people aren't the most organized users. So, when a company does
have both status codes linked to them, there are two results in the
query.

How can I make a query that searches for both codes, but only displays
a company once? Is there a creative way to have it show both codes (if
applicable) in one field so the sales guy knows he marked it with
both?

Thanks in advance!
magmike

 

[pietlinden]

I have a table related to the master table called Status. One company
can have multiple statuses, such as Quoting, Can Bid Next Year, Big
(1000+ employees), Decision Made At Another Location, etc. There is a
Status Types table as well, so each status type has it's own ID
(StatusTypeID).

I created a query to display companies that either have the DROP
status (meaning the decision maker invited the sales rep to stop by
sometime without an appointment) or VISIT (meaning the sales rep
thinks he needs to stop by sometime (because he can't get the guy on
the phone or something). In reality, no company should have both - but
sales people aren't the most organized users. So, when a company does
have both status codes linked to them, there are two results in the
query.

How can I make a query that searches for both codes, but only displays
a company once? Is there a creative way to have it show both codes (if
applicable) in one field so the sales guy knows he marked it with
both?

Thanks in advance!
magmike

use EXISTS in your subquery instead...
SELECT ...
FROM Company
WHERE EXISTS (SELECT CompanyID FROM Invoice...)

then you should only get a single record for each company with
invoices, regardless of how many there are.