qry to show active clients showing duplicates

  • Thread starter Thread starter Nydia
  • Start date Start date
N

Nydia

I have a dba that keeps track of clients that we have in
our programs. One client that be in several programs. I
need to run a report that shows how many clients we
currently have open. When i run the query, if a client is
in 2 programs it's counting them 2. I want it show all
open clients only 1 x.

there are 2 tables, tblclient, which has client
information (address, phone ect.) the second table is
tblprogram which has the program name start date. the
relationship is one (tblclient) to many (tblprogram)

how do i get this to work? any help is greatly
appreciated :)
 
I have a dba that keeps track of clients that we have in
our programs. One client that be in several programs. I
need to run a report that shows how many clients we
currently have open. When i run the query, if a client is
in 2 programs it's counting them 2. I want it show all
open clients only 1 x.

there are 2 tables, tblclient, which has client
information (address, phone ect.) the second table is
tblprogram which has the program name start date. the
relationship is one (tblclient) to many (tblprogram)

how do i get this to work? any help is greatly
appreciated :)
Click to expand...

Please post the SQL view of your query. Without knowing what you're
doing it's hard to tell what you might do differently!

I *suspect* you should be able to use the Unique Values property of
the query and display only client fields, but without more information
it's hard to be sure.
 
here is the sql view

ELECT tblDescriptors.SSNO, tblDescriptors.Lastname,
tblDescriptors.Firstname, tblProgram.ProgramID,
tblProgram.TransTermDate
FROM tblDescriptors INNER JOIN tblProgram ON
tblDescriptors.IDNO = tblProgram.IDNO
WHERE (((tblProgram.TransTermDate) Is Null));

hope this helps
 
SELECT tblDescriptors.SSNO, tblDescriptors.Lastname,
tblDescriptors.Firstname, tblProgram.ProgramID,
tblProgram.TransTermDate
FROM tblDescriptors INNER JOIN tblProgram ON
tblDescriptors.IDNO = tblProgram.IDNO
WHERE (((tblProgram.TransTermDate) Is Null));
Click to expand...

Well... you're specifically asking to see the ProgramID in the query,
so if the person is in three programs you will see three records, one
for each ProgramID.

If you ONLY want to see one record from tblDescriptors, regardless of
how many programs the person is in, leave tblProgram.ProgramID and
tblProgram.TransTermDate out of the SELECT clause (by unchecking the
Show box) and set the Unique Values property of the query to TRUE. The
SQL would be

SELECT DISTINCT tblDescriptors.SSNO, tblDescriptors.Lastname,
tblDescriptors.Firstname
FROM tblDescriptors INNER JOIN tblProgram ON
tblDescriptors.IDNO = tblProgram.IDNO
WHERE (((tblProgram.TransTermDate) Is Null));
 
Back
Top