473,320 Members | 1,914 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Normalize table

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
Sep 13 '06 #1
9 3235

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

Sep 13 '06 #2

Thanks Pieter,
You feel better? ;-)

You gave me some new insights. For now I decided to keep it 'simple' using 2
tables (user and user-app) where user in fact is logon name which is unique.
I can add ID_numbers later. From what you wrote I understand that for the
second table I can do something like the following:

for i from 2 to <nr of fields in source table>

1. run a table-make-query (Q1) and use
DBEngine(0)(0).TableDefs("MyTable").Fields(i).Name
to pick the right field
This query should select all users where field App(i)=True
2.Q1 results table should be edited by a query
which adds a field to the table and adds the value of App(i) to every
record
3. This resulting table should be added to the main user-app table.

endfor

I'd be very happy to make this work. Some questions about the above:
1. How can I use <DBEngine(0)(0).TableDefs("MyTable").Fields(i).Nam ein my
sql query to reference the right field name?
2. Regarding point 2: how can I add a new field to the result table and make
sure all records are filled with the particular application name (App(i))?

Thanks again,
john

<pi********@hotmail.comschreef in bericht
news:11**********************@p79g2000cwp.googlegr oups.com...
>
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

Sep 13 '06 #3
Okay, got it working.

Public Sub NormalizeTable(ByVal strSrcTable As String, ByVal
strDestTable As String, ByVal strPK As String)
'Statement to be built:
'INSERT INTO User_Apps ( Username, Access )
'SELECT xlsAppsLoaded.Username, xlsAppsLoaded.Access
'FROM xlsAppsLoaded
'WHERE xlsAppsLoaded.Access='T';

'INPUTS:
'strSrcTable = the table the data is coming FROM
'strDestTable = the table the data is being appended TO
'strPK = the primary key of the Destination table.

'SAMPLE CALL
'Normalizetable "xlsappsloaded","User_Apps","username"

Dim tdf As DAO.TableDef
Dim intField As Integer
Dim strSQL As String

Set tdf = DBEngine(0)(0).TableDefs(strSrcTable)
For intField = 1 To tdf.Fields.Count - 1

'--building SQL statement
strSQL = "INSERT INTO " & strDestTable & " (Username,
ApplicationTitle) "
strSQL = strSQL & "SELECT [" & strSrcTable & "].[" & strPK
& "], '" & tdf.Fields(intField).Name & "'"
strSQL = strSQL & " FROM [" & tdf.Name & "]"
strSQL = strSQL & " WHERE ((([" & tdf.Name & "].[" &
tdf.Fields(intField).Name & "]='T')));"

'--executing the SQL statement (dbFailOnError just makes
the code continue of an illegal insert is run
'--it will just fail on that insert, but the code will
continue
DBEngine(0)(0).Execute strSQL, dbFailOnError
Next intField
Set tdf = Nothing

'---just feedback...
MsgBox "Finished normalizing " & strSrcTable & " into " &
strDestTable, vbOKOnly

End Sub

Sep 13 '06 #4
Thanks very much,
I'll work on it tomorrow
john

<pi********@hotmail.comschreef in bericht
news:11**********************@d34g2000cwd.googlegr oups.com...
Okay, got it working.

Public Sub NormalizeTable(ByVal strSrcTable As String, ByVal
strDestTable As String, ByVal strPK As String)
'Statement to be built:
'INSERT INTO User_Apps ( Username, Access )
'SELECT xlsAppsLoaded.Username, xlsAppsLoaded.Access
'FROM xlsAppsLoaded
'WHERE xlsAppsLoaded.Access='T';

'INPUTS:
'strSrcTable = the table the data is coming FROM
'strDestTable = the table the data is being appended TO
'strPK = the primary key of the Destination table.

'SAMPLE CALL
'Normalizetable "xlsappsloaded","User_Apps","username"

Dim tdf As DAO.TableDef
Dim intField As Integer
Dim strSQL As String

Set tdf = DBEngine(0)(0).TableDefs(strSrcTable)
For intField = 1 To tdf.Fields.Count - 1

'--building SQL statement
strSQL = "INSERT INTO " & strDestTable & " (Username,
ApplicationTitle) "
strSQL = strSQL & "SELECT [" & strSrcTable & "].[" & strPK
& "], '" & tdf.Fields(intField).Name & "'"
strSQL = strSQL & " FROM [" & tdf.Name & "]"
strSQL = strSQL & " WHERE ((([" & tdf.Name & "].[" &
tdf.Fields(intField).Name & "]='T')));"

'--executing the SQL statement (dbFailOnError just makes
the code continue of an illegal insert is run
'--it will just fail on that insert, but the code will
continue
DBEngine(0)(0).Execute strSQL, dbFailOnError
Next intField
Set tdf = Nothing

'---just feedback...
MsgBox "Finished normalizing " & strSrcTable & " into " &
strDestTable, vbOKOnly

End Sub

Sep 13 '06 #5
I tried the code but I can't get it to work:
The code stops on:

Dim tdf As DAO.TableDef

I get the error message that the 'info type', defined by the user, is not
defined.
DAO.TableDef won't turn blue in stead of the other types of variables.

Any idea how to solve this?
thanks
john

<pi********@hotmail.comschreef in bericht
news:11**********************@d34g2000cwd.googlegr oups.com...
Okay, got it working.

Public Sub NormalizeTable(ByVal strSrcTable As String, ByVal
strDestTable As String, ByVal strPK As String)
'Statement to be built:
'INSERT INTO User_Apps ( Username, Access )
'SELECT xlsAppsLoaded.Username, xlsAppsLoaded.Access
'FROM xlsAppsLoaded
'WHERE xlsAppsLoaded.Access='T';

'INPUTS:
'strSrcTable = the table the data is coming FROM
'strDestTable = the table the data is being appended TO
'strPK = the primary key of the Destination table.

'SAMPLE CALL
'Normalizetable "xlsappsloaded","User_Apps","username"

Dim tdf As DAO.TableDef
Dim intField As Integer
Dim strSQL As String

Set tdf = DBEngine(0)(0).TableDefs(strSrcTable)
For intField = 1 To tdf.Fields.Count - 1

'--building SQL statement
strSQL = "INSERT INTO " & strDestTable & " (Username,
ApplicationTitle) "
strSQL = strSQL & "SELECT [" & strSrcTable & "].[" & strPK
& "], '" & tdf.Fields(intField).Name & "'"
strSQL = strSQL & " FROM [" & tdf.Name & "]"
strSQL = strSQL & " WHERE ((([" & tdf.Name & "].[" &
tdf.Fields(intField).Name & "]='T')));"

'--executing the SQL statement (dbFailOnError just makes
the code continue of an illegal insert is run
'--it will just fail on that insert, but the code will
continue
DBEngine(0)(0).Execute strSQL, dbFailOnError
Next intField
Set tdf = Nothing

'---just feedback...
MsgBox "Finished normalizing " & strSrcTable & " into " &
strDestTable, vbOKOnly

End Sub

Sep 14 '06 #6

john wrote:
I tried the code but I can't get it to work:
The code stops on:

Dim tdf As DAO.TableDef

I get the error message that the 'info type', defined by the user, is not
defined.
DAO.TableDef won't turn blue in stead of the other types of variables.

Any idea how to solve this?
thanks
john
open the code module (well, any code module). Then go to Tools,
References, and select Microsoft DAO 3.6

Then it'll work.

Sep 14 '06 #7
Yes! that dit it!
thanks
john

<pi********@hotmail.comschreef in bericht
news:11*********************@b28g2000cwb.googlegro ups.com...
>
john wrote:
>I tried the code but I can't get it to work:
The code stops on:

Dim tdf As DAO.TableDef

I get the error message that the 'info type', defined by the user, is not
defined.
DAO.TableDef won't turn blue in stead of the other types of variables.

Any idea how to solve this?
thanks
john

open the code module (well, any code module). Then go to Tools,
References, and select Microsoft DAO 3.6

Then it'll work.

Sep 15 '06 #8
In article <Ve********************@casema.nl>, jo**@test.com says...
Yes! that dit it!
thanks
john

<pi********@hotmail.comschreef in bericht
news:11*********************@b28g2000cwb.googlegro ups.com...

john wrote:
I tried the code but I can't get it to work:
The code stops on:

Dim tdf As DAO.TableDef

I get the error message that the 'info type', defined by the user, is not
defined.
DAO.TableDef won't turn blue in stead of the other types of variables.

Any idea how to solve this?
thanks
john
open the code module (well, any code module). Then go to Tools,
References, and select Microsoft DAO 3.6

Then it'll work.

The UserName table of course was easy.

The Application table would have been easy with a copy - paste special -
transpose in excel then import to Access.

Leaving only the third table which was fairly easy to set up in excel as
well. Lesson is that sometimes 'fixing' the data at the source is the
answer.

Sep 16 '06 #9
The Application table would have been easy with a copy - paste special -
transpose in excel then import to Access.

Leaving only the third table which was fairly easy to set up in excel as
well. Lesson is that sometimes 'fixing' the data at the source is the
answer.
Transposing in Excel is no problem, but it in no way creates normalized
data. How do you use Transpose to compress something like a 150
columns x 100+ (possibly) rows grid to two columns (userID,
softwareID)?

Could you post some code that would make this work or outline the steps
in Excel? I tried transposing and it accomplished little. Now I have
rows instead of columns, but it's nowhere near normalized

Sep 17 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Christos TZOTZIOY Georgiou | last post by:
I found at least one case where decombining and recombining a unicode character does not result in the same character (see at end). I have no extensive knowledge about Unicode, yet I believe that...
20
by: Steve Jorgensen | last post by:
Hi all, I've just finished almost all of what has turned out to be a real bear of a project. It has to import data from a monthly spreadsheet export from another program, and convert that into...
4
by: Nathan Benefield | last post by:
I currently have a spreadsheet tracking votes on legislation in a matrix type format. It is something like this Name Act1 Veto1 Act1A Jones yes No Yes Johnson Yes ...
3
by: Janelle.Dunlap | last post by:
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...
4
by: kollatjorva | last post by:
Hi all I'm trying to get a value from an xml node 'Publisher' use the value as a name of an .css class. This works fine until I get a value from the Publisher node with white space in it. I've...
5
by: =?iso-8859-1?B?TWF0dGlhcyBCcuRuZHN0cvZt?= | last post by:
Hello! I'm trying to find what package I should use if I want to: 1. Create 3d vectors. 2. Normalize those vectors. 3. Create a 3x3 rotation matrix from a unit 3-d vector and an angle in...
8
by: bbcrock | last post by:
I have three tables with a relationship I've never worked with before. Can anyone suggest/comment on the best way to create a third normal form relationship between these tables? The tables...
0
bugboy
by: bugboy | last post by:
At what point is normalizing worth forgetting? I have a column varchar (4) that will repeat one of only eight possible values for each row. Is it really more efficient to break this out into it's...
9
by: Peter Bengtsson | last post by:
In UTF8, \u0141 is a capital L with a little dash through it as can be seen in this image: http://static.peterbe.com/lukasz.png I tried this: '' I was hoping it would convert it it 'L'...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.