Need help with joining, mixing and shaking two tables ...

Discussion in 'Computer Support' started by Christian, Apr 7, 2004.

  1. Christian

    Christian Guest

    Hi there,

    first of all thank you for your interest in (helping me to solve ? or
    just being curious about ? ;-) my problem.

    I have to join, mix and shake two tables and no clue how best to do
    this. I have Microsoft Office that I can use, so probably Excel and
    Access will be the tools of choice. (If necessary I could also use any
    free software that runs under WinXP, but would prefer not to install
    anything, though I would not hesitate about getting AWK, SED, etc.)
    I will try to explain the task in terms of two lists of people and
    addresses. Though it is slightly more complex in reality, this
    description does catch the basic difficulties I am facing.

    What I start with is two tables, say lists of my friends (who also
    have no clue about computers, so I cannot ask them ... ;-):

    Table 1 contains a long list of names in the format (name, first name,
    nickname). For all entries in table 1 all three fields are filled and
    thus all the entries are different from each other (no duplicates when
    considering all three fields together).
    Table 2 contains fewer entries that are names and addresses in the
    format (name, first name, nickname, ZIP code, city, street, phone
    Table 2 lists some of the people from table 1 and some additional
    entries that come in the format (name, first name, <blank>, ZIP code,
    city, street, phone number). For some the address is empty, too.
    I.e. not everybody in table 2 has a nickname, so this field may be
    empty for some entries. But there may be people of the same name, some
    with and some without nicknames, perhaps even with the same address.
    (Miller, John and Miller, John, jr. are living in the same place.)
    Duplicates in names for those without nickname are possible in table
    2, though the addresses may be different.

    Step 1:
    I need to create a "complete" table. Complete means it contains all
    the entries from table 2 (i.e. no addresses get lost) plus all entries
    from table 1 that are not (namewise) already in table 2.
    Then the table should be sorted in alphabetical order, first by name,
    then first name, then nickname.
    So the result will be a table listing all of my friends, some with
    addresses, some not - and no information will be lost.
    It should look like this:

    name 1, first name 1, nickname 1, ZIP 1, city 1, street 1, phone 1
    name 1, first name 1, nickname 2, ZIP 2, city 2, street 2, phone 2
    name 1, first name 1, nickname 3, ZIP 3, city 3, street 3, phone 3
    name 2, first name 2, , ZIP 4, city 4, street 4, phone 4
    name 2, first name 2, , ZIP 4a, city 4a, street 4, phone 4
    name 2, first name 2, nickname 5, ZIP 5, city 5, street 5, phone 5
    name 3, first name 3, nickname 6, ZIP 6, city 6, street 6, phone 6
    name 7, first name 7, nickname 7
    name 8, first name 8, nickname 8, ZIP 8, city 8, street 8, phone 8

    Step 2:
    (This is about giving everybody a home. ;-)
    Now I need to fill the addresses for everybody who has a nickname.
    This is how it needs to be done:

    Go through all entries that have name, first name and nickname and do
    - if the entry already has address: no action
    - if the entry has no address: copy address from equivalent entry
    without nickname. If there is more than one equivalent entry without
    nickname (Smith, John with address, Smith, Anna with address and
    Smith, Adam, "Joe" without address) then create an additional entry
    with the same nickname until there are as many of them as equivalent
    entries with address. Then copy each address to one of the "nickname
    entries". A constellation of multiple equal (name1, first name1,
    <blank>, address) and several (name1, first name1, nickname, <blank>)
    is possible and needs to be considered.

    Well, "that's all" !
    (Other than that I need to do this for about 30 "table 2"s and throw
    away all entries without nickname after completion of step 2 - but how
    to do that I think I know. ;-)

    So if you have any suggestion how I can do this, please post it in
    reply to this posting. If you have a solution for only one of the
    steps above, it is welcome, too. :)

    Thanks a lot in advance

    Christian, Apr 7, 2004
    1. Advertisements

  2. Christian wrote:

    <snip desired table manipulation>

    It sounds as though you need a database app/manager not a word
    processor. Word and Excel aren't the answers to database situations,
    just because they came with the lower-end (i.e., not containing Access
    database front end) versions of Office.
    Blinky the Shark, Apr 8, 2004
    1. Advertisements

  3. Christian

    Christian Guest

    I also have access to Access.

    Christian, Apr 8, 2004
  4. Christian

    PuddleNuts Guest

    Access is the way to go. (Access is garbage, but if you have it.....) I
    can help you with the SQL scripting, but you may be better off doing it
    manually (in Excel or Access) if the list is short (500 items or less).
    PuddleNuts, Apr 8, 2004
  5. Christian

    Ed Rust Guest

    Do the various tables already exist, and , if so, what app(s) did you
    use to create them?

    I have Microsoft Office that I can use, so probably Excel and
    This sounds like a fairly easy task if you are using Access.

    <snip description of desired manipulations)

    If you have a uniform format already, you can export all the tables to
    text files in a single format, e.g., tab- or comma-delimited, then
    simply import them to a single Excel spreadsheet or Access database table.

    If not already in a common format i.e., all with the same number and
    positioning of fields, create a new table in Access and append the data
    to the table column-by-column by cutting and pasting. Not necessarily
    the most elegant solution, but substantially faster than having to go
    back and enter extra tabs or commas to make the source tables uniform.

    After the new table is completely created, searching for duplicates is
    relatively easy by using a query.


    Ed Rust, Apr 8, 2004
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.