[AccessD] deduping qry results

William,

I did this recently in SQL Server...I'm sure it can be done in VBA...I have not tried in an Access query.  Hopefully the concept will give you an alternative.

Good Luck,

Mark A. Matte


 **********************BEGIN************************
--By Mark A. Matte 3/18/2008
declare @CaseID decimal(15,0), at NoteNum int, at NoteCount int
 declare MyCursor cursor
 for Select case_id,note_seq_nbr,count(*) as CT
 from tblCaseNote_temp
 group by case_id,note_seq_nbr
 having count(*)>1
 order by case_id,note_seq_nbr
 open mycursor
 fetch next from mycursor
 into @CaseID, at NoteNum, at NoteCount
 while (@@fetch_status =0)

 Begin
 delete top(@noteCount-1) from tblCaseNote_Temp where
 case_id=@CaseID and note_seq_nbr=@noteNum
 fetch next from mycursor
 into @CaseID, at NoteNum, at NoteCount
 End
 Close mycursor
 deallocate mycursor
 ***********************END*************************


> From: wdhindman at dejpolsystems.com
> To: accessd at databaseadvisors.com
> Date: Fri, 28 Mar 2008 13:23:59 -0400
> Subject: Re: [AccessD] deduping qry results
>
> ...not quite Charlotte ...the EPCID reflects product categories that an
> Exhibtor sells ...I need to show all Exhibitors (CompanyID) for a given show
> (qryApr08Grid) who sell a selected product (EPCID) ...watch out now ...plus
> ANY Exhibitor from the qryApr08Grid who has paid a sponsorshp fee (AdClass
> =A) whether or not they sell that product.
>
> ...so far, I'm not getting there ...theoretically I should be able to use
> Totals: GroupBy and First to do this ...but it keeps dropping valid records
> when I do that ...so far this has me stumped.
>
> William
>
> ----- Original Message -----
> From: "Charlotte Foust" 
> To: "Access Developers discussion and problem solving"
> 
> Sent: Friday, March 28, 2008 12:53 PM
> Subject: Re: [AccessD] deduping qry results
>
>
>> William, if you're only joining tblExhibitorProducts in order to filter
>> aryApr08Grid.CompanyID, Why not use an In and a subquery on
>> tblExhibitorProducts.CompanyID filtered by the EPCID entered instead of
>> an inner join?
>>
>> Charlotte Foust
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William
>> Hindman
>> Sent: Friday, March 28, 2008 8:43 AM
>> To: Access Developers discussion and problem solving
>> Subject: [AccessD] deduping qry results
>>
>> ...senior moment ...posted on another thread by mistake :(
>>
>> ...given the following query:
>>
>> 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"));
>>
>> ...how can I avoid duplicates in the OR results?
>> ...I've tried totals and uniuqe values/records but nothing produces
>> consistent results ...help!
>>
>> William
>> --
>> 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
>>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

_________________________________________________________________
Watch “Cause Effect,” a show about real people making a real difference.  Learn more.
http://im.live.com/Messenger/IM/MTV/?source=text_watchcause