Creating normalized tables with queries

[Janelle.Dunlap@ge.com]

I have a table in my database that is linked to an excel spreadsheet.
I need to be able to manipulate the data in this linked table so that I
can create smaller normalized tables that work with what I am trying to
do (all data used in my db will come from this one spreadsheet). I am
currently trying to utilize the make-table queries to create the tables
that I need from this data, however I am unsure of how to add foreign
keys to a table so that my data matches up correctly. Does anyone know
of a way to do this? Is there some sort of append query that can be
run to find entries in one table matching the entries of another table
as the criterion? Any help would be much appreciated!

[Rick Wannall]

Re: Creating normalized tables with queries

If this is a one-way deal, importing data into normalized tables and kissing
the old one goodbye, something along this line is what you need:

(Never tried pasting datasheets in this form, so if the tables are scrambled
I apologize in advance.)


Linked spreadsheet:

NameKey FirstName LastName HomePhone WorkPhone
1 Bob Wilson 111.222.333 444.555.6666
3 Harriet Stowe 777.888.9999 55.33.1111


Here are some queries to run

Make Normalized Tables 01 - Name Table (create name table, assuming each row
represents a different person)

SELECT Sheet1.NameKey, Sheet1.FirstNam e, Sheet1.LastName INTO MyNameTable
FROM Sheet1;

Resulting data in MyNameTable
NameKey FirstName LastName
1 Bob Wilson
3 Harriet Stowe


Make Normalized Tables 02 - Phone Table (create a phone table, using home
phone to begin with)

SELECT Sheet1.NameKey, Sheet1.HomePhon e AS Phone, "H" AS PhoneType INTO
MyPhoneTable
FROM Sheet1
WHERE (((Sheet1.HomeP hone) Is Not Null));

Resulting data in MyPhoneTable
NameKey Phone PhoneType
1 111.222.333 H
3 777.888.9999 H


Make Normalized Tables 03 - Append to Phone Table (append next type of
phone number to phone table, this time work phone)

INSERT INTO MyPhoneTable ( NameKey, Phone, PhoneType )
SELECT Sheet1.NameKey, Sheet1.WorkPhon e AS Phone, "W" AS PhoneType
FROM Sheet1
WHERE (((Sheet1.WorkP hone) Is Not Null));

Resulting data in MyPhoneTable (including rows added above, of course)
NameKey Phone PhoneType
1 111.222.333 H
3 777.888.9999 H
1 444.555.6666 W
3 55.33.1111 W


Make Normalized Tables 04 - Names and Phones from new tables (stitch name
and phone back together in a simple select query)

SELECT MyNameTable.Fir stName, MyNameTable.Las tName, MyPhoneTable.Ph oneType,
MyPhoneTable.Ph one
FROM MyNameTable INNER JOIN MyPhoneTable ON MyNameTable.Nam eKey =
MyPhoneTable.Na meKey;

Resulting data shown in this select query
FirstName LastName PhoneType Phone
Bob Wilson W 444.555.6666
Bob Wilson H 111.222.333
Harriet Stowe W 55.33.1111
Harriet Stowe H 777.888.9999

Make Normalized Tables 05 - Look like the old table using crosstab query
(present the normalized data to look like the original spreadsheet, except
column names, which I didn't bother about but could have if it mattered)

NameKey FirstName LastName H W
1 Bob Wilson 111.222.333 444.555.6666
3 Harriet Stowe 777.888.9999 55.33.1111


Fix all of the data on the way in. Don't drag it all in and then try to
match things up if you can avoid it.

[CDMAPoster@FortuneJames.com]

Re: Creating normalized tables with queries

Janelle.Dunlap@ ge.com wrote:[color=blue]
> of a way to do this? Is there some sort of append query that can be
> run to find entries in one table matching the entries of another table
> as the criterion? Any help would be much appreciated![/color]

You can use a subquery that utilizes criteria to obtain the foreign
key. Here's an example:

Let's say I have tblEmployees that is mostly normalized and
tblVacations that uses a single field for the employee name from an
imported spreadsheet.

tblEmployees
EID Autonumber
EFirstName Text
ELastName Text
EID EFirstName ELastName
1 Joe Young
2 King Kong
3 The Hulk

tblVacations
VacationID Autonumber
VacationName Text
StartDate Date/Time
EndDate Date/Time
VacationID VacationName StartDate EndDate
1 King Kong 6/8/06 6/13/06
2 Joe Young 7/10/06 7/14/06

qryOnVacation:
SELECT EFirstName, ELastName, (SELECT VacationID FROM tblVacations
WHERE Date() BETWEEN StartDate AND EndDate AND VacationName =
tblEmployees.EF irstName & ' ' & tblEmployees.EL astName) AS VacationKey
FROM tblEmployees;

!qryOnVacation:
EFirstName ELastName VacationKey
Joe Young Null
King Kong 1
The Hulk Null

That was a warm up. Now I want to use the names in tblEmployee to
eliminate tblVacations.Va cationName.

qryNewtblVacati on:
SELECT VacationID, StartDate, EndDate, (SELECT EID FROM tblEmployees
WHERE EFirstName & ' ' & ELastName = tblVacations.Va cationName) AS EID
FROM tblVacations;

!qryNewtblVacat ion:
VacationID StartDate EndDate EID
1 6/8/06 6/13/06 2
2 7/10/06 7/14/06 1

It's not a perfect example but should give you an idea of a way to
start matching up the ID's. The DLookup function acts much like a
subquery so it could be used instead the subquery.

James A. Fortune
CDMAPoster@Fort uneJames.com