[AccessD] Fastest Way

Drew Wutka DWUTKA at Marlow.com
Thu Jan 14 18:41:29 CST 2010


Ugh....

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Thursday, January 14, 2010 6:16 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Fastest Way

Yeah, the doctor says the first four fields could be identical with
different results.  

R 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Thursday, January 14, 2010 3:58 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Fastest Way

Interesting problem here.

The quirk is going to be that last field.  Can you really have two
records
with the first four fields (including a date and time) with different
'Test
Result Text'.  I would think you would end up with a different value in
the
date and/or time fields for new test results.

So, ASSUMING that the Test Result is irrelevant to the uniqueness of a
record, I would recommend loading a collection with the first four
fields as
the 'key' to each item in the collection.  I would create a separate
table
with these 'keys' (for faster loading).  Put the fields together with a
delimiter like :.  This would create a one line string with a unique
identifier for each record.

For example, if the first record is 1, 1 , 1-29-2004,1214, you're ID
field
would be 1:1:01292004:1214.  Adding that to a collection would put all
of
the currently imported records into memory in a handy little tiny
string,
that you can query with an 'error if it doesn't exist' function,
creating a
pretty instantaneous check if a record has been imported.

However, if that fifth field can be different with the first four fields
the
same, this would probably be a bad approach because that field could
jump
the amount of memory being used quite a bit!

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Thursday, January 14, 2010 9:01 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Fastest Way

Dear List:

I have a medical database into which lab tests are imported from a
spreadsheet (exported from a mainframe medical records database at a big
hospital).  Front and back ends are Access 2003. The table
tblPatientTest
has (in addition to the autonumber PK) 5 fields:

Patient ID (FK)

Test ID (FK)

Test Date as Date/Time (indexed duplicates OK)

Test Time as long integer - value between 0000 and 2399 (indexed
duplicates
OK)

Test Result - Text (indexed duplicates OK)

I'm using DAO to add the records to the table.  Imports will happen
monthly
and the number of records will be on the order of 10,000-100,000 with
mean
probably around 20k-40k.

As the tests are imported I want to check the table to see if the test
record is already present by finding a record which matches all 5 fields
to
prevent duplicate test records from being entered.

Since the import will take place monthly, import time is not super
critical
but before importing the user will run an Error Report which does all
the
error checking that the import does without actually doing the import.
The
user then resolves the errors and when the Error report is clean, will
run
the import.  So the error report may have to be run more than once to
clean
the data.

It is possible that the user will try to import a spreadsheet that has
already been imported. So it's important to screen for duplicates. 

So I want to find the fastest method of checking for duplicate.

The obvious thing is rs.FindFirst on all five fields, but I'm thinking
that
may take a really long time.

Another possibility would be to create a temporary table in the front
end or
external mdb, and create indexed records by concatenating all five
fields
and checking for dups on that table.  Probably faster than the first
method
but creating the table could take a lot of time when the Patient Test
table
has a few hundred thousand records in it.

Or I could add the field of concatenated fields to the table itself as
the
records are added, indexed and maintain that.

Or some other method that I haven't thought of or don't know about.

Any advice welcome.

MTIA

 

Rocky Smolin

Beach Access Software

858-259-4334

www.e-z-mrp.com <http://www.e-z-mrp.com/> 

www.bchacc.com <http://www.bchacc.com/> 

 

 

 

The information contained in this transmission is intended only for the
person or entity to which it is addressed and may contain II-VI
Proprietary
and/or II-VI Business Sensitive material. If you are not the intended
recipient, please contact the sender immediately and destroy the
material in
its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure,
dissemination, or other use of, or taking of any action in reliance upon
this information by persons or entities other than the intended
recipient is
prohibited.


--
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
The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.





More information about the AccessD mailing list