Unanswered: Matching records from different tables???
Not as simple as it seems, I need to match records from two different tables easy I know, only problem is that the only common field is the company name and it seems that the two tables carry slightly different details on some of the companies... I.E.
Table 1 Table 2
B & Q B & Q PLC
Sainsburys Sainsburys PLC
I need info stored in table 1 such as address tel etc, to show with information held in table 2 (advertising spend).
Can you run any SQL I'm not that good with SQL though so simple terms please, to find a part match, or first 5 letter match or something like that, Im sure one of you clever guys can do it.
Great help on the above code, but have come across another problem, some company names are long names like "South West Electrical Centre" and some are small like "Tooltech" with the small ones it works brilliantly in matching them, but you can imagine in the South West their are a lot of business which start with South West (Fools!) anyway is their a way that I can split the information held into two tables one holding all customer names with less than 10 letters in the word and another table which holds customers whose name contain more than 10 letters,
If anyone understands that HELP............................
"... is their a way that I can split the information held into two tables one holding all customer names with less than 10 letters in the word and another table which holds customers whose name contain more than 10 letters... "
Glad the other code helped.
To split the data at 10 characters you can use the mid function twice into 2 new fields: once as in mid(fieldname, 1, 10) and then mid(fieldname, 11, len(fieldname)).
Let me know if you need me to put that into SQL for you.
Originally posted by Davekingwcp
Could you put it into SQL it took me a while to get the other bit working, the field for the shortened info is comparefield1 + comparefield2 and the table is called Mergedinformation and MMS(Radio).
Ok Dave, here's an example for one of the tables. Hopefully this is well laid out for you so you can easily change the code if I've mis-understood your object names.
To update TABLE [Mergedinformation], FIELD [ShortCoName] with the first 10 characters of data from the FIELD [Comparefield1]...
UPDATE SET [Mergedinformation].[ShortCoName] = Mid([comparefield1],1,10);
To update TABLE [Mergedinformation], FIELD [ShortCoName2] (note this is a new field) with the next set of characters after the first 10 characters from the data in FIELD [Comparefield1]...
UPDATE SET [Mergedinformation].[ShortCoName2] = IIf(Len()<10,"",Mid(,11,Len()));