Generate a report in Excel using Access table data

[aman1234]

Hi guys

Please find attached spreadsheet. I have access back-end and excel front-end for this project and now I want to generate a report as shown in sheet "Report". As I can't send the access database structure so I have copied the data and structure of Access table in "tblmain" sheet. Now from tblmain sheet I want a report to display like "Report" sheet. Like when the user press enter then this report should get displayed.

Any help would be much appreciated.

Thanks

[aman1234]

Sorry I didn't attach the excel workbook. Please see attachment.

Thanks

[nilem]

Hi aman1234

Sub ertert()
Dim x, y(), i&, j&, k&, n&

With Sheets("tblmain")
    x = .Range("A1:C" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With
Dim d As Object: Set d = CreateObject("Scripting.Dictionary"): j = 1

With d
    .CompareMode = 1
    For i = 2 To UBound(x)
        If Not .Exists(x(i, 2)) Then j = j + 1: .Item(x(i, 2)) = j
    Next i
End With
ReDim y(1 To UBound(x, 1), 1 To j): j = 1

With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 2 To UBound(x)
        k = d.Item(x(i, 2))
        If .Exists(x(i, 1)) Then
            n = .Item(x(i, 1)): y(n, k) = Left(x(i, 3), 1)
        Else
            j = j + 1: .Item(x(i, 1)) = j
            y(j, 1) = x(i, 1): y(j, k) = Left(x(i, 3), 1)
        End If
    Next i
End With

With Sheets("Report")
    .UsedRange.ClearContents
    .Range("A2").Resize(j, d.Count + 1).Value = y: .Range("A2") = "Name"
    .Range("B2").Resize(, d.Count).Value = d.keys: .Activate
End With

Set d = Nothing
End Sub

[aman1234]

Thanks nilem for sending me the code. I will definetely try it tomorrow in the office.

One question as now you have used tblmain sheet to generate a report but in actual I have access database and tblmain is a table in it. Can we modify the code so that it will look for data in tblmain access table and generate a report in excelsheet?

Thanks again for your help.

[aman1234]

Thanks nilem, I checked it today and its working absolutely fine. Just few changes I need to make in the code:

1. Set the background color to Amber when Training.
2. Set the background color to Green when Accredited.
3. Red when month to go before accreditation expires.

Could you please help me to make these changes in the Report? Everything works fine but we need to add back color.

Thanks