Distilling a Query?

[Craig]

I have two tables: one that lists Clients and another that lists Client
Projects.

The tables have a relationship between their respective Client fields.

Not every Client in the Client Table has a Project in the Client Projects
Table. Furthermore, many of the Clients who do have a Project have several
Projects in the Client Projects Table.

I have managed to create a query that lists only Clients who have a Project
in the Client Projects Table but each Client is listed as many times as it
has Projects.

My question is: how can I create a Query that lists all the Clients who have
a Project in the Client Projects Table but only list each Client only once?

Thanks

c

 

[Smartin]

I have two tables: one that lists Clients and another that lists Client
Projects.

The tables have a relationship between their respective Client fields.

Not every Client in the Client Table has a Project in the Client Projects
Table. Furthermore, many of the Clients who do have a Project have several
Projects in the Client Projects Table.

I have managed to create a query that lists only Clients who have a Project
in the Client Projects Table but each Client is listed as many times as it
has Projects.

My question is: how can I create a Query that lists all the Clients who have
a Project in the Client Projects Table but only list each Client only once?

Thanks

c

Does this give you a start?:

SELECT
C.ID
FROM
Clients C INNER JOIN ClientProjects P
ON
C.ID = P.ClientID
GROUP BY
C.ID
;

< or >

SELECT DISTINCT
C.ID
FROM
Clients INNER JOIN ClientProjects P
ON
C.ID = P.ClientID
;

 

[John W. Vinson]

My question is: how can I create a Query that lists all the Clients who have
a Project in the Client Projects Table but only list each Client only once?

Two ways:

- include only fields from the Client table, or uncheck the Show checkbox
under the Client Projects table, and set the query's Unique Values property to
Yes.

- Use a Subquery:

SELECT Clients.This, Clients.That, Clients.TheOther WHERE EXISTS
(SELECT ClientID FROM Projects WHERE Projects.ClientID = Clients.ClientID);


John W. Vinson [MVP]

 

[Craig]

All I needed was "Group By."

Simple.