How do I test for a prior use? 
Author Message
 How do I test for a prior use?

Going batty,

I need to test if a number is entered twice in inported
data.
If it has been enter prior then I need to flag it, not
delete or exclude it just flag it.
The table would look like this:

Num     | Flag
1           |
2           |
3           |
1           | Used
4           |
2           | Used
5           |
1           | Used

I tried using a Seek but that always found itself and
flagged it.
I think I am missing something.

Thanks for the help.

--
Have a nice life.

"Progress is just a faster road to the end." me



Tue, 25 Jul 2000 03:00:00 GMT  
 How do I test for a prior use?

Hi,

I'd posted a possible answer to a somewhat similar question recently.  Am
including the original post and code.  Hope it may help

Dim db As Database, rs As Recordset
Dim rs2 As Recordset
Dim i As Long, lngCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("table1", dbOpenDynaset)
Set rs2 = db.OpenRecordset("table1", dbOpenDynaset)
    rs.MoveLast
    rs2.MoveLast
    rs.MoveFirst
    rs2.MoveFirst
    lngCount = rs.RecordCount
    Do While Not rs.EOF
        For i = 0 To lngCount - 1
            If rs2!sumfield = rs!sumfield Then
                If rs!ThirdField < rs2!ThirdField Then
                    rs.Edit
                        rs!sumfield = rs!sumfield - 2
                    rs.Update
                ElseIf rs!ThirdField > rs2!ThirdField Then
                    rs2.Edit
                        rs2!sumfield = rs2!sumfield - 2
                    rs2.Update
                End If
            End If
            rs2.MoveNext
        Next i
        rs.MoveNext
        rs2.MoveFirst
    Loop

I haven't tested this but am quite sure that the logic is right.

HTH

--
Dev Ashish
---------------

:I have a db which has a sum field.   When two or more records have the same
:value in this sum field(record 1 sumfield=44 and record 2 sumfield=44) I
:need a peice of code which will lookup the value of a third field,
determine
:which record, 1 or 2, has a lesser value in the third field and then
:automatically reduce the value in that record's sum field by a value of 2.
:Can anyone help.
:
:Thanks
:
:Ross
:
:

--
Just my $0.001
Dev Ashish
---------------



Tue, 25 Jul 2000 03:00:00 GMT  
 How do I test for a prior use?

Dev, thanks for the reply, your code looks like it would
work.
In fact I had saved that posting as a reference.

Your code compares two recordsets from two tables.

Quote:
>Snip

    Do While Not rs.EOF
        For i = 0 To lngCount - 1
            If rs2!sumfield = rs!sumfield Then

Quote:
>End Snip

I have only one table. Data is inported in if a number is
repeated I need to Flag it.
It would be simple if I did not want to retain this
duplicate number but I do, it has to be dealt with not
ignored.
My current thinking is to try to send each Num to a temp
table before it is validated.
With the temp table not allowing duplicates. Then seeking
this number in the temp table and if found setting
FLAG "USED" (Yes/No)
I keep getting a "Empty RecordSet" error. There must be a
way but I'm missing it.
I'd post my code but then I'd really be embarrassed :-(

Num  | FLAG
1        |
2        |
3        |
1        |
4        |
2        |
5        |
1        |

It needs to look like this

Num  | FLAG
1        |
2        |
3        |
1        | Used
4        |
2        | Used
5        |
1        | Used

--
Have a nice life.

"Progress is just a faster road to the end." me

Quote:
>Hi,

>I'd posted a possible answer to a somewhat similar question
recently.  Am
>including the original post and code.  Hope it may help

>Snip

    Do While Not rs.EOF
        For i = 0 To lngCount - 1
            If rs2!sumfield = rs!sumfield Then


Tue, 25 Jul 2000 03:00:00 GMT  
 How do I test for a prior use?

Hi,

:Your code compares two recordsets from two tables.
Nope, it compares two recordsets from _one_ table.
Set rs = db.OpenRecordset("table1", dbOpenDynaset)
Set rs2 = db.OpenRecordset("table1", dbOpenDynaset)

Essentially what you need to do is something like this. (remember, we're
dealing with teh same table)
        For i = 0 To lngCount - 1
            If rs2!sumfield = rs!sumfield Then
                    rs2.Edit
                        rs2!Flag= "Used"
                    rs2.Update
            End If
            rs2.MoveNext
        Next i

That should do the trick.

HTH
--
Just my $.001
Dev Ashish
---------------

:Dev, thanks for the reply, your code looks like it would
:work.
:In fact I had saved that posting as a reference.
:
:Your code compares two recordsets from two tables.
:
:>Snip
:    Do While Not rs.EOF
:        For i = 0 To lngCount - 1
:            If rs2!sumfield = rs!sumfield Then
:>End Snip
:<<<snipped>>>



Tue, 25 Jul 2000 03:00:00 GMT  
 How do I test for a prior use?

Dev, thanks a lot for the help.
The problem is again that the Num finds itself and FLAG is
set to "Used"

Num  | FLAG
1        |
2        |
3        |
1        |
4        |
2        |

results in
Num  | FLAG
1        | Used
2        | Used
3        | Used
1        | Used
4        | Used
2        | Used

what I need is
Num  | FLAG
1        |
2        |
3        |
1        | Used
4        |
2        | Used

I did stick this together correctly, didn't I?

Dim db As Database, rs As Recordset
Dim rs2 As Recordset
Dim i As Long, lngCount As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("table1", dbOpenDynaset)
Set rs2 = db.OpenRecordset("table1", dbOpenDynaset)
    rs.MoveLast
    rs2.MoveLast
    rs.MoveFirst
    rs2.MoveFirst
    lngCount = rs.RecordCount
    Do While Not rs.EOF
        For i = 0 To lngCount - 1
            If rs2!Num = rs!Num Then
                    rs2.Edit
                        rs2!Flag= "Used"
                    rs2.Update
            End If
            rs2.MoveNext
        Next i
        rs.MoveNext
        rs2.MoveFirst
    Loop



Tue, 25 Jul 2000 03:00:00 GMT  
 How do I test for a prior use?

Hi,

my sincere apologies.  As it's become common recently, yet again I didn't
double-check the question and code while posting.

            If rs2!Num = rs!Num Then
                    rs2.Edit
                        rs2!Flag= "Used"
                    rs2.Update
            End If

You're right.  The above will mark the current record as Used too.  What you
need is a secondary check in the If condition.

Now, if you have another field in the table, let's say an Autonumber field,
you can modify the If condifion as
If (rs2!Num = rs!Num) AND (rs2!SomeID <> rs!SomeID AND rs2!Flag <> "Used" )
then

if you don't have this unique id field, try playing with something like this
If rs2!Num = rs!Num AND (rs2!AbsolutePosition <> rs!AbsolutePosition AND
rs2!Flag<>"Used" )Then

I hope I'm right this time.  This code should make sure that (a) you're not
on the same record and (b) the current record in rs2 is not yet flagged
"Used"....

HTH
--
Just my $.001
Dev Ashish
---------------

:Dev, thanks a lot for the help.
:The problem is again that the Num finds itself and FLAG is
:set to "Used"
:
:Num  | FLAG
:1        |
:2        |
:3        |
:1        |
:4        |
:2        |
:
:results in
:Num  | FLAG
:1        | Used
:2        | Used
:3        | Used
:1        | Used
:4        | Used
:2        | Used
:



Tue, 25 Jul 2000 03:00:00 GMT  
 How do I test for a prior use?

I would go with your temp table idea, but instead of using DAO use queries.

Query 1 - Update query
Join your existing table and your temp table on the field you want to check
for duplicates and add the Flag field from the temp table, change it to an
update query and put in true for the update value on the Flag field.  This
will then set the field to true only on the duplicate records in the
temporary table.

Query 2 - Append query
Then do an append from the temp table to the existing table

Query 3 - Delete query
Then clear down the temp table with a delete query.

In the following I've used tblExists as the existing table and tblTemp as
the temporary table, I've used the field names you have used below.

Query1 SQL is as follows
UPDATE tblExists INNER JOIN tblTemp ON tblExists.Num = tblTemp.Num SET
tblTemp.Flag = True;

Query 2 SQl as follows
INSERT INTO tblExists SELECT tblTemp.* FROM tblTemp;

Query 3 SQL as follows
DELETE tblTemp.* FROM tblTemp;

and that should do it.

Quote:

>Dev, thanks for the reply, your code looks like it would
>work.
>In fact I had saved that posting as a reference.

>Your code compares two recordsets from two tables.

>>Snip
>    Do While Not rs.EOF
>        For i = 0 To lngCount - 1
>            If rs2!sumfield = rs!sumfield Then
>>End Snip

>I have only one table. Data is inported in if a number is
>repeated I need to Flag it.
>It would be simple if I did not want to retain this
>duplicate number but I do, it has to be dealt with not
>ignored.
>My current thinking is to try to send each Num to a temp
>table before it is validated.
>With the temp table not allowing duplicates. Then seeking
>this number in the temp table and if found setting
>FLAG "USED" (Yes/No)
>I keep getting a "Empty RecordSet" error. There must be a
>way but I'm missing it.
>I'd post my code but then I'd really be embarrassed :-(

>Num  | FLAG
>1        |
>2        |
>3        |
>1        |
>4        |
>2        |
>5        |
>1        |

>It needs to look like this

>Num  | FLAG
>1        |
>2        |
>3        |
>1        | Used
>4        |
>2        | Used
>5        |
>1        | Used

>--
>Have a nice life.

>"Progress is just a faster road to the end." me


>>Hi,

>>I'd posted a possible answer to a somewhat similar question
>recently.  Am
>>including the original post and code.  Hope it may help

>>Snip

>    Do While Not rs.EOF
>        For i = 0 To lngCount - 1
>            If rs2!sumfield = rs!sumfield Then



Tue, 25 Jul 2000 03:00:00 GMT  
 How do I test for a prior use?

Dev, thanks again for your help.
I think there has to be a better method than the comparison
methods we have tried so far.
I have not finished testing with a third field check but it
looks like something like that would work.
The problem is I did a little math and it appears using the
current comparison method it would
take N x (N+1) itinerations to check each Num.
It seems I need to sort the Num (column) and then check any
entry that was out of order.

Num  | FLAG
1        |
2        |
3        |
1        |
4        |
2        |
5        |

Sorted this would be: If Num(Position) <> Num(Prior_Position
+ 1) excluding First Num(Position) _
                                     OutOfOrder = Yes
Num  | FLAG     | OutOfOrder
1        |                |
1        |                | Yes
2        |                |
2        |                | Yes
3        |                |
4        |                |
5        |                |

Then If Num(Position)(OutOfOrder =Yes) = Num(Position
1,2,3,4,5) FLAG = "USED"

This would potentially still be a lot of testing but its'
potential  versus known.

So where are the Math Teachers when you kneed them, usually
in the Programming Groups someone
will pipe in with "Back in '67 when I was doing my Doctrinal
Thesis on the Vassectional  Propagation of Slime
Vissectitudes  my prof always lectured me on Pigmillions
Proper Methods of Number searches and he could prove that a
Bubble Sort was sufficient to find and Number between -32654
and 32654. Where upon some one else would come in with the
most authoritative advocacy of using B-Trees and all of a
sudden their would be a holy war going with some really
great learning, of course most of it was over my head but I
was nice to get exposed to the "Higher Learning"

In conclusion does anyone have their favorite method for
finding duplicate numbers?

The list I will be searching may be between 300 to 2000
entries. Therefore I need to do it in the fewest moves.
Thats 90,300 to 40,002,000 steps. There may not be any
duplicates or possible 1 or 2.

Am I on the right track?
I can add whatever fields are needed.
Oh yeah Access97

Thanks again Dev, your help is always most appreciated.
--
Have a nice life.

"Progress is just a faster road to the end." me

Quote:
>Hi,

>my sincere apologies.  As it's become common recently, yet
again I didn't
>double-check the question and code while posting.

>            If rs2!Num = rs!Num Then
>                    rs2.Edit
>                        rs2!Flag= "Used"
>                    rs2.Update
>            End If

>You're right.  The above will mark the current record as
Used too.  What you
>need is a secondary check in the If condition.

>Now, if you have another field in the table, let's say an
Autonumber field,
>you can modify the If condifion as
>If (rs2!Num = rs!Num) AND (rs2!SomeID <> rs!SomeID AND

rs2!Flag <> "Used" )
Quote:
>then

>if you don't have this unique id field, try playing with
something like this
>If rs2!Num = rs!Num AND (rs2!AbsolutePosition <>

rs!AbsolutePosition AND

- Show quoted text -

Quote:
>rs2!Flag<>"Used" )Then

>I hope I'm right this time.  This code should make sure
that (a) you're not
>on the same record and (b) the current record in rs2 is not
yet flagged
>"Used"....

>HTH
>--
>Just my $.001
>Dev Ashish
>---------------


>:Dev, thanks a lot for the help.
>:The problem is again that the Num finds itself and FLAG is
>:set to "Used"
>:
>:Num  | FLAG
>:1        |
>:2        |
>:3        |
>:1        |
>:4        |
>:2        |
>:
>:results in
>:Num  | FLAG
>:1        | Used
>:2        | Used
>:3        | Used
>:1        | Used
>:4        | Used
>:2        | Used
>:



Wed, 26 Jul 2000 03:00:00 GMT  
 How do I test for a prior use?

Dev, thanks again for your help.
I think there has to be a better method than the comparison
methods we have tried so far.
I have not finished testing with a third field check but it
looks like something like that would work.
The problem is I did a little math and it appears using the
current comparison method it would
take N x (N+1) itinerations to check each Num.
It seems I need to sort the Num (column) and then check any
entry that was out of order.

Num  | FLAG
1        |
2        |
3        |
1        |
4        |
2        |
5        |

Sorted this would be: If Num(Position) <> Num(Prior_Position
+ 1) excluding First Num(Position) _
                                     OutOfOrder = Yes
Num  | FLAG     | OutOfOrder
1        |                |
1        |                | Yes
2        |                |
2        |                | Yes
3        |                |
4        |                |
5        |                |

Then If Num(Position)(OutOfOrder =Yes) = Num(Position
1,2,3,4,5) FLAG = "USED"

This would potentially still be a lot of testing but its'
potential  versus known.

So where are the Math Teachers when you kneed them, usually
in the Programming Groups someone
will pipe in with "Back in '67 when I was doing my Doctrinal
Thesis on the Vassectional  Propagation of Slime
Vissectitudes  my prof always lectured me on Pigmillions
Proper Methods of Number searches and he could prove that a
Bubble Sort was sufficient to find and Number between -32654
and 32654. Where upon some one else would come in with the
most authoritative advocacy of using B-Trees and all of a
sudden their would be a holy war going with some really
great learning, of course most of it was over my head but I
was nice to get exposed to the "Higher Learning"

In conclusion does anyone have their favorite method for
finding duplicate numbers?

The list I will be searching may be between 300 to 2000
entries. Therefore I need to do it in the fewest moves.
Thats 90,300 to 40,002,000 steps. There may not be any
duplicates or possible 1 or 2.

Am I on the right track?
I can add whatever fields are needed.
Oh yeah Access97

Thanks again Dev, your help is always most appreciated.
--
Have a nice life.

"Progress is just a faster road to the end." me

Quote:
>Hi,

>my sincere apologies.  As it's become common recently, yet
again I didn't
>double-check the question and code while posting.

>            If rs2!Num = rs!Num Then
>                    rs2.Edit
>                        rs2!Flag= "Used"
>                    rs2.Update
>            End If

>You're right.  The above will mark the current record as
Used too.  What you
>need is a secondary check in the If condition.

>Now, if you have another field in the table, let's say an
Autonumber field,
>you can modify the If condifion as
>If (rs2!Num = rs!Num) AND (rs2!SomeID <> rs!SomeID AND

rs2!Flag <> "Used" )
Quote:
>then

>if you don't have this unique id field, try playing with
something like this
>If rs2!Num = rs!Num AND (rs2!AbsolutePosition <>

rs!AbsolutePosition AND

- Show quoted text -

Quote:
>rs2!Flag<>"Used" )Then

>I hope I'm right this time.  This code should make sure
that (a) you're not
>on the same record and (b) the current record in rs2 is not
yet flagged
>"Used"....

>HTH
>--
>Just my $.001
>Dev Ashish
>---------------


>:Dev, thanks a lot for the help.
>:The problem is again that the Num finds itself and FLAG is
>:set to "Used"
>:
>:Num  | FLAG
>:1        |
>:2        |
>:3        |
>:1        |
>:4        |
>:2        |
>:
>:results in
>:Num  | FLAG
>:1        | Used
>:2        | Used
>:3        | Used
>:1        | Used
>:4        | Used
>:2        | Used
>:



Wed, 26 Jul 2000 03:00:00 GMT  
 How do I test for a prior use?

Hi,

Yup, to me at least it seems like it would work.  I didn't try the actual
comparison computation but tried to visualize that on a sorted array. (btw I
include a ShellSort method which should be pretty fast. Hope it may help)
Defnitely, this would be faster than FindNext or even Seek since you'll be
going thru the entire table in each pass.

In the past, whenever I had to do a Find within an array (for some
statistical stuff), I tended to sort the array first.  Not for performance,
but it was easy for me to visualize how the find would actually reference
the individual array positions etc.  Of course,  in course of  time, I
realized that it _is_ the preferred thing to do just on the basis of
performance itself.

Try using the ShellSort the following way
Sub test()
Dim a(1 To 10) As Integer
Dim i As Integer
a(1) = 22
a(2) = 12
a(3) = 3
a(4) = 22
a(5) = 1
a(6) = 23
a(7) = 32
a(8) = 3
a(9) = 9
a(10) = 1
    ShellSort a()
    For i = 1 To 10
        Debug.Print a(i)
    Next i
End Sub

Here's the Sort method....
Sub ShellSort(a() As Integer)
'
' Very fast sort: 2n Log n comparisons
'
' Calling convention:
'   Redim A(1 To 20) as Integer
'   ShellSort A()
'
Dim i As Integer, J As Integer, Low As Integer, Hi As Integer
Dim Temp As Integer, Swapped As Integer
  Low = LBound(a)
  Hi = UBound(a)
  J = (Hi - Low + 1) \ 2
  Do While J > 0
    For i = Low To Hi - J
      If a(i) > a(i + J) Then
        Temp = a(i)
        a(i) = a(i + J)
        a(i + J) = Temp
      End If
    Next i
    For i = Hi - J To Low Step -1
      If a(i) > a(i + J) Then
        Temp = a(i)
        a(i) = a(i + J)
        a(i + J) = Temp
      End If
    Next i
    J = J \ 2
  Loop
End Sub

HTH

--
Just my $.001
Dev Ashish
---------------



Wed, 26 Jul 2000 03:00:00 GMT  
 How do I test for a prior use?

Thanks Dev and Terry for the help.
For my first three years of using Access, I did everything
with Queries and a occasional Macro.
Then I started using Recordsets in code, and didn't use
queries except for the most basic needs.
I think I fell in that old when all you have is a hammer
everything looks like a nail trap.
Anyway here is my solution to finding duplicate records.

1. I am doing a find duplicates Query (thanks to the wizard)
that makes a table listing all of the records that are
duplicated.
2. I am running a Update Query with a join between the two
tables updating any record in the duplicate table with a
FLAG = Yes in the original table.

That's it. It saves me a lot of coding time and it appears
quite fast. I do not know how Jet does it's work but I
figure it knows more than I do so I just let it do it's
thing. That's the philosophy I used to use when all I used
was Queries.

Again Dev, and Terry thanks for the help it sure saved me a
lot of time.
--
Have a nice life.

"Progress is just a faster road to the end." me

Quote:
>I would go with your temp table idea, but instead of using
DAO use queries.

>Query 1 - Update query
>Join your existing table and your temp table on the field
you want to check
>for duplicates and add the Flag field from the temp table,
change it to an
>update query and put in true for the update value on the
Flag field.  This
>will then set the field to true only on the duplicate
records in the
>temporary table.

>Query 2 - Append query
>Then do an append from the temp table to the existing table

>Query 3 - Delete query
>Then clear down the temp table with a delete query.

>In the following I've used tblExists as the existing table
and tblTemp as
>the temporary table, I've used the field names you have
used below.

>Query1 SQL is as follows
>UPDATE tblExists INNER JOIN tblTemp ON tblExists.Num =
tblTemp.Num SET
>tblTemp.Flag = True;

>Query 2 SQl as follows
>INSERT INTO tblExists SELECT tblTemp.* FROM tblTemp;

>Query 3 SQL as follows
>DELETE tblTemp.* FROM tblTemp;

>and that should do it.



Sun, 30 Jul 2000 03:00:00 GMT  
 How do I test for a prior use?

the only other thing you may want to do is have a second and third sort
order within the duplicates, just add it to the sql

you could even generalise the sub with rs, dataField, flagField, flagValue
arguments so you could run it on any table

corbin

Sub flagDuplicates()

Dim daoDB As DATABASE
Dim daoRS As Recordset

Dim varNew
Dim varOld

Set daoDB = CurrentDb
Set daoRS = daoDB.OpenRecordset("select data, flag from newTable order by
data")

Do While Not daoRS.EOF
    varNew = daoRS("data")

    If varNew = varOld Then
        daoRS.Edit
            daoRS("flag") = "used"
        daoRS.UPDATE
    End If

    varOld = varNew
    daoRS.MoveNext
Loop

End Sub



Quote:
> Going batty,

> I need to test if a number is entered twice in inported
> data.
> If it has been enter prior then I need to flag it, not
> delete or exclude it just flag it.
> The table would look like this:

> Num     | Flag
> 1           |
> 2           |
> 3           |
> 1           | Used
> 4           |
> 2           | Used
> 5           |
> 1           | Used

> I tried using a Seek but that always found itself and
> flagged it.
> I think I am missing something.

> Thanks for the help.

> --
> Have a nice life.

> "Progress is just a faster road to the end." me



Sun, 30 Jul 2000 03:00:00 GMT  
 
 [ 14 post ] 

 Relevant Pages 

1. TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST

2. Modules/Macros to add prior to using Access 2000

3. Test is requery is done

4. How to test when shell() is done?

5. TEST TEST TEST TEST

6. alt.test,comp.test,misc.test,news.test

7. TEST TEST TEST

8. TEST TEST TEST

9. Testing,testing,testing

10. test test test

11. ADO thinks DBMS is done, but DBMS ain't done

12. Calculate Date Range From Prior Week

 

 
Powered by phpBB® Forum Software