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
    number).
    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
     
    Christian, Apr 7, 2004
    #1
    1. Advertising

  2. Christian wrote:

    <snip using Word>

    > 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


    <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 Linux RU 4892F
    http://linuxnotjustforgeeks.org
    http://blinkynet.net
    http://blinkynet.net/spag/w2000src.html - Win Source Code Leak
     
    Blinky the Shark, Apr 8, 2004
    #2
    1. Advertising

  3. Christian

    Christian Guest

    Blinky the Shark <> wrote in message news:<>...

    > It sounds as though you need a database app/manager not a word
    > processor.


    I also have access to Access.
    ;-)

    Christian
     
    Christian, Apr 8, 2004
    #3
  4. Christian

    PuddleNuts Guest

    Christian wrote:

    > Blinky the Shark <> wrote in message news:<>...
    >
    >
    >>It sounds as though you need a database app/manager not a word
    >>processor.

    >
    >
    > I also have access to Access.
    > ;-)
    >
    > Christian

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

    --
    Your proctologist called, they found your head.
     
    PuddleNuts, Apr 8, 2004
    #4
  5. Christian

    Ed Rust Guest

    Christian wrote:

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


    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
    > Access will be the tools of choice.


    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.

    HTH,

    Ed

    >
    > Thanks a lot in advance
    >
    > Christian
     
    Ed Rust, Apr 8, 2004
    #5
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Capt RobP

    Shaking Startup Music

    Capt RobP, Sep 17, 2003, in forum: Computer Support
    Replies:
    1
    Views:
    388
    Froats
    Sep 18, 2003
  2. visu

    joining two avseq.dat files

    visu, Oct 3, 2003, in forum: Computer Support
    Replies:
    2
    Views:
    2,907
    Diabolic Preacher
    Oct 4, 2003
  3. toyalp2

    Tables help, please

    toyalp2, Jan 22, 2004, in forum: Computer Support
    Replies:
    11
    Views:
    658
    informant
    Jan 25, 2004
  4. Robert

    Can't load Yahoo game tables ... need help

    Robert, Sep 19, 2006, in forum: Computer Support
    Replies:
    1
    Views:
    541
    Robert
    Sep 19, 2006
  5. Steve Pfister
    Replies:
    0
    Views:
    297
    Steve Pfister
    Apr 16, 2014
Loading...

Share This Page