Using VBA to copy data from one field to another (1 Viewer)

DuckyMinotaur

New member
Local time
Today, 13:07
Joined
Nov 23, 2016
Messages
7
Hi guys,

I have a database where records from two tables can only be matched automatically if certain criteria are met (e.g. the name is the same and the D.O.B is the same ect...)
I have a query that matches these records together if all criteria are met (this is done by copying the primary key value for one record in the first table into a matched primary key field for the matching record in the second table)
The data can be matched if not all the criteria are met, but only manually. So I've produced a form with the unmatched records from one table on one sub form and the unmatched records from the other table on another sub form.
Is there a way to click on the records that match and then press a button to copy the primary key value for the record in the first table into a matched primary key field in the second table? :banghead:
Thankyou :)
 

DuckyMinotaur

New member
Local time
Today, 13:07
Joined
Nov 23, 2016
Messages
7
That's what I've done for the first one, but the user needs to manually match the other records, I was just wondering if there was a way to make it more user friendly?
 

static

Registered User.
Local time
Today, 13:07
Joined
Nov 2, 2015
Messages
823
Code:
INSERT INTO Table2 ( name, dob )
SELECT [name], [dob]
FROM (
	SELECT Table1.name, Table1.dob
	FROM Table1 LEFT JOIN Table2 ON Table1.dob = Table2.dob AND Table1.name = Table2.name
	WHERE Table2.name Is Null
)


would insert into table2 missing names and dobs from table1.

You could create the query on the fly depending on the fields being matched.
 

DuckyMinotaur

New member
Local time
Today, 13:07
Joined
Nov 23, 2016
Messages
7
The problem is that the data was manually entered elsewhere and so there is errors that a human can pick up a lot easier so it needs to be a manual match
 

moke123

AWF VIP
Local time
Today, 09:07
Joined
Jan 11, 2013
Messages
3,852
we have no idea what your data looks like or what your workflow is so its difficult to give you a concrete solution. if you upload a sample it may be easier.
 

static

Registered User.
Local time
Today, 13:07
Joined
Nov 2, 2015
Messages
823
If it's sensitive data obfuscate it somehow. We'd only need a sample.


If only a human can recognise the errors how can you automate it?

You could probably make viewing/finding common errors easier but we can't help unless you give more details.

The query I posted is two queries in one. You don't have to use the update, just build the select based on user defined fields and parameters and display the result for checking.
 

Cronk

Registered User.
Local time
Tomorrow, 00:07
Joined
Jul 4, 2013
Messages
2,770
I have had the occasional app where it was necessary for manual confirmation of near duplicates. One situation was the issue of military medals where a new application from an ex service personnel almost but not quite matched an existing member record in the database.

Differences might be change of name, gender, address, service number (on re-enlistment), marital status.

A query could be used to insert into the import table the existing MemberID where there was a match on all fields but where say 3 out of five fields where there was a match, a manual eyeball could identify duplicates with an acceptable degree of certainty.

Use a query to update the single record in the import table. As we don't know your field names you'll have to change the following

update ImportTable set ImportTable.keyFieldName to [selected keyFieldName value in subform1] where ImportTable.keyFieldName = [value in subform2]
 

DuckyMinotaur

New member
Local time
Today, 13:07
Joined
Nov 23, 2016
Messages
7
I've found out how to do it now
I have something like

Private Sub cmdMatchRecord_Click()

a = form.subforma.fielda
form.subformb.fieldb = a

End sub

I originally had the user having to copy and paste the data from one to the other and wanted to make it more user friendly
 

moke123

AWF VIP
Local time
Today, 09:07
Joined
Jan 11, 2013
Messages
3,852
I'm a big fan of the Levenschtein distance and soundex myself. I use a procedure when entering names that checks them against all similiar names and presents the user with a list of possible matches. It catches all the slight misspellings such as Jon - John, Smith - Smyth, etc.
 

Users who are viewing this thread

Top Bottom