[AccessD] Deleting Duplicates

Hi Gustav,
Do you have a quick sql where I can delete duplicate records leaving just
one behind.  The wizard shows me all but I need only the duplicate so that I
can delete it.

ta

Max



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: 16 May 2009 08:50
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Random ina query

Hi Stuart

That is true. The idea, however, with my function (listed previously) was to
provide a generic solution which you can plug in and will work under any
condition without having to alter any other code or macro.

/gustav


>>> stuart at lexacorp.com.pg 16-05-2009 01:07 >>>
You don't need to go to all that trouble. You can do it with a one line
function:

Function Rndm(FieldVal as Variant) as Single
   Rndm = Rnd()
End Function.

 - as long as you call Randomize at some stage before opening the query.

You can do so in a Startup() function invoked from an Autoexec macro, in the
On_open of 
your startup form or in an event procedure which opens the query.


-- 
Stuart

On 15 May 2009 at 12:57, jwcolby wrote:

> couldn't you do something like: (air code)
> 
> function Rndm()
> static blnInitialized as boolean
> 	if blnInitialized then
> 		rndm = Random()
> 	else
> 		Randomize
> 		Rndm = Random()
> 		blnInitialized = true
> 	endif
> end function
> 
> Then the randomize would occur in the same scope as the call to random().
> 
> Or does the fact that the function is called in a query somehow prevent
the static from sticking 
> around?  Or some other issue?
> 
> John W. Colby
> www.ColbyConsulting.com 
> 
> 
> Gustav Brock wrote:
> > Hi Jim
> > 
> > No. As I wrote to Susan (note the word "initially"):
> > 
> > <quote>
> > I had to deal with this and found out that the common method spread
around the web doesn't work.
> > 
> > The problem is that calling Randomize outside the query (by an external
function) takes place in a different scope than that of the query, thus
Rnd(..) is run in the query with the same seed initially which, of course,
will return the same sample or sequence of samples.
> > </quote>
> > 
> > The years passed by and Susan forgot all about it, so in 2007 she wrote
a tip without mentioning this trap:
> > 
> >   http://blogs.techrepublic.com.com/howdoi/?p=149 
> > 
> > She does discuss a UDF but, unfortunately, that is for another purpose.
> > 
> > /gustav
> > 
> > 
> >>>> jimdettman at verizon.net 15-05-2009 17:42:19 >>>
> > 
> >   I forgot to add a field to the call, but otherwise, that will work.
With
> > it defined as I posted, it would get called once at the start of the
query.
> > It needs to be Rnd([<fieldname>]).   If you don't have a non-zero
numeric
> > field, you can do Rnd(Len([<field name>])
> > 
> >   But other then that, it does work.
> > 
> > Jim.
> > 
> >   
> > 
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com 
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
> > Sent: Friday, May 15, 2009 11:25 AM
> > To: accessd at databaseadvisors.com 
> > Subject: Re: [AccessD] Random ina query
> > 
> > Hi Jim
> > 
> > Not you! I've posted that code many times here and 14 times at [you know
> > where] since 2004.
> > 
> > I even found my function here (with left out credits of course):
> > 
> >   http://www.vbforums.com/showthread.php?t=388276 
> > 
> > /gustav
> > 
> > 
> >>>> jimdettman at verizon.net 15-05-2009 17:07:48 >>>
> > 
> >   Yup.  Define a column as Rnd(1), sort on that column and set the TOP
> > predicate for the query for the number of records you want.


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