[AccessD] finding duplicates among different departmental programs

Thanks Scott. This looks like it may do the trick. I may have to create
several of these, because there are 14 rows in this column; all asking
me to break it down a little more (i.e. how many are: over 75, frail,
vets, white, asian, black, etc.). However, once I get to this point and
find an accurrate number for the total, the rest is usually much
simpler.

I could not, however use your code exact; the count wouldn't work, so I
made a separate query that counted this union query. My query ended up
being:

SELECT [xx-test1].kClientID
FROM [xx-test1]

UNION SELECT [xx-test2].kClientID
FROM [xx-test2];


And then another:

SELECT Count([qryTitleIII-New].kClientID) AS CountOfkClientID
FROM [qryTitleIII-New];

Thanks again...it turned out pretty simple!

John W Clark


>>> marcus at tsstech.com 7/30/2004 11:55:51 AM >>>
I miss understood your question. Couldn't you just union the two
queries and count by the person? 

For example:

SELECT Count(Person) AS Total_Number_Of_People
FROM
[SELECT Person FROM query1
UNION
SELECT Person FROM query2].

I'm tying in the other thread (Undocumented Access SQL syntax).

Scott Marcus


-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Clark
Sent: Friday, July 30, 2004 10:58 AM
To: accessd at databaseadvisors.com 
Subject: [AccessD] finding duplicates among different departmental
programs


I created a program, about a year and a half ago, for an agency that
deals with providing programs for the elderly. This agency gets its
funding from many different sources and a single source may fund
multiple programs. To track all of this we need to provide many stats.

When this was written, there wasn't a concern about a person being in
multiple programs, although I warned about it. Now, because there
state
reporting has changed, there may be a concern. The state is kicking
back
their reports because one of their funding counts is greater than the
total amount of participants; there are 1236 people enrolled with the
agency, but one group of programs is showing a count of 1257. I think
the fact that they are even close is a coincidence...I took a sample
of
13 members and all but four were in at least 2 of the programs, and
actually my queries, out of necessity, each handle two programs (there
are four for this group) and each one blocks out its own 'duplicates',
so it is very conceivable that many of these people are in all four of
the groups.

Question:

qryOne looks for people in Prog1 OR Prog2 (count = 809)
qryTwo looks for people in ProgA OR ProgB (count = 410)

I need a total count of those is qryOne OR qryTwo 

*** I don't want those that are in both counted twice, which is what a
count of 1219 (809 + 410) 
will give me.

I'm thinking that I need to use one of these queries into another
query...???

Thanks in advance for any help!
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com 


-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com