[AccessD] Query giving different results each time

 Mark,

If the backend is Access, you are going to have to do 2 queries because
Access does not allow multiple fields in a subselect. I am not sure
whether SQL SERVER
Will allow multiple.

In ORACLE the following will give you what you want - I have to do
something similar every day

SELECT B.profile_cm_seq,
        B.effdt,
        B.bo_id,
        B.primary_ind,
        B.phone
FROM tblPRS_Pending_PHONE B
WHERE (B.bo_id,B.effdt) 
		IN(SELECT a.bo_id, Max(a.effdt)
               FROM tblPRS_Pending_PHONE a
               WHERE a.primary_ind="Y"
		   GROUP BY a.bo_id)
AND B.primary_ind="Y" 

---

With Access
QUERY 1:
	SELECT a.bo_id, Max(a.effdt) as MAX_DT
      FROM tblPRS_Pending_PHONE a
      WHERE a.primary_ind="Y"
	GROUP BY a.bo_id

QUERY 2: 
SELECT B.profile_cm_seq,
        B.effdt,
        B.bo_id,
        B.primary_ind,
        B.phone
FROM tblPRS_Pending_PHONE as B 
INNER JOIN QUERY1 as Q1 
on B.bo_id = q1.BO_ID AND b.EffDt = q1.MAX_DT
WHERE b.PRIMARY_IND = "Y"

----
Another ORACLE OPTION
SELECT B.profile_cm_seq,
        B.effdt,
        B.bo_id,
        B.primary_ind,
        B.phone
FROM tblPRS_Pending_PHONE B,
     (SELECT a.bo_id, Max(a.effdt) MaxDt
      FROM tblPRS_Pending_PHONE a
      WHERE a.primary_ind="Y"
	GROUP BY a.bo_id) Q1
WHERE (B.bo_id = q1.BO_ID AND b.effdt = q1.MAX_DT) 
	AND B.primary_ind = "Y"
----

Hope this helps
Patti 

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> Mark A Matte
> Sent: Wednesday, July 28, 2004 12:11 PM
> To: accessd at databaseadvisors.com
> Subject: RE: [AccessD] Query giving different results each time
> 
> wow...ok...I still don't understand why I would get different 
> results from the same sql...but I'm ok with that for now...
> 
> Scott, the sql below and the 2 query approach did give 
> different results(about 300 difference).
> 
> The sql below did give the number I expected...but as 
> Patricia pointed out...it did not always give the max of the 
> date...although it provided the correct number of 
> records...they were not always the correct ones...So I tried 
> to change the 'last' on the effdt to 'max'...but the results 
> do vary...I get the last phone number...but the max 
> date...with this approach the date and phone don't 
> necessarily correspond anymore.
> 
> So close...I can feel it.
> 
> Thanks,
> 
> Mark
> 
> 
> 
>