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

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

 

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

 

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.

HTH,

Ed