Pulling User Data from Multiple Columns in to a single Table (1 Viewer)

[RLea]

Hi,

Hoping someone may be able to give a bit of guidance on how I can achieve something.

I'm current working on creating a database that allows my company to track completion of online training of various different training modules.
The information all comes in as multiple excel files and has 20+ columns (of which I only need 2/3) which I then want to pull together into a single table within access.

The easiest way I found to do this was to import the entire excel sheet and then use an append query to add the columns I need from there. I've achieved that using the code below. However, the problem comes when some of the excel files have multiple training modules on them so I need to cycle through these and them as well.

Basically it comes like this

[EmployeeID] [MOD 1 STATUS] [MOD 2 STATUS] etc etc
010101 Successful Not Regsitered


What I want to do is to be able to take that and put it in to a table whereby the module is given an ID and it becomes this

[ModuleID] [FileID] [Status]
1 010101 Successful
2 010101 Not Registered

Any ideas would be massively appreciated.

Set Db = CurrentDb
Set appExcel = CreateObject("Excel.Application")
DoCmd.RunSQL "DELETE * FROM tbl_TrainingData"
Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
If FilePicker.Show = True Then
    For Each VarItem In FilePicker.SelectedItems
        StrModuleData = Dir(VarItem)
        StrFPath = VarItem
    Next
End If
Set MyWorkbook = appExcel.Workbooks.Open(StrFPath)
appExcel.Visible = False
With MyWorkbook
    .Sheets("Sheet1").Rows("1:9").EntireRow.Delete
    .Sheets("Sheet1").Range("C1").Value = "FileID"
    ModuleNameExcel = .Sheets("Sheet1").Range("T1").Value
    On Error GoTo Err_NoModuleID
    ModuleCode = DLookup("[ModuleID]", "tbl_Modules", "[ModuleName] = '" & ModuleNameExcel & "'")
    On Error Resume Next
    .Sheets("Sheet1").Range("T1").Value = "Status"
    .Save
    .Close
End With
DoCmd.TransferSpreadsheet acImport, 8, "tbl_ImportedExcel", StrModuleData, True, ""

StrInsert = "INSERT into tbl_TrainingData(FileID,Status) "
StrSelect = "SELECT FileID, Status1 FROM tbl_ImportedExcel"
StrSQL = StrInsert & StrSelect
DoCmd.RunSQL StrSQL
StrUpdate = "UPDATE tbl_TrainingData SET tbl_TrainingData.ModuleID = " & ModuleCode & _
" WHERE (((tbl_TrainingData.ModuleID) Is Null))"
DoCmd.RunSQL StrUpdate
Set FilePicker = Nothing
Set Db = Nothing
Exit Sub
Err_NoModuleID:
MsgBox "There is no corresponding module for this data in tbl_Modules. Please add the module information to tbl_Modules and try again", vbOKOnly, "Missing information"
Exit Sub
End Sub

 

[jdraw]

Please provide a fuller description of what this involves at your company
that allows my company to track completion of online training of various different training modules.. Just trying to find out the level of detail required.

This model reflects employee training and position required training. It may not represent your requirement.

 

[RLea]

Hi jdraw,

what you've provided is essentially what I've created in terms of the database.

Essentially we have continuous training throughout the year for various reasons, some is for all colleagues and some is dependent on their position. We receive reports on training completion on a regular basis (this varies per training module, sometimes daily sometimes weekly) but this data is for all colleague (c.20,000+) regardless of whether they need to complete it or not. It's just essentially a pull from server. What we are looking to achieve is way of taking that data from excel, importing it into access and then being able to use access to strip out anyone who doesn't need to complete it in order to give us an accurate representation of overall completion rates for each module.

The way I've set up the database relationships and tables allows me to get that out but it's getting the data in from Excel which is causing the issue, due to the way it comes to us. As mentioned there may be 20,000 employees on it and one module or there may be 5 modules. I'm looking to automate this as much as possible so that we can essentially press a button and select the report we want to import and then it will cycle through the data, taking the data in for each module and adding it to the "tbl_TrainingData" table.

Does that help?

 

[jdraw]

Yes it helps. It might be easier to understand and test a few approaches if you could supply some samples of your records.
You may find that mocking up some sample outputs (reports/queries) and working from your proposed tables, you can test scenarios before actually building the import process(es) or the physical database. Often, a little more analysis of requirements and process will reduce the number of code/adjust/vet cycles.
If the incoming format is variable, then you'd have to identify patterns and develop a routine(s) based on pattern.
Also, a few sample records and scenarios may get more viewers and responses/options.
Good luck.

 

[The_Doc_Man]

RLea, you have already done the part that would make most people shy away from the problem anyway by having an Excel App Object in use. So if I read the problem right, you have a spreadsheet that has two rows but a variable number of columns. The columns align such that row N is the course and row N+1 is the result.

In that case, you can look at the ROWS property .COLUMNS, which tells you how many columns you have on that row. So where you are stripping out data from ROWS(N) and ROWS(N+1), put that inside a loop based on ROWS(N).COLUMNS (obviously minus 1 to account for the first column as an identifier).

Does that help with the mechanical portion of your problem?

 

[arnelgp]

maybe you can provide a strip down excel file.
i'm sure anyone can take a look at it and give the result you need.

 

[RLea]

RLea, you have already done the part that would make most people shy away from the problem anyway by having an Excel App Object in use. So if I read the problem right, you have a spreadsheet that has two rows but a variable number of columns. The columns align such that row N is the course and row N+1 is the result.

In that case, you can look at the ROWS property .COLUMNS, which tells you how many columns you have on that row. So where you are stripping out data from ROWS(N) and ROWS(N+1), put that inside a loop based on ROWS(N).COLUMNS (obviously minus 1 to account for the first column as an identifier).

Does that help with the mechanical portion of your problem?

The_Doc_Man

Thanks for your reply, sorry I was away the last few days so just picking up on it now.

I managed to solve this issue although probably not in the most clean way. I've basically imported the entire document from excel to access and then used a Do Until the table field count = X as X is the point I know there is only one module. The loop essential finds the module ID and then imports the status with that module into my Tbl_TrainingData. I'll have a look at your suggestion though as that sounds cleaner.

I'm now stuck on another section though which I'd love some insight into.
Basically what I'm looking to create is a table which shows every employee by and their status for each module. However, not every employee needs to complete every module so I need to be able to strip out those who don't need to complete the module and give them the status of "Not Required". Then those who are required but haven't completed the status of "Outstanding".

Using the database I've created I can get to a point where I know who needs to complete each module using queries. What I'm struggling to do is find a way to then link that to the status of those who have completed and give everyone else the appropriate status as above.

View attachment Dummy Data.xlsx
View attachment End Result.xlsx
View attachment Training Dashboard v2.0 - Copy.accdb
As Arnel has suggested, to make this a bit clearer I've attached some dummy data, a copy of the database, and what I need as an end result.

Again, any help would be greatly appreciated.