I think you are making this too difficult. I think you can do it in one select joining the 3 tables together. Your second select is definaty wrong right where you say you have a problem. You can't compare a count to an ID, it will always fail.
I think something like this will work: select count (dist ypid) into vcount from yp t1, services t2, adminissions t3 + where t1.ypid = t2.ypid and t1.ypid = t3.ypid and td.dserve between .gvdate1 and .gvdate2 ....+ group by ypid having count * > 1 Troy ===== Original Message from [EMAIL PROTECTED] at 3/27/02 1:56 pm >I have a parent table of clients with a primary key of ypid. There is a >child table of client admissions with a foreign key of ypid, and a second >child table of services also with a foreign key of ypid. I'm trying to >count the number of clients appearing in the admission table more than once >by the program value for individuals receiving services in a date range from >the service table. > >This gives me the initial value (total clients): > >SELECT COUNT(*) INTO VCOUNTALL INDICATOR vctallind + >FROM YP WHERE YPID IN (SELECT YPID FROM SERVICE WHERE COSTCENT = .GVPROG + >AND DSERV BETWEEN .GVDATE1 AND .GVDATE2) > >This is how I'm asking the second part: > >SELECT COUNT(ypid) INTO VCOUNT2 INDICATOR VINDmult + >FROM yp WHERE ypid IN (SELECT ypid FROM service + > WHERE costcent = .gvprog AND dserv BETWEEN .gvdate1 AND .gvdate2) + > --This is where I think my query falls down. > and ypid IN (SELECT count(ypid) FROM ev + > HAVING COUNT(ypid) > 1 ) > >My goal being to subtract vcount2 from vcountall to get the number of >individuals admitted more than once in a period. The problem is that the >second query always returns 0. > >Am I missing a third step? How can I identify the record count for a parent >table where the FK appears more than once in the Admissions table (EV) with >services in a date range in the Service table? > >tia > >Dave >[EMAIL PROTECTED] > > > > >================================================ >TO SEE MESSAGE POSTING GUIDELINES: >Send a plain text email to [EMAIL PROTECTED] >In the message body, put just two words: INTRO rbase-l >================================================ >TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] >In the message body, put just two words: UNSUBSCRIBE rbase-l >================================================ >TO SEARCH ARCHIVES: >http://www.mail-archive.com/rbase-l%40sonetmail.com/ ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
