Matching two datasets - running a subform from SQL query

[RobinL]

Dear all,

I’m looking for some pointers on how to solve what I think is a relatively simple problem. I’m experienced in programming VBA in Excel, but haven’t much experience in Access.

I have two tables in a database, and I need to ‘match’ the records in each, based on several fields such as name, dob, and a unique identifier field. The problem is that the data quality is quite poor and so there is missing and inaccurate data. Therefore I can’t just join the tables using the unique ID field.

I’ve already matched all the records which have good enough data to match automatically (including doing various rounds of string manipulation etc). The remainder need human input to decide whether they’re really matches.

I’d like to make a form similar to the mock-up I’ve created below. The top left of the form shows information from the first table. For each record in this table (tblCCSS), I need to try and find a match in a second table (tblCPD).

What I’d like to be able to do is quickly to run several different queries to see if I can find either a match. For instance, if I try and match against the DOB in the tblCCSS, this may find several records in the second table (tblCPD), only one of which is a match.

After I’ve found the match, I’d like a button which records the match in a separate table (using a unique key field which I’ve established for each table) and removes the entries from the two matching tables (tblCPD and tblCCSS).

I’m very happy to work on this little project myself – I’m not asking for a full solution. But it would be extremely useful if someone could indicate whether I’m going in the right direction, and give me some pointers of how a pro would do it.

I’m thinking of setting up a form containing two subforms, as pictured in the mock up. When the ‘run SQL query’ button is clicked, this will update the dataset that underlies the second subform (on the top right).

One thing I’m unsure about is how to control the dataset in the second subform. I notice that in Access I can set the “recordsource” property of a form to a SQL query. Is this the way to go or is there a better way to do it?

Thanks very much for any advice,

Kind regards,

Robin

 

[spikepl]

I would use a query for each subform.

I would not shift data around, but just mark the matched records, and each query should just display unmatched records.

If left table has IDLeft and right table IDRight, then in the matched record on the left you could insert the IDRight and vice versa, and requery the subforms to display only unmatched records.

Or something like that.

 

[RobinL]

Thanks very much for the quick response - what you've suggested definately makes sense to me.

Could I also ask what's the best way to set up the queries for the two subforms?

Is it preferable to set this up using the 'RecordSource' property (as an SQL string), or is it better to set up a recordsets and then use something like

Set subfrm1.Recordset = rs1

Is the latter preferable because I can then update the same recordsets with the markers for whether I have found a match?

Thanks again!

 

[spikepl]

The major point is to do the minimum work for the one-off effort.

Create each query in the query designer, and save.

Set the RecordSource property of each subform to the name of the relevant query.

Then look at the forms Filter property. You can construct a filter out of your criteria, that then filters what is shown on the form.

Alternatively, in the query designer you could use the values from the controls as criteria. Right-click in the criterion-field and select Build. This will let you navigate to the control in question (referencing controls on subforms can initially appear tricky at times)

 

[RobinL]

Thanks very much - I'm experimenting with your suggestions. I'm pretty confident I'll be able to get it to work now