Improving update query (1 Viewer)

trab

Registered User.
Local time
Today, 13:14
Joined
Feb 10, 2014
Messages
19
I'm using MsAccess 2007, and I have two large tables of data, say tbl01 and tbl02, which are added to each month, and I have to update tbl01 from tbl02, based on different sets of linking criteria. This can take a long time. Would it help, if I appended to a temporary table with linked records from both tables, and then update tbl01 from the temporary table? (Or even perhaps try the linking the two table that contain the new month's data?)

I'm clutching at straws maybe, but I'm wondering how to improve the whole process.
 

jleach

Registered User.
Local time
Today, 09:14
Joined
Jan 4, 2012
Messages
308
Can you post the SQL of the update query? That'd give us a much better idea of what might be done to help.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:14
Joined
May 7, 2009
Messages
19,169
indexed both table on the Fields that are involved in the Query.
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:14
Joined
Jan 23, 2006
Messages
15,361
In addition to Jack's request can you tell us a little more about the tables and the data? What is the subject matter? How many records are involved? What is the purpose of the activity? Not trying to be snoopy, just looking for some business context since there may be alternatives..
 

trab

Registered User.
Local time
Today, 13:14
Joined
Feb 10, 2014
Messages
19
Hello,

I work in the NHS, and I'm linking drugs expenditure to patient visits here, linking on :

date;
consultant (ConsCodeNat);
location;

There is also a mapping table for locations, mapping locations on the one table to those on the second.

The two tables, transactions and tblPatientVisitsLookup have around 120K and 360K records respectively, that's around 15K and 45K per month.

Here is the SQL of the sort of query I'm using, albeit trimmed down a bit :

Code:
UPDATE (transactions INNER JOIN TblPatientVisitsLookup ON (transactions.ConsCodeNat = TblPatientVisitsLookup.ConscodeNat) AND 
       (transactions.CaseNo = TblPatientVisitsLookup.UnitNo)) INNER JOIN tblWardMappings ON (TblPatientVisitsLookup.DeptLoc = tblWardMappings.[OpsLocation) AND (transactions.ward = tblWardMappings.[PharmLocation]) 

SET transactions.Episode = [TblPatientVisitsLookup]![ID], 
    transactions.EpisodeforMatching = Trim(Mid([TblPatientVisitsLookup]![ID],1,12)), 
    transactions.Consepno = Val(Trim(Mid([TblPatientVisitsLookup]![ID],13,2))), 
    transactions.[QueryNo] = "qrySLRLinkUpdate08", 
    transactions.Category = [TblPatientVisitsLookup]![Source]

WHERE (((transactions.Episode)="in query") AND 
      ((transactions.Contra) Not Like "Contra*" Or (transactions.Contra) Is Null) AND 
      ((IIf([issue date]>=[SubStartDt] And ([issue date]<=[SubEndDt] Or IsNull([SubEndDt])),"Update","Ignore"))="Update") AND 
      ((transactions.Value)>0) AND ((TblPatientVisitsLookup.Source)="IPG"));
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:14
Joined
May 7, 2009
Messages
19,169
which table does [issue date] belongs? tblWardMappings?


if not in tblWardMappings, then the table is useless.
 

trab

Registered User.
Local time
Today, 13:14
Joined
Feb 10, 2014
Messages
19
No it's in transactions. Why is the ward mapping table useless, if I have different codes for the same location, depending on which table they are in
e.g. for ward 1, say, it's WD01 in transactions, and W001 in the patient visits table?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:14
Joined
May 7, 2009
Messages
19,169
the table is neither used in Criteria or in Set (Update) statement. it just add additional load to your query, or served as a Tier (junction) to table Transactions and tblPatientVisitsLookup. what really is involved is Transactions and tblPatientVisitsLookup.
 

trab

Registered User.
Local time
Today, 13:14
Joined
Feb 10, 2014
Messages
19
So if, as part of the linking criteria, I wish to link on the location, how might I do that, if the codes on each table are different? You might say just link on patient ID and the date, but sometimes the drug (cancer drug) is issued on the pharmacy system in advance in preparation for the patient's expected visit.
 

Users who are viewing this thread

Top Bottom