Import "matrix" from Excel

[Cal]

I currently have an Excel spreadsheet that I am trying to convert to Access.
The information is in what I would call a matrix. It is used to keep track
of training requirements and skill levels for our department.

Down the left side of the spreadsheet is the employee names. Across the top
row is the "skill" description. At the intersection of employee name and
skill description is a number from 1 - 5, which indicates whether the person
has had the training, the training is not required, etc.

I have already setup a database with tables. I am trying to figure out how
to get this data into the database. I have looked at several examples in the
groups, but I haven't found any that deal with this type of matrix. I am
trying to avoid having to type this in by hand as there is about 50 employees
and about 30 skills.

Can this be done?

Thanks!

 

[Brian Wilson]

I currently have an Excel spreadsheet that I am trying to convert to
Access.
The information is in what I would call a matrix. It is used to keep
track
of training requirements and skill levels for our department.

Down the left side of the spreadsheet is the employee names. Across the
top
row is the "skill" description. At the intersection of employee name and
skill description is a number from 1 - 5, which indicates whether the
person
has had the training, the training is not required, etc.

I have already setup a database with tables. I am trying to figure out
how
to get this data into the database. I have looked at several examples in
the
groups, but I haven't found any that deal with this type of matrix. I am
trying to avoid having to type this in by hand as there is about 50
employees
and about 30 skills.

Can this be done?

Thanks!

Yes it can be done and is fairly straight forward, but you would have to
write a bit of code to do it. Or perhaps someone here would write the code
for you but we don't know how you have structured the tables in the database
where you want the data to go.

I would expect you to have four tables for this data:

tblEmployee:
EmpID (autonumber PK)
EmpFirstName
EmpLastName
etc

tblSkill
SkiID (autonumber PK)
SkiName

tblCode
CodID (the number 1 to five)
CodName (training done, not required, etc)

tblTraining:
TrnEmployee (the ID from tblEmployee.EmpID)
TrnSkill (the ID from tblSkill.SkiID)
TrnCode (the ID from tblCode.CodID)



My firrst step would be to simply cut and paste this into a temporary Access
table. Then write a routine which would loop through each employee, adding
the various records.

 

[Cal]

Wow, you are good! That is almost exactly what I have for tables.

I am assuming that the information will have to go into the "tblTraining"
table. In that table, for every employee there would be multiple entries,
right? The difficulty that I see is that in the Excel spreadsheet the
employee is only listed once. Or isn't that really an issue?

I have never done a cut-and-paste from Excel to Access. What would the
steps be?

Thanks for your time!!!!!

 

[Brian Wilson]

Wow, you are good! That is almost exactly what I have for tables.

I am assuming that the information will have to go into the "tblTraining"
table. In that table, for every employee there would be multiple entries,
right? The difficulty that I see is that in the Excel spreadsheet the
employee is only listed once. Or isn't that really an issue?

I have never done a cut-and-paste from Excel to Access. What would the
steps be?

Thanks for your time!!!!!

Actually, perhaps the import wizard would be easier than cut and paste.
While on the tables tab of the database window choose File>Get External
Data>Import and select files of type Excel. Find your import matrix and
tell the wizard your spreadsheet contains column names also let it assign
its own primary key.
You should then end up with a table with fields like
ID, Employee, Word, Excel, Outlook, Access, Powerpoint, etc (or whatever
your skills are) Once you have this 'matrix' in Access, you can transform
it into the new format. It does require some vba (visual basic for
applications) coding so let us know if you need help with this.

 

[Cal]

OK, I have it in Access now. Yes, I could use some help. I am very familar
with VBA, just not within Access and doing what we are talking about.

Before I did the import, I modified the spreadsheet. I changed the employee
name to the employeeID in the tblEmployee. I also changed the skill
description to be the skillID of the tblSkill. My thought was that this is
going to go in the "interim" table that "converts" the many-to-many
relationship. Did I do that right?

Any help with the VBA code would be appreciated.

Thanks!

 

[Brian Wilson]

OK, I have it in Access now. Yes, I could use some help. I am very
familar
with VBA, just not within Access and doing what we are talking about.

Before I did the import, I modified the spreadsheet. I changed the
employee
name to the employeeID in the tblEmployee. I also changed the skill
description to be the skillID of the tblSkill. My thought was that this
is
going to go in the "interim" table that "converts" the many-to-many
relationship. Did I do that right?

Any help with the VBA code would be appreciated.

Thanks!

If you have managed to use the ID's then this saves us a bit of coding,
because what you should have now is a table with as many columns as there
are skills plus one containing the employee id. If you told the import
wizard that your spreadsheet contained column names, then the columns should
have names like 1,2,3,etc which represent the skill id's.

So in this example, I assume the table is called 'tblExcel' and the first
column named EmpID and the rest of the columns are named as per the skill
id. The destination is called 'tblTraining' and has its own autonumber pk,
followed by columns representing EmployeeID, SkillID, CodeID. You create an
import form with a button named cmdImport. The code might be something like
this:


Option Compare Database
Option Explicit

Private Sub cmdImport_Click()

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim blnError As Boolean

strSQL = "SELECT * FROM tblExcel ORDER BY EmpID"

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardOnly, dbReadOnly)

While (rst.EOF = False) And (blnError = False)
MsgBox rst.Fields("Employee")
If Not AddTrainingRecords(rst) Then
blnError = True
End If
rst.MoveNext
Wend

MsgBox "Import Complete", vbInformation

Exit_Handler:

If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub


Private Function AddTrainingRecords(rstEmployee As DAO.Recordset) As Boolean

On Error GoTo Err_Handler

Dim strSQL As String
Dim dbs As DAO.Database
Dim rstTraining As DAO.Recordset
Dim fld As DAO.Field
Dim lngEmpID As Long

strSQL = "SELECT * FROM tblTraining"

Set dbs = CurrentDb

Set rstTraining = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbAppendOnly)

For Each fld In rstEmployee.Fields
If lngEmpID = 0 Then
lngEmpID = rstEmployee.Fields(0)
Else
rstTraining.AddNew
rstTraining.Fields(1) = lngEmpID
rstTraining.Fields(2) = CLng(fld.Name)
rstTraining.Fields(3) = rstEmployee.Fields(fld.Name)
rstTraining.Update
End If
Next fld

AddTrainingRecords = True

Exit_Handler:

If Not rstTraining Is Nothing Then
rstTraining.Close
Set rstTraining = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

 

[Cal]

Brian,

With a little tweaking, I got it to work. Most of the problems related to
the data in the original spreadsheet.

Thanks again for all of your help!!!!! It is really appreciated!!!