Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    Plymouth, UK
    Posts
    59

    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.

    Please help........

  2. #2
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108

    Re: Matching records from different tables???

    You can try this.

    Create a new fields in both your tables. These fields will both hold the shortened company data: shortened to 5 chars.

    Create a new Update query based on tbl1. In the query, add your new field and insert an expression like this into the "Update To:" box:
    mid([companytablename],1,5).

    That expression will take the first 5 characters of your company data and put them into your new field. The SQL should look something like this:

    UPDATE Table1 SET Table1.ShortCoName = Mid("companyname",1,5);

    Repeat the process for table 2. You now have 2 tables that can be compared to each other.


    Good luck - Andy

  3. #3
    Join Date
    Nov 2003
    Location
    Plymouth, UK
    Posts
    59

    Re: Matching records from different tables???

    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............................

  4. #4
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108

    Re: Matching records from different tables???

    "... 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... "

    Hi Dave

    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.


    Regards - Andy

  5. #5
    Join Date
    Nov 2003
    Location
    Plymouth, UK
    Posts
    59
    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).

    Regards Dave

  6. #6
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108
    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).

    Regards Dave
    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([1])<10,"",Mid([1],11,Len([1])));

    Hope that's clear.


    Regards - Andy

  7. #7
    Join Date
    Nov 2003
    Location
    Plymouth, UK
    Posts
    59
    Clear as Mud!

    Thats brill, thank you

    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •