john wrote:
I have imported an Excel spreadsheet in Access. This table has 150 fields.
The first field is 'user name', and all the other fields represent
application names of which the value can be True of False for that
particular user. User generally uses about 10 applications. I would like to
divide this table into 3 tables: User, Application, and Relation
User-Application. The user table is easy to make but how do I make the other
two tables? If the application name is true for a user that field name that
represents the application name (from the old table) should be converted to
a value in the Application table with field 'Application Name'? And how can
I make the relationship table?
Thanks in advance.
john
Oh, this is not going to be pretty.
Users should be easy... in theory you could link to the SS (say it's
called xlsAppsInstalled in your DB). Then the user names would be
SELECT [user name] FROM xlsAppsInstalled ORDER BY [user name];
The others are going to be ugly. You should convert them to a "shape"
like this (hey, it's late, my brain is fading!):
(Username, ApplicationName)
INSERT INTO tblXLSUsers ( xlsUserName )
SELECT xlsAppsLoaded.Username
FROM xlsAppsLoaded
ORDER BY xlsAppsLoaded.Username;
would do the appending for you... (assuming each Username is unique)
for the normalization part... this is the fun part.
the basic idea is to return all the combinations of UserName and
AppName where Installed is True/Checked. Then append those non-null
records to the User_Application table.
You can do this pretty easily by looping through the fields collection
of the table and then returning the record pairs where the value is
True. (Sorry, have to do that when I'm more awake). In
spreadsheet-speak, you'd grab the value in the "Username" column each
time, and then you'd move through App1, app2, App3 columns.
Each time you use a query like this:
SELECT xlsAppsLoaded.Username, xlsAppsLoaded.App1
WHERE (((xlsAppsLoaded.App1)="T")))
except you loop over all the "App#" fields. (use the fields
collection).
Then you create an insert statement to run the insert. (say it's built
on the fly and stuffed into strSQL (a string variable)
then you can do:
DBEngine(0)(0).Execute strSQL, dbFailOnError
to insert the data.
And if you stitch it all together, it'll do the normalization stuff for
you. Very handy if you have a sick number of columns in your
spreadsheet. Oh, and you can grab the Application Name by getting the
name of the "column" in your table.
DBEngine(0)(0).TableDefs("MyTable").Fields(intcoun ter).Name
Not to worry... I'll fix it for ya when I'm awake... but right now I
gotta sleep... really!
Pieter