[AccessD] deduping qry results

You want only those records where the EPCID is equal to the parameter
Or if EPCID is NOT there then the ADCLASS = A 
BUT if they have the same address
  you want to keep the EPCID = Parameter over the A only

Well then try  
   SELECT tblExhibitorProducts.EPCID,
        qryApr08Grid.CompanyID,
        qryApr08Grid.CompanyName,
        qryApr08Grid.AdClass,
        qryApr08Grid.SGOn,
        MAX(IIF(tblExhibitorProducts.EPCID = 11, 1, qryApr08Grid.AdClass
="A", 2)) Whoisbetter 
   FROM tblExhibitorProducts
     INNER JOIN qryApr08Grid
     ON tblExhibitorProducts.CompanyID = qryApr08Grid.CompanyID
   WHERE (tblExhibitorProducts.EPCID=11)
      OR (qryApr08Grid.AdClass ="A") 
Group by         tblExhibitorProducts.EPCID,
        qryApr08Grid.CompanyID,
        qryApr08Grid.CompanyName,
        qryApr08Grid.AdClass,
        qryApr08Grid.SGOn ;



**************************************************
* Patricia O'Connor    
* Information Technology Specialist 3 (Programming) 
* OTDA - BDMA
* (W) mailto:Patricia.O'Connor at otda.state.ny.us
* (w) mailto:aa1160 at nysemail.state.ny.us
**************************************************
 

> 
--------------------------------------------------------
This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments.  Please notify the sender immediately by reply e-mail and delete the e-mail from your system. 


-----Original Message-----

> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> William Hindman
> Sent: Friday, March 28, 2008 12:52 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] deduping qry results
> 
> ...I'm need to eliminate CompanyID and CompanyName dupes that 
> result from the OR due to there being multiple identical 
> CompanyIDs with different EPCIDs ...I should be able to do 
> this with GroupBy and First but it keeps killing good records :(
> 
> William
> 
> ----- Original Message -----
> From: "O'Connor, Patricia (OTDA)" <Patricia.O'Connor at otda.state.ny.us>
> To: "Access Developers discussion and problem solving" 
> <accessd at databaseadvisors.com>
> Sent: Friday, March 28, 2008 12:37 PM
> Subject: Re: [AccessD] deduping qry results
> 
> 
> > William
> >
> > Could you show an example of what you are trying to avoid.
> > Or be a bit more specific?
> >
> > What element(S) are you trying not to get a duplicate of ?
> >
> >  SELECT DISTINCTROW
> >       tblExhibitorProducts.EPCID,
> >       qryApr08Grid.CompanyID,
> >       qryApr08Grid.CompanyName,
> >       qryApr08Grid.AdClass,
> >       qryApr08Grid.SGOn

> >  FROM tblExhibitorProducts
> >    INNER JOIN qryApr08Grid
> >    ON tblExhibitorProducts.CompanyID = qryApr08Grid.CompanyID
> >  WHERE (tblExhibitorProducts.EPCID=11)
> >     OR (qryApr08Grid.AdClass ="A") ;
> >
> > I have an idea but need to see if I am right
> >
> > **************************************************
> > * Patricia O'Connor
> > * Information Technology Specialist 3 (Programming)
> > * OTDA - BDMA
> > * (W) mailto:Patricia.O'Connor at otda.state.ny.us
> > * (w) mailto:aa1160 at nysemail.state.ny.us
> > **************************************************
> >
> >
>