Ping John Vinson

M

MikeR

John -
Regarding our thread on optimizing a way to prevent/flag dupes, my newsreader isn't
showing it. Is there an archive I can search?
Mike
 
K

Kelvin Beaton

Hi Mike

Any ideas on how to get this code to look at First and Last names?

It works great for a single field, and it would even be better if I could
check for dup first and last names....

Any input would be appreciated

Kelvin

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'------------------------------------------------------------
' Customer_ValidateID
'------------------------------------------------------------
Function Customers_ValidateID()
If (Eval("DLookUp(""[CustomerID]"",""[Customers]"",""[CustomerID] =
Form.[CustomerID] "") Is Not Null")) Then
Beep
MsgBox "The Customer ID you entered already exists. Enter a unique
ID.", vbInformation, "Duplicate Customer ID"
DoCmd.CancelEvent
End If
End Function
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 
J

John W. Vinson

Any ideas on how to get this code to look at First and Last names?

It works great for a single field, and it would even be better if I could
check for dup first and last names....
Click to expand...

Just put whatever criteria you need in the DLookUp. I don't see any need for
the Eval so we can lose one layer of quotemarks:

Function Customers_ValidateID()
If Not IsNull(DLookUp("[CustomerID]","[Customers]","[LastName] = """ &
Form.[txtLastName] & """ AND [FirstName] = """ & Form.[txtFirstName] & """"))
Then
Beep
< do something to handle dups>
End If

Note that it is perfectly possible to have two customers named Mike Smith. The
"do something" should NOT be to reject the addition!

John W. Vinson [MVP]
 
M

MikeR

John said:
A FindFirst is fast - FOR ONE RECORD.
Click to expand...
And provides me the info I need to log the record as a potential dupe.
A Query does your whole recordset in one swell foop.
One should be enough.
If you want *fast and no duplicates* then create a unique Index on the five
fields; run an append query from your linked text file; *you're done*.

Lines of code: 0.
Complexity of SQL: absolutely minimal.
Click to expand...
But no feedback for logging the error.
If you need specific information about *which* records are duplicates, then
you do need to run a find-duplicates query (on your linked text file). That
could be done prior to the append query, or afterward.
Click to expand...
I'm not after existing dupes, but potential ones that would be created it the record
were inserted/appended.
If you're checking for duplicates only between existing records and new
records, a Select query to show the dups, and then an Append query using a
frustrated outer JOIN between the linked file and the internal table would
work:

qryShowDups:

SELECT Field1, Field2, Field3, Field4, Field5, Field6, Field7
FROM linkedtable
INNER JOIN table
ON table.field1 = linkedtable.field1
AND table.field2 = linkedtable.field2
AND table.field3 = linkedtable.field3
AND table.field4 = linkedtable.field4
AND table.field5 = linkedtable.field5;
Click to expand...
linkedtable? I don't understand. There is no linked table. There is only one table.
INSERT INTO table(Field1, Field2, Field3, Field4, Field5, Field6, Field7)
SELECT Field1, Field2, Field3, Field4, Field5, Field6, Field7
FROM linkedtable
LEFT JOIN table
ON table.field1 = linkedtable.field1
AND table.field2 = linkedtable.field2
AND table.field3 = linkedtable.field3
AND table.field4 = linkedtable.field4
AND table.field5 = linkedtable.field5
WHERE table.field1 IS NULL;
Click to expand...
See above.

This is pretty much where I started, a findfirst, and depending on the result, an
insert. I may have to look at something other than Access (altho I can't imagine
what), because this is VERY slow. SQLServer is too big for my little desktop app.

Maybe this thread will stay for awhile.
Mike
 
J

John W. Vinson

linkedtable? I don't understand. There is no linked table. There is only one table.
See above.
Click to expand...

I was suggesting that you *link* to the table that you're going to export. It
might be necessary to Import the textfile into a temporary table (so you can
use indexes on the join).

John W. Vinson [MVP]
 
M

MikeR

John said:
I was suggesting that you *link* to the table that you're going to export. It
might be necessary to Import the textfile into a temporary table (so you can
use indexes on the join).
Click to expand...
Ahhhh. The light comes on. Dimly, but on.
Thanks, John
 
M

MikeR

John said:
I was suggesting that you *link* to the table that you're going to export. It
might be necessary to Import the textfile into a temporary table (so you can
use indexes on the join).

John W. Vinson [MVP]
Click to expand...
I'm doing something wrong. I created a second table called tmpLog in the same
database(no linking, right?). The 5 fields that uniquely ID a record are indexed in
both tables. The "real" table is named Log. The inserts into both tables only work
the first time thru the loop. Subsequent execution doesn't produce any errors, but
doesn't insert any data either. Also the RecordsAffected is zero after "c := if
dbDAO.RecordsAffected" even tho data is inserted into the Log table. I'm curious to
see what the speed is, because this scheme requires 3 hits on the DB for each record,
whereas using FindFirst and only inserting on a NoMatch condition took hits 2 at
most. My SQL looks like this.

sql := 'INSERT into [TmpLOG] (U_Call, Call, LDate, Freq, Mode, R_RST, ' +
'R_Serial, S_RST, S_Serial, Power, Grid, Iota, CQ_zone, ITU_Zone, ' +
'ST, COunty, CID, Comment, QSL_R, QSL_S, QSL, Manager) ' +
'VALUES ("' + inRec.u_call + '", "' + inRec.call + '", #' + inRec.QDate + '#,' +
inRec.freq + ', "' + inrec.mode + '", "' + inRec.r_rst + '", "' +
inRec.r_serial + '", "' + inrec.s_rst + '", "' + inrec.s_serial + '", "' +
inRec.power + '", "' + inRec.grid + '", "' +
inRec.iota + '", "' + inrec.cq_zone + '", "' + inRec.itu_zone + '", "' +
inRec.state + '", "' + inrec.county + '", "' + inRec.CID + '", "' +
inRec.comment + '", "' + inrec.qsl_R + '", "' + inRec.qsl_S + '", "' +
inRec.qsl + '", "' + inrec.manager + '");';
dbDAO.Execute(sql);
c := if dbDAO.RecordsAffected;

sql := 'INSERT into [LOG] (U_Call, Call, LDate, Freq, Mode, R_RST, R_Serial, ' +
'S_RST, S_Serial, Power, Grid, Iota, CQ_zone, ITU_Zone, ST, COunty, ' +
'CID, Comment, QSL_R, QSL_S, QSL, Manager) Select tmplog.u_call, ' +
'tmplog.Call, tmplog.LDate, tmplog.Freq, tmplog.Mode, tmplog.R_RST, ' +
'tmplog.R_Serial, tmplog.S_RST, tmplog.S_Serial, tmplog.Power, ' +
'tmplog.Grid, tmplog.Iota, tmplog.CQ_zone, tmplog.ITU_Zone, tmplog.ST, ' +
'tmplog.County, tmplog.CID, tmplog.Comment, tmplog.QSL_R, tmplog.QSL_S, ' +
'tmplog.QSL, tmplog.Manager from [tmpLog] Left Join LOG on ' +
'Log.U_Call = tmplog.U_Call AND Log.Call = TmpLog.Call ' +
'AND Log.LDATE = TmpLog.lDate AND Log.Mode = TmpLog.Mode ' +
'AND Log.freq = TmpLog.freq WHERE Log.Call is null;';
dbDAO.Execute(sql);
if dbDAO.RecordsAffected > 0 then
result := true;
dbDAO.Execute('Delete * from TmpLog');
 
J

John W. Vinson/MVP

I'm doing something wrong. I created a second table called tmpLog in the
same database(no linking, right?). ...
Click to expand...

Sorry - out of town for a while. I won't be able to reply until late next
week.

John W. Vinson [MVP]
 
M

MikeR

John said:
I was suggesting that you *link* to the table that you're going to export. It
might be necessary to Import the textfile into a temporary table (so you can
use indexes on the join).
Click to expand...

OK, John. Got it working. I added a table to the same database, so no linking, right?
Each of the 5 fields that define a unique record are indexed. No PK is defined.
I add to the secondary table (tmpLog), run the join query on Log, and delete the data
from tmpLog for each record.
Unfortunately, time of execution has not decreased. Can Access really not be whipped
into at least a fast trot?
Mike
 
M

MikeR

John said:
Sorry - out of town for a while. I won't be able to reply until late next
week.

John W. Vinson [MVP]
Click to expand...
John - Thanks so much for your help. I got it going now.
Mike
 
J

John W. Vinson

OK, John. Got it working. I added a table to the same database, so no linking, right?
Each of the 5 fields that define a unique record are indexed. No PK is defined.
I add to the secondary table (tmpLog), run the join query on Log, and delete the data
from tmpLog for each record.
Unfortunately, time of execution has not decreased. Can Access really not be whipped
into at least a fast trot?
Click to expand...

Individual indexes on the five fields will not ensure uniqueness, and will not
(I don't believe, I may be mistaken) improve performance as much as a SINGLE
unique five-field index. Try removing the individual indexes, and
ctrl-clicking all five fields, and then the Key icon.

If you could post your current SQL again, someone might have a suggestion.

John W. Vinson [MVP]
 
Top