Best way to add missing data in Table1 from column in Table2 but no exact common field

[newdba12345]

Hello everyone,


I've collected details of 190,000 plus football games using Excel to download the data from the web and clean it up and deposit in Access, where I run reports etc. to analyse the data.

Unfortunately its without the country or league data attached to each game (there was no easy way to get that at the same time), so now of course I want to add that data and have it in either another table along with the team names, or have that data added to the existing table.

I was hoping to make a relationship between the two tables on team name (which is the only common piece of data in the two tables, although spellings are slightly different between the tables sometimes so I may need some wildcard-type matching going on too.)and then get the data from Table2 into Table1.

So I end up with Table1 having the original data plus the "missing" columns for Country and League imported for each game, alternatively a new table with all the data from both matched-up would also suffice.

I tried making a relationship between the two tables but then I got a bit confused and have been scratching my head for a while, even watched a few videos but I'm still at a loss as to how best manage this and can't get a query to pull data from both tables, only one or the other.

Think I've now got a mental block on this somehow, so I'm putting this request for help here as I'm sure once the seasoned experts (and probably the beginners too) amongst you read this and stop laughing for long enough you'll have an answer for me!!

Thanks in advance.

[June7]

And how are the team names spelled differently? How much variation - St. Louis, Saint Louis? How was this permitted to happen? Pattern matching will likely offer nothing but frustration. The only solution may be to manually clean up the data so names are consistent. This can be a tedious, time-consuming task. Then really should set up link on autonumber type as PK and number type as FK and not duplicate name in multiple tables.

[CJ_London]

Agree with June. Match what you can and look at what's left. Decide a standard format and manually correct the remainder (how many teams can there be?). If this is an ongoing problem, perhaps create an 'alias' table and use the replace function to correct e.g.

tblAliases
Phrase...Alias
St.         Saint
St          Saint

then use a replace function to replace Phrase with Alias. However you need to take care, you would not want to change Stamford to Saintamford for example.

Had a similar situation with a pharma client where they had Tab, Tabs and Tablets, Cap, Caps, Capsule, Capsules etc so my advice would be to replace longer with shorter e.g. St replaces Saint and St.

Another small trick is to use an underscore to represent a space so it is visible in the aliases table then replace it before applying the main replace e.g.

replace(myField,replace(Phrase,"_"," "),Alias)

[isladogs]

What format is the data you are downloading? XML? JSON?
There are several sites offering such data. Have you looked at other sites?

[orange]

I agree with the advice offered and would add the following:

-when you start to "cleanse" the data, be sure to keep backups(bad things can happen and you don't want to start from scratch repeatedly)
-lots of people have spent careers "cleansing data". While some things may be suited to automated routines, some may need "human eyeballs and best guessing"

What is the goal with respect to analyzing the data? (Just curious).

Good luck.

[Access12345]

A matching table might be a solution.

Table 1 Team Name = FC Ajax.
Table 2 Team name = Footbal club Ajax.

Matching table:

Teamname table 1	Teamname table 2
FC Ajax	Footbal club Ajax
Etc.	Etc.

SELECT
  Table1.MatchDate,
  Table2.TeamLocation
FROM Table1
INNER JOIN MatchingTable
  ON Table1.Teamname = MatchingTable.[Teamname table 1]
INNER JOIN Table2
  ON MatchingTable.[Teamname table 2] = Table2.Teamname

[orange]

Do you have an answer for Ajax

how many teams can there be?

.

You could Group by Name and Count to get some idea of the variation in names.

[June7]

Building the 'matching' table would still require reading through data and creating the matching pair records - might as well just correct the data. Unless as Ajax said, this is on-going problem. And if it is on-going, I would expect new variations on each import. Will have to be vigilant and validate with every import.

[isladogs]

If it is an ongoing problem then I would definitely investigate alternative sources of such information as I suggested in post #4
For example, see the two JSON files attached which I downloaded free a year or so ago.
There are many similar examples (XML or JSON) both for football and other sports - mostly free to download.

then all you need to do is import the data and transform it into a format that Access can use

[newdba12345]

Many thanks everyone for all the advice much appreciated.

I am checking to see which option will involve the least work which may be matching the majority of names and then manually updating the rest. Once I have done that future imports will have the xorrect data allocated automatically after import, so that solution will definitely work going forward.

To clarify after the data was downloaded (from several sites) and combined, additional data was then calculated and added based on things like current form etc., so the data is now quite unique.

I've not yet determined how many individual teams there are, so will provide that along with an update on my attempts at the suggested solutions.

Thanks again to all those who responded.