Update queries with aggregate/subquery issues 1

[Felix18807]

I have two tables containing name and a postcode data.

I need to do fuzzy matching between the two. I can join the two tables on postcode but want to fuzzy match on the name string.

I have a code that generates a statistic of similarity between two strings and creates a greater value for a more similar match (Jaro).

This far I have been using this query

UPDATE [TABLEB] INNER JOIN [TABLEA] ON [TABLEB].[Postcode] = [TABLEA].POSTCODE SET [TABLEA].B_ID = [TABLEB].ID, [TABLEA].MatchLevel = "3. Fuzzy Name And Postcode"
WHERE ((([TABLEA]. MatchLevel) Is Null) AND ((Jaro([TABLEA]![Name],[TABLEB]![Name]))>=0.8));

to identify the matches but on reflection I do not believe this gives me the best match for a postcode.

So I have made this query I hope selects the top fuzzy match

SELECT TableA.*, FUZZY.TableB.ID, FUZZY.Jaro
FROM (SELECT TableA.ID, TableB.ID, Max(Jaro([TableA].[Name],[TableB].[Name])) AS Jaro
FROM TableB INNER JOIN TableA ON TableB.Postcode = TableA.Postcode
GROUP BY TableA.ID, TableB.ID
HAVING (((Max(jaro([TableA].[Name],[TableB].[name])))>=0.8 And (Max(Jaro([TableA].[Name],[TableB].[Name])))<1))
)  AS FUZZY INNER JOIN TableA ON FUZZY.TableA.ID = TableA.ID;

My problem starts when I try to use this query as a subquery in an update. Has anyone got any experience of this? One solution would be to create a table based on my query then use this for the update but I want to avoid this extra step as this process is repeated frequently.

Many Thanks

 

[kjv1611]

SELECT TableA.*, FUZZY.TableB.ID, FUZZY.Jaro
FROM (SELECT TableA.ID, TableB.ID, Max(Jaro([TableA].[Name],[TableB].[Name])) AS Jaro
FROM TableB INNER JOIN TableA ON TableB.Postcode = TableA.Postcode
GROUP BY TableA.ID, TableB.ID
HAVING (((Max(jaro([TableA].[Name],[TableB].[name])))>=0.8 And (Max(Jaro([TableA].[Name],[TableB].[Name])))<1))
)  AS FUZZY INNER JOIN TableA ON FUZZY.TableA.ID = TableA.ID;

Are you sure that works? I see one obvious error (I think) where it should give you an error. I think it should be changed to this:

SELECT TableA.*, [HIGHLIGHT]FUZZY.bID[/HIGHLIGHT], FUZZY.Jaro
FROM (SELECT TableA.ID [HIGHLIGHT]AS aID[/HIGHLIGHT], TableB.ID [HIGHLIGHT]AS bID[/HIGHLIGHT], Max(Jaro([TableA].[Name],[TableB].[Name])) AS Jaro
FROM TableB INNER JOIN TableA ON TableB.Postcode = TableA.Postcode
GROUP BY TableA.ID, TableB.ID
HAVING (((Max(jaro([TableA].[Name],[TableB].[name])))>=0.8 And (Max(Jaro([TableA].[Name],[TableB].[Name])))<1))
)  AS FUZZY INNER JOIN TableA ON [HIGHLIGHT]FUZZY.aID[/HIGHLIGHT] = TableA.ID;

Basically, when you pull in the ID from each table, it shouldn't stay named by the previous table name and field name, but rather a new fieldname. That's why I named them aID and bID to keep it short.

Then you have to also refer to those by the new names everywhere else.

Can you test with that and see if it fixes your issues? It may be there is another issue somewhere, or some rule in JET SQL that disallows what you're trying to do, but hopefully that'll fix it..


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Felix18807]

I made the amendments and it does seem to have fixed the error. As a warning to anyone else attempting this type of update query - opening it in designer seems to break the code somehow. Re-opening it and re-running it directly works fine.
No idea what the query designer is up to however. Might be specific to my machine...

 

[kjv1611]

No, I've had the Query Design view mess-up a few of my queries in the past. It can be rather annoying at times.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[oharab]

I can also confirm, designer does not like sub queries!

----------------------------------------------
Ben O'Hara

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.