[AccessD] Normalizing Excel Data

ewaldt at gdls.com ewaldt at gdls.com
Thu Apr 20 12:51:33 CDT 2006


This will be a simplified version of what I need. I believe I can expand it
appropriately, if I can be helped in this simplified version.

Let's say we have an Excel spreadsheet with the following data:

ID    Name  Data_A      Data_B      Data_C
100   Tom   11    22    33
101   Mary  44    55    66
102   John  77    88    99

Now, what I want to do is to import this into Access, where I've already
normalized tables as follows:

tblMain
Main_ID     Long integer, autonumber
Main_Num    Single
Main_Name   Text


tblData
Data_ID     Long integer, autonumber
Data_Num    Single
Data_Value  Single


What I need to create from these tables and the appropriate relationship is
as follows:

Data_Num    Data_Value
100.01            11
100.02            22
100.03            33
101.01            44
101.02            55
etc.

What I am doing is creating a new ID number for each Name-Data combination,
and entering its given data in that record. For each value in DataA, the
new ID number is formed by adding .01 to the Main_Num for the corresponding
Name record.

This is required by business rules, not by logical database rules. Sigh.
Anyway, I'm looking for logical ways to (1) set this up initially, (2)
import the Excel data, which is, of course, flat file, and (3) have it
available afterwords for normal database usage, meaning inputting data
directly, rather than through importing.

I hope this is clear (I wouldn't place any bets on it, though). Any help
would be sincerely appreciated. I've been working on it for quite a while,
but just can't seem to get a handle on it.

TIA,

Thomas F. Ewald
FCS Database Manager
General Dynamics Land Systems
(586) 276-1256





This is an e-mail from General Dynamics Land Systems. It is for the intended recipient only and may contain confidential and privileged information.  No one else may read, print, store, copy, forward or act in reliance on it or its attachments.  If you are not the intended recipient, please return this message to the sender and delete the message and any attachments from your computer. Your cooperation is appreciated.



More information about the AccessD mailing list